c# - 如何阻止 EF 生成无用代码，这有关系吗？

Question

我正在运行一个类似 linq 的查询

dbEntity.Where(x => x.FundType== "E")
        .Where(x => x.ReportDate == new DateTime(2014,10,23))
        .GroupBy(x => x.ReportDate)
        .Select(groupedDate => new CategorySourceData
            {
                ReportDate = groupedDate.Key,
                TotalFlow = groupedDate.Sum(x => x.Flow)
            }
        .ToList();

我希望它给我与此查询相同的结果以及类似的运行时间

select ReportDate,
       sum(flow)
from vwDailyFundFlowDetail
where FundType = 'E'
group by ReportDate

Ef 生成返回正确数据的查询。但是，我的手写查询在我的测试集上花费了大约 13 秒，而 ef 生成的查询在同一测试集上花费了 24 秒。

ef 查询看起来像

exec sp_executesql N'SELECT 
    1 AS [C1], 
    [GroupBy1].[K1] AS [ReportDate], 
     CAST( [GroupBy1].[A1] AS real) AS [C2]
    FROM ( SELECT 
        [Extent1].[ReportDate] AS [K1], 
        SUM([Extent1].[Flow]) AS [A1]
        FROM (SELECT 
      [vwDailyFundFlowDetail].[HFundId] AS [HFundId], 
      [vwDailyFundFlowDetail].[ReportDate] AS [ReportDate], 
      [vwDailyFundFlowDetail].[Flow] AS [Flow], 
      [vwDailyFundFlowDetail].[ForexChange] AS [ForexChange], 
      [vwDailyFundFlowDetail].[AssetsEnd] AS [AssetsEnd], 
      [vwDailyFundFlowDetail].[ShareID] AS [ShareID], 
      [vwDailyFundFlowDetail].[ShareClass] AS [ShareClass], 
      [vwDailyFundFlowDetail].[ISIN] AS [ISIN], 
      [vwDailyFundFlowDetail].[CUSIP] AS [CUSIP], 
      [vwDailyFundFlowDetail].[Ticker] AS [Ticker], 
      [vwDailyFundFlowDetail].[AssetsStart] AS [AssetsStart], 
      [vwDailyFundFlowDetail].[PortfolioChange] AS [PortfolioChange], 
      [vwDailyFundFlowDetail].[FundName] AS [FundName], 
      [vwDailyFundFlowDetail].[SSID] AS [SSID], 
      [vwDailyFundFlowDetail].[Advisor] AS [Advisor], 
      [vwDailyFundFlowDetail].[GEOID] AS [GEOID], 
      [vwDailyFundFlowDetail].[FTCID] AS [FTCID], 
      [vwDailyFundFlowDetail].[BenchIndex] AS [BenchIndex], 
      [vwDailyFundFlowDetail].[FundType] AS [FundType], 
      [vwDailyFundFlowDetail].[ETF] AS [ETF], 
      [vwDailyFundFlowDetail].[Domicile] AS [Domicile], 
      [vwDailyFundFlowDetail].[GeographicFocus] AS [GeographicFocus], 
      [vwDailyFundFlowDetail].[Currency] AS [Currency], 
      [vwDailyFundFlowDetail].[FundDomicile] AS [FundDomicile], 
      [vwDailyFundFlowDetail].[Manager] AS [Manager], 
      [vwDailyFundFlowDetail].[FundCurrency] AS [FundCurrency], 
      [vwDailyFundFlowDetail].[Benchmark] AS [Benchmark], 
      [vwDailyFundFlowDetail].[FundFocus] AS [FundFocus], 
      [vwDailyFundFlowDetail].[NetChange] AS [NetChange], 
      [vwDailyFundFlowDetail].[FundId] AS [FundId], 
      [vwDailyFundFlowDetail].[InstOrRetail] AS [InstOrRetail], 
      [vwDailyFundFlowDetail].[Hedge_yn] AS [Hedge_yn], 
      [vwDailyFundFlowDetail].[SRI_yn] AS [SRI_yn], 
      [vwDailyFundFlowDetail].[SCID] AS [SCID], 
      [vwDailyFundFlowDetail].[DistributorId] AS [DistributorId], 
      [vwDailyFundFlowDetail].[Distributor] AS [Distributor], 
      [vwDailyFundFlowDetail].[Frontier] AS [Frontier], 
      [vwDailyFundFlowDetail].[FundCategory] AS [FundCategory], 
      [vwDailyFundFlowDetail].[Commodity] AS [Commodity], 
      [vwDailyFundFlowDetail].[CurrId] AS [CurrId], 
      [vwDailyFundFlowDetail].[Idx] AS [Idx], 
      [vwDailyFundFlowDetail].[Bear] AS [Bear], 
      [vwDailyFundFlowDetail].[Dividend] AS [Dividend], 
      [vwDailyFundFlowDetail].[AbsoluteReturn] AS [AbsoluteReturn], 
      [vwDailyFundFlowDetail].[Islamic_yn] AS [Islamic_yn], 
      [vwDailyFundFlowDetail].[Ins] AS [Ins], 
      [vwDailyFundFlowDetail].[Inf] AS [Inf], 
      [vwDailyFundFlowDetail].[Silver] AS [Silver], 
      [vwDailyFundFlowDetail].[Gold] AS [Gold], 
      [vwDailyFundFlowDetail].[AG] AS [AG], 
      [vwDailyFundFlowDetail].[Metal] AS [Metal], 
      [vwDailyFundFlowDetail].[Administrator] AS [Administrator], 
      [vwDailyFundFlowDetail].[Custodian] AS [Custodian], 
      [vwDailyFundFlowDetail].[Bear2x] AS [Bear2x], 
      [vwDailyFundFlowDetail].[Bear3x] AS [Bear3x], 
      [vwDailyFundFlowDetail].[Leverage1x] AS [Leverage1x], 
      [vwDailyFundFlowDetail].[Leverage2x] AS [Leverage2x], 
      [vwDailyFundFlowDetail].[MuniStateId] AS [MuniStateId], 
      [vwDailyFundFlowDetail].[Leverage3x] AS [Leverage3x], 
      [vwDailyFundFlowDetail].[Synthetic_ETF] AS [Synthetic_ETF], 
      [vwDailyFundFlowDetail].[Physical_ETF] AS [Physical_ETF], 
      [vwDailyFundFlowDetail].[IG] AS [IG], 
      [vwDailyFundFlowDetail].[EMC] AS [EMC], 
      [vwDailyFundFlowDetail].[EMS] AS [EMS], 
      [vwDailyFundFlowDetail].[EMM] AS [EMM], 
      [vwDailyFundFlowDetail].[MuniState] AS [MuniState], 
      [vwDailyFundFlowDetail].[FundQualityId] AS [FundQualityId], 
      [vwDailyFundFlowDetail].[FundDurationId] AS [FundDurationId], 
      [vwDailyFundFlowDetail].[QualityName] AS [QualityName], 
      [vwDailyFundFlowDetail].[DurationName] AS [DurationName], 
      [vwDailyFundFlowDetail].[ClosedEnd] AS [ClosedEnd], 
      [vwDailyFundFlowDetail].[MLP] AS [MLP], 
      [vwDailyFundFlowDetail].[ArrivalTime] AS [ArrivalTime], 
      [vwDailyFundFlowDetail].[ArrivalDayCode] AS [ArrivalDayCode], 
      [vwDailyFundFlowDetail].[DActivationDate] AS [DActivationDate], 
      [vwDailyFundFlowDetail].[WActivationDate] AS [WActivationDate], 
      [vwDailyFundFlowDetail].[MActivationDate] AS [MActivationDate], 
      [vwDailyFundFlowDetail].[ShareClassCurrency] AS [ShareClassCurrency]
      FROM [dbo].[vwDailyFundFlowDetail] AS [vwDailyFundFlowDetail]) AS [Extent1]
        WHERE ((@p__linq__0 IS NULL) OR ([Extent1].[FundType] = @p__linq__1) OR (([Extent1].[FundType] IS NULL) AND (@p__linq__1 IS NULL))) AND (0 = (CASE WHEN ( EXISTS (SELECT 
            1 AS [C1]
            FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
            WHERE 1 = 0
        )) THEN cast(1 as bit) ELSE cast(0 as bit) END))
        GROUP BY [Extent1].[ReportDate]
    )  AS [GroupBy1]',N'@p__linq__0 nvarchar(4000),@p__linq__1 varchar(8000)',@p__linq__0=N'E',@p__linq__1='E'

where子句所在的部分

AND (0 = (CASE WHEN ( EXISTS (SELECT 
            1 AS [C1]
            FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
            WHERE 1 = 0
        )) THEN cast(1 as bit) ELSE cast(0 as bit) END))

似乎是主要问题。据我所知，case 语句始终为 0，所以这始终为真。这部分查询的存在正在改变执行计划的一部分

看起来像

如果我从 where 子句中删除 case 语句，那么据我所知，该查询的执行计划和运行时间与我手写的 sql 将变得完全相同。如何阻止 EF 添加 where 子句的这一部分？我在我的 linq 代码中做了什么使 EF 将此添加到查询中？

Answer 1

您需要在 DBContext 上设置 Configuration.UseDatabaseNullSemantics = true;

这将阻止添加的is null 检查

https://msdn.microsoft.com/en-us/library/system.data.entity.infrastructure.dbcontextconfiguration.usedatabasenullsemantics(v=vs.113).aspx