我有一个Select语句,目前的格式如下:
dbEntity
.GroupBy(x => x.date)
.Select(groupedDate => new {
Calculation1 = doCalculation1 ? x.Sum(groupedDate.Column1) : 0),
Calculation2 = doCalculation2 ? x.Count(groupedDate) : 0)
在查询中,doCalculation1 和 doCalculation2 是之前设置的布尔值。这将在生成的 SQL 中创建一个 case 语句,类似于
DECLARE @p1 int = 1
DECLARE @p2 int = 0
DECLARE @p3 int = 1
DECLARE @p4 int = 0
SELECT (Case When @p1 = 1 THEN Sum(dbEntity.Column1)
Else @p2
End) as Calculation1,
(Case When @p3 = 1 THEN Count(*)
Else @p4
End) as Calculation2
当doCalculation1为true时,我希望生成的SQL语句如下所示。
SELECT SUM(Column1) as Calculation1, Count(*) as Calculation2
当 doCalculation2 为 false 时,就像这样。
SELECT 0 as Calculation1, Count(*) as Calculation2
有没有办法让EF强制执行查询并像这样工作?
编辑:
bool doCalculation = true;
bool doCalculation2 = false;
dbEntity
.Where(x => x.FundType == "E")
.GroupBy(x => x.ReportDate)
.Select(dateGroup => new
{
ReportDate = dateGroup.Key,
CountInFlows = doCalculation2 ? dateGroup.Count(x => x.Flow > 0) : 0,
NetAssetEnd = doCalculation ? dateGroup.Sum(x => x.AssetsEnd) : 0
})
.ToList();
生成这个SQL。
-- Region Parameters
DECLARE @p0 VarChar(1000) = 'E'
DECLARE @p1 Int = 0
DECLARE @p2 Decimal(5,4) = 0
DECLARE @p3 Int = 0
DECLARE @p4 Int = 1
DECLARE @p5 Decimal(1,0) = 0
-- EndRegion
SELECT [t1].[ReportDate],
(CASE
WHEN @p1 = 1 THEN (
SELECT COUNT(*)
FROM [dbEntity] AS [t2]
WHERE ([t2].[Flow] > @p2) AND ([t1].[ReportDate] = [t2].[ReportDate]) AND ([t2].[FundType] = @p0)
)
ELSE @p3
END) AS [CountInFlows],
(CASE
WHEN @p4 = 1 THEN CONVERT(Decimal(33,4),[t1].[value])
ELSE CONVERT(Decimal(33,4),@p5)
END) AS [NetAssetEnd]
FROM (
SELECT SUM([t0].[AssetsEnd]) AS [value], [t0].[ReportDate]
FROM [dbEntity] AS [t0]
WHERE [t0].[FundType] = @p0
GROUP BY [t0].[ReportDate]
) AS [t1]
执行计划中包含许多索引扫描、暂存器和连接操作。在测试集上平均运行时间约为20秒,而生产集将要更大。
我希望它能像SQL一样运行速度快。
select reportdate, 1, sum(AssetsEnd)
from vwDailyFundFlowDetail
where fundtype = 'E'
group by reportdate
平均运行时间约为12秒,并且大部分查询都在执行计划中的单个索引查找中绑定。实际的sql输出无关紧要,但是使用case语句性能似乎要差得多。
至于我为什么要这样做,我需要生成动态的select语句,就像我在Dynamically generate Linq Select中询问的那样。用户可以选择执行一项或多项计算,直到请求到达之前我不知道选择了哪些选项。这些请求很耗费资源,因此我们不想在没有必要的情况下运行它们。我根据用户请求设置doCalculation bools。
该查询旨在替换将从硬编码的SQL查询字符串插入或删除字符的某些代码,然后执行该字符串。那种方式运行得相当快,但维护起来非常麻烦。