动态生成Linq Select [英] Dynamically generate Linq Select

问题描述

我有一个数据库，用户可以运行各种计算。计算运行在4个不同的列上，每个计算不一定使用每列，即compute1可能会变成sql，如

  SELECT SUM（Column1 ）
 FROM TABLE 
 WHERE Column1不为空
  

并且calculate2将为

  SELECT SUM（Column2）
 WHERE Column2为null 
  

我正在尝试通过linq生成这个数据，我可以通过计算每一次，如

  table.Where（x => x.Column1！= null）
 .Where（x => x.Column2 == null）
。 GroupBy（x => x.Date）
 .Select（dateGroup => new 
 {
 Calculation1 = dateGroup.Sum（x => x.Column1！= null），
 Calculation2 = dateGroup.Sum（x => x.Column2 == null）
} 
  

问题是我的数据集非常大，所以我不喔nt执行计算，除非用户已经请求。我已经研究了动态生成Linq查询。所有我现在发现的是PredicateBuilder和DynamicSQL，它们似乎只能用于动态生成Where谓词，并将sql查询本身硬编码为一个字符串，并在必要时插入Sum（Column1）或Sum（Column2）。

如何将Select查询的不同部分动态添加到这样的匿名类型？或者我应该看一个完全不同的处理方式。

解决方案

我解决了这个问题，让自己不得不失去类型安全与动态Linq通过使用一个恶意的解决方法。我有一个包含bools的对象，它对应于我想要做的计算，例如

  public class CalculationChecks 
 {
 bool doCalculation1 {get; set;} 
 bool doCalculation2 {get; set;} 
} 
  

然后在我的选择中进行检查，以确定是否应该进行计算或返回一个常量，像这样

  Select（x => new 
 {
 Calculation1 = doCalculation1？DoCalculation1（x）：0，
 Calculation2 = doCalculation2？DoCalculation2（x）：0 
 } 
  

但是，这似乎是linq到sql或ef的一个边缘情况，导致生成sql仍然执行DoCalculation1（）和DoCalculation2中指定的计算，然后使用case语句来决定是否将数据返回给我，它的运行速度显着降低了40-60％，执行计划显示它使用的查询效率要低得多。

解决此问题的方法是使用ExpressionVisitor来执行表达式，如果相应的bool为false，则删除计算。显示如何实现此ExpressionVisitor的代码由@StriplingWarrior在此问题上提供有EF Linq选择语句选择常量或函数

将这两个解决方案结合在一起仍然不会创建运行在100的sql ％sql的速度。在测试中，无论测试集的大小是否在普通sql的10s内，执行计划的主要部分是相同的

I have a database that users can run a variety of calculations on. The calculations run on 4 different columns each calculation does not necessarily use every column i.e. calculation1 might turn into sql like

SELECT SUM(Column1) 
FROM TABLE 
WHERE Column1 is not null

and calculation2 would be

SELECT SUM(Column2)
WHERE Column2 is null

I am trying to generate this via linq and I can get the correct data back by calculating everything every time such as

table.Where(x => x.Column1 != null)
     .Where(x => x.Column2 == null)
     .GroupBy(x => x.Date)
     .Select(dateGroup => new
             {
               Calculation1 = dateGroup.Sum(x => x.Column1 != null),
               Calculation2 = dateGroup.Sum(x => x.Column2 == null)
             }

The problem is that my dataset is very large, and so I do not want to perform a calculation unless the user has requested it. I have looked into dynamically generating Linq queries. All I have found so far is PredicateBuilder and DynamicSQL, which appear to only be useful for dynamically generating the Where predicate, and hardcoding the sql query itself as a string with the Sum(Column1) or Sum(Column2) being inserted when necessary.

How would one go about dynamically adding the different parts of the Select query into an anonymous type like this? Or should I be looking at an entirely different way of handling this

解决方案

I solved this and kept myself from having to lose type safety with Dynamic Linq by using a hacky workaround. I have a object containing bools that correspond to what calculations I want to do such as

public class CalculationChecks
{
  bool doCalculation1 {get;set;}
  bool doCalculation2 {get;set;}
}

and then do a check in my select for whether or not I should do the calculation or return a constant, like so

Select(x => new 
{
  Calculation1 = doCalculation1 ? DoCalculation1(x) : 0,
  Calculation2 = doCalculation2 ? DoCalculation2(x) : 0
}

However, this appears to be an edge case with linq to sql or ef, that causes the generated sql to still do the calculations specified in DoCalculation1() and DoCalculation2 and then use a case statement to decide whether or not its going to return the data to me. It runs signficantly slower,40-60% in testing, and the execution plan shows that it uses a much more inefficient query.

The solution to this problem was to use an ExpressionVisitor to go through the expression and remove the calculations if the corresponding bool was false. The code showing how implement this ExpressionVisitor was provided by @StriplingWarrior on this question Have EF Linq Select statement Select a constant or a function

Using both of these solutions together is still not creating sql that runs at 100% the speed of plain sql. In testing it was within 10s of plain sql no matter the size of the test set, and the major portions of the execution plan were the same

这篇关于动态生成Linq Select的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！