具有EF Linq Select语句选择常量或函数(Have EF Linq Select statement Select a constant or a function)

我有一个当前格式为的Select语句

dbEntity .GroupBy(x => x.date) .Select(groupedDate => new { Calculation1 = doCalculation1 ? x.Sum(groupedDate.Column1) : 0), Calculation2 = doCalculation2 ? x.Count(groupedDate) : 0)

在查询中，doCalculation1和doCalculation2是先前设置的bool。这会在生成的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语句的性能似乎要差得多。

至于为什么我这样做，我需要生成动态选择语句，就像我在动态生成Linq Select中所要求的那样。用户可以选择要执行的一组计算中的一个或多个，并且在请求进入之前我将不知道所选择的内容。请求是昂贵的，因此除非必要，否则我们不希望运行它们。我根据用户请求设置了doCalculation bools。

此查询应该替换一些代码，这些代码插入或删除存储为字符串的硬编码sql查询中的字符，然后执行该代码。这运行得相当快，但维持是一场噩梦

I have a Select statement that is currently formatted like

dbEntity .GroupBy(x => x.date) .Select(groupedDate => new { Calculation1 = doCalculation1 ? x.Sum(groupedDate.Column1) : 0), Calculation2 = doCalculation2 ? x.Count(groupedDate) : 0)

In the query doCalculation1 and doCalculation2 are bools that are set earlier. This creates a case statement in the Sql being generated, like

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

What I want to happen is for the generated sql is to be like this when doCalculation1 is true

SELECT SUM(Column1) as Calculation1, Count(*) as Calculation2

and like this when doCalculation2 is false

SELECT 0 as Calculation1, Count(*) as Calculation2

Is there any way to force a query through EF to act like this?

Edit:

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();

generates this 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]

which has many index scans and a spool and a join in the execution plan. It also takes about 20 seconds on average to run on the test set, with the production set going to be much larger.

I want it to run in the same speed as sql like

select reportdate, 1, sum(AssetsEnd) from vwDailyFundFlowDetail where fundtype = 'E' group by reportdate

which runs in about 12 seconds on average and has the majority of the query tied up in a single index seek in the execution plan. What the actual sql output is doesnt matter, but the performance appears to be much worse with the case statements.

As for why I am doing this, I need to generate a dynamic select statements like I asked in Dynamically generate Linq Select. A user may select one or more of a set of calculations to perform and I will not know what is selected until the request comes in. The requests are expensive so we do not want to run them unless they are necessary. I am setting the doCalculation bools based on the user request.

This query is supposed to replace some code that inserts or deletes characters from a hardcoded sql query stored as a string, which is then executed. That runs fairly fast but is a nightmare to maintain

最满意答案

从技术上讲，可以通过表达式树访问器在Select查询中传递Expression，它在三元运算符的左侧检查常量值，并用适当的子表达式替换三元表达式。

例如：

public class Simplifier : ExpressionVisitor { public static Expression<T> Simplify<T>(Expression<T> expr) { return (Expression<T>) new Simplifier().Visit(expr); } protected override Expression VisitConditional(ConditionalExpression node) { var test = Visit(node.Test); var ifTrue = Visit(node.IfTrue); var ifFalse = Visit(node.IfFalse); var testConst = test as ConstantExpression; if(testConst != null) { var value = (bool) testConst.Value; return value ? ifTrue : ifFalse; } return Expression.Condition(test, ifTrue, ifFalse); } protected override Expression VisitMember(MemberExpression node) { // Closed-over variables are represented as field accesses to fields on a constant object. var field = (node.Member as FieldInfo); var closure = (node.Expression as ConstantExpression); if(closure != null) { var value = field.GetValue(closure.Value); return VisitConstant(Expression.Constant(value)); } return base.VisitMember(node); } }

用法示例：

void Main() { var b = true; Expression<Func<int, object>> expr = i => b ? i.ToString() : "N/A"; Console.WriteLine(expr.ToString()); // i => IIF(value(UserQuery+<>c__DisplayClass0).b, i.ToString(), "N/A") Console.WriteLine(Simplifier.Simplify(expr).ToString()); // i => i.ToString() b = false; Console.WriteLine(Simplifier.Simplify(expr).ToString()); // i => "N/A" }

所以，你可以在你的代码中使用这样的东西：

Expression<Func<IGrouping<DateTime, MyEntity>>, ClassYouWantToReturn> select = groupedDate => new { Calculation1 = doCalculation1 ? x.Sum(groupedDate.Column1) : 0), Calculation2 = doCalculation2 ? x.Count(groupedDate) : 0 }; var q = dbEntity .GroupBy(x => x.date) .Select(Simplifier.Simplify(select))

然而，这可能比它的价值更麻烦。 SQL Server几乎无疑会优化“1 == 1”的情况，并且允许Entity Framework生成不太漂亮的查询不应该被证明是性能问题。

更新

查看更新后的问题，这似乎是少数几个生成正确查询确实很重要的实例之一。

除了我建议的解决方案，还有一些其他的选择：你可以使用原始的sql映射到你的返回类型，或者你可以使用LinqKit根据你想要的选择不同的表达式，然后在你的Select中“调用”该表达式查询。

It would technically be possible to pass the Expression in your Select query through an expression tree visitor, which checks for constant values on the left-hand side of ternary operators, and replaces the ternary expression with the appropriate sub-expression.

For example:

public class Simplifier : ExpressionVisitor { public static Expression<T> Simplify<T>(Expression<T> expr) { return (Expression<T>) new Simplifier().Visit(expr); } protected override Expression VisitConditional(ConditionalExpression node) { var test = Visit(node.Test); var ifTrue = Visit(node.IfTrue); var ifFalse = Visit(node.IfFalse); var testConst = test as ConstantExpression; if(testConst != null) { var value = (bool) testConst.Value; return value ? ifTrue : ifFalse; } return Expression.Condition(test, ifTrue, ifFalse); } protected override Expression VisitMember(MemberExpression node) { // Closed-over variables are represented as field accesses to fields on a constant object. var field = (node.Member as FieldInfo); var closure = (node.Expression as ConstantExpression); if(closure != null) { var value = field.GetValue(closure.Value); return VisitConstant(Expression.Constant(value)); } return base.VisitMember(node); } }

Usage example:

void Main() { var b = true; Expression<Func<int, object>> expr = i => b ? i.ToString() : "N/A"; Console.WriteLine(expr.ToString()); // i => IIF(value(UserQuery+<>c__DisplayClass0).b, i.ToString(), "N/A") Console.WriteLine(Simplifier.Simplify(expr).ToString()); // i => i.ToString() b = false; Console.WriteLine(Simplifier.Simplify(expr).ToString()); // i => "N/A" }

So, you could use this in your code something like this:

Expression<Func<IGrouping<DateTime, MyEntity>>, ClassYouWantToReturn> select = groupedDate => new { Calculation1 = doCalculation1 ? x.Sum(groupedDate.Column1) : 0), Calculation2 = doCalculation2 ? x.Count(groupedDate) : 0 }; var q = dbEntity .GroupBy(x => x.date) .Select(Simplifier.Simplify(select))

However, this is probably more trouble than it's worth. SQL Server will almost undoubtedly optimize the "1 == 1" case away, and allowing Entity Framework to produce the less-pretty query shouldn't prove to be a performance problem.

Update

Looking at the updated question, this appears to be one of the few instances where producing the right query really does matter, performance-wise.

Besides my suggested solution, there are a few other choices: you could use raw sql to map to your return type, or you could use LinqKit to choose a different expression based on what you want, and then "Invoke" that expression inside your Select query.

更多推荐