C# 动态添加新的 lambda 表达式以创建过滤器

Dynamically add new lambda expressions to create a filter

提问by

I need to do some filtering on an ObjectSet to obtain the entities I need by doing this :

我需要对 ObjectSet 进行一些过滤以通过执行以下操作获取我需要的实体：

query = this.ObjectSet.Where(x => x.TypeId == 3); // this is just an example;

Later in the code (and before launching the deferred execution) I filter the query again like this :

在代码的后面（在启动延迟执行之前），我再次过滤查询，如下所示：

query = query.Where(<another lambda here ...>);

That works quite well so far.

到目前为止效果很好。

Here is my problem :

这是我的问题：

The entities contains a DateFromproperty and a DateToproperty, which are both DataTimetypes. They represent a period of time.

实体包含一个DateFrom属性和一个DateTo属性，它们都是DataTime类型。它们代表了一段时间。

I need to filter the entities to get only those that are part of a collectionof periods of time. The periods in the collection are not necessarily contiguous, so, the logic to retreive the entities looks like that :

我需要过滤的实体只拿到那些一个部分集合的时间段。集合中的周期不一定是连续的，因此，检索实体的逻辑如下所示：

entities.Where(x => x.DateFrom >= Period1.DateFrom and x.DateTo <= Period1.DateTo)
||
entities.Where(x => x.DateFrom >= Period2.DateFrom and x.DateTo <= Period2.DateTo)
||

... and on and on for all the periods in the collection.

... 以及集合中的所有时期。

I have tried doing that :

我试过这样做：

foreach (var ratePeriod in ratePeriods)
{
    var period = ratePeriod;

    query = query.Where(de =>
        de.Date >= period.DateFrom && de.Date <= period.DateTo);
}

But once I launch the deferred execution, it translates this into SQL just like I want it (one filter for each of the periods of time for as many periods there is in the collection), BUT, it translates to AND comparisons instead of OR comparisons, which returns no entities at all, since an entity cannot be part of more than one period of time, obviously.

但是，一旦我启动延迟执行，它就会像我想要的那样将其转换为 SQL（每个时间段都有一个过滤器，用于集合中的多个时间段），但是，它会转换为 AND 比较而不是 OR 比较，它根本不返回任何实体，因为一个实体显然不能属于多个时间段。

I need to build some sort of dynamic linq here to aggregate the period filters.

我需要在这里构建某种动态 linq 来聚合周期过滤器。

---

---

Update

更新

Based on hatten's answer, I've added the following member :

根据 hatten 的回答，我添加了以下成员：

private Expression<Func<T, bool>> CombineWithOr<T>(Expression<Func<T, bool>> firstExpression, Expression<Func<T, bool>> secondExpression)
{
    // Create a parameter to use for both of the expression bodies.
    var parameter = Expression.Parameter(typeof(T), "x");
    // Invoke each expression with the new parameter, and combine the expression bodies with OR.
    var resultBody = Expression.Or(Expression.Invoke(firstExpression, parameter), Expression.Invoke(secondExpression, parameter));
    // Combine the parameter with the resulting expression body to create a new lambda expression.
    return Expression.Lambda<Func<T, bool>>(resultBody, parameter);
}

Declared a new CombineWithOr Expression :

声明了一个新的 CombineWithOr 表达式：

Expression<Func<DocumentEntry, bool>> resultExpression = n => false;

And used it in my period collection iteration like this :

并在我的周期集合迭代中使用它，如下所示：

foreach (var ratePeriod in ratePeriods)
{
    var period = ratePeriod;
    Expression<Func<DocumentEntry, bool>> expression = de => de.Date >= period.DateFrom && de.Date <= period.DateTo;
    resultExpression = this.CombineWithOr(resultExpression, expression);
}

var documentEntries = query.Where(resultExpression.Compile()).ToList();

I looked at the resulting SQL and it's like the Expression has no effect at all. The resulting SQL returns the previously programmed filters but not the combined filters. Why ?

我查看了生成的 SQL，它就像表达式根本没有任何效果。生成的 SQL 返回先前编程的过滤器，但不返回组合过滤器。为什么 ？

---

---

Update 2

更新 2

I wanted to give feO2x's suggestion a try, so I have rewritten my filter query like this :

我想试试 feO2x 的建议，所以我重写了我的过滤器查询：

query = query.AsEnumerable()
    .Where(de => ratePeriods
        .Any(rp => rp.DateFrom <= de.Date && rp.DateTo >= de.Date))

As you can see, I added AsEnumerable()but the compiler gave me an error that it cannot convert the IEnumerable back to IQueryable, so I have added ToQueryable()at the end of my query :

如您所见，我添加了AsEnumerable()但编译器给了我一个错误，它无法将 IEnumerable 转换回 IQueryable，所以我ToQueryable()在查询的末尾添加了：

query = query.AsEnumerable()
    .Where(de => ratePeriods
        .Any(rp => rp.DateFrom <= de.Date && rp.DateTo >= de.Date))
            .ToQueryable();

Everything works fine. I can compile the code and launch this query. However, it doesn't fit my needs.

一切正常。我可以编译代码并启动此查询。但是，它不符合我的需求。

While profiling the resulting SQL, I can see that the filtering is not part of the SQL querybecause it filters the dates in-memory during the process. I guess that you already know about that and that is what you intended to suggest.

在分析生成的 SQL 时，我可以看到过滤不是 SQL 查询的一部分，因为它在此过程中过滤了内存中的日期。我想你已经知道了，这就是你打算提出的建议。

Your suggestion works, BUT, since it fetches all the entitiesfrom the database (and there are thousands and thousands of them) before filtering them in-memory, it's really slow to get back that huge amount from the database.

你的建议有效，但是，因为它在过滤内存之前从数据库中获取所有实体（并且有成千上万个），所以从数据库中取回大量实体真的很慢。

What I really want is to send the period filtering as part of the resulting SQL query, so it won't return a huge amount of entities before finishing up with the filtering process.

我真正想要的是将句点过滤作为结果 SQL 查询的一部分发送，因此在完成过滤过程之前它不会返回大量实体。

采纳答案by hattenn

Despite the good suggestions, I had to go with the LinqKitone. One of the reasons is that I will have to repeat the same kind of predicate aggregation in many other places in the code. Using LinqKit is the easiest one, not to mention I can get it done by writing only a few lines of code.

尽管有很好的建议，我还是不得不选择LinqKit。原因之一是我将不得不在代码的许多其他地方重复相同类型的谓词聚合。使用 LinqKit 是最简单的一种，更不用说我只需编写几行代码就可以完成。

Here is how I solved my problem using LinqKit :

以下是我如何使用 LinqKit 解决我的问题：

var predicate = PredicateBuilder.False<Document>();
foreach (var submittedPeriod in submittedPeriods)
{
    var period = period;
    predicate = predicate.Or(d =>
        d.Date >= period.DateFrom && d.Date <= period.DateTo);
}

And I launch deferred execution (note that I call AsExpandable()just before) :

我启动了延迟执行（请注意，我AsExpandable()之前调用过）：

var documents = this.ObjectSet.AsExpandable().Where(predicate).ToList();

I looked at the resulting SQL and it does a good job at translating my predicates into SQL.

我查看了生成的 SQL，它在将谓词转换为 SQL 方面做得很好。

回答by feO2x

How about this code:

这段代码怎么样：

var targets = query.Where(de => 
    ratePeriods.Any(period => 
        de.Date >= period.DateFrom && de.Date <= period.DateTo));

I use the LINQ Anyoperator to determine if there is any rate period that conforms to de.Date. Although I'm not quite sure how this is translated into efficient SQL statements by entity. If you could post the resulting SQL, that would be quite interesting for me.

我使用 LINQAny运算符来确定是否存在符合de.Date. 尽管我不太确定如何将其转换为实体的高效 SQL 语句。如果您可以发布生成的 SQL，那对我来说会很有趣。

Hope this helps.

希望这可以帮助。

UPDATE after hattenn's answer:

在 Hattenn 的回答后更新：

I don't think that hattenn's solution would work, because Entity Framework uses LINQ expressions to produce the SQL or DML that is executed against the database. Therefore, Entity Framework relies on the IQueryable<T>interface rather than IEnumerable<T>. Now the default LINQ operators (like Where, Any, OrderBy, FirstOrDefault and so on) are implemented on both interfaces, thus the difference is sometimes hard to see. The main difference of these interfaces is that in case of the IEnumerable<T>extension methods, the returned enumerables are continuously updated without side effects, while in the case of IQueryable<T>the actual expression is recomposed, which is not free of side effects (i.e. you are altering the expression tree that is finally used to create the SQL query).

我不认为 hattenn 的解决方案会起作用，因为实体框架使用 LINQ 表达式来生成对数据库执行的 SQL 或 DML。因此，实体框架依赖于IQueryable<T>接口而不是IEnumerable<T>. 现在默认的 LINQ 运算符（如 Where、Any、OrderBy、FirstOrDefault 等）在两个接口上都实现了，因此有时很难看出差异。这些接口的主要区别在于，在IEnumerable<T>扩展方法的情况下，返回的枚举不断更新，没有副作用，而在IQueryable<T>实际表达式的情况下，重新组合，这不是没有副作用（即您正在更改表达式最终用于创建 SQL 查询的树）。

Now Entity Framework supports the ca. 50 standard query operators of LINQ, but if you write your own methods that manipulate an IQueryable<T>(like hatenn's method), this would result in an expression tree that Entity Framework might not be able to parse because it simply doesn't know the new extension method. This might be the cause why you cannot see the combined filters after you composed them (although I would expect an exception).

现在实体框架支持 ca。LINQ 的 50 个标准查询运算符，但是如果您编写自己的方法来操作IQueryable<T>（如 hatnn 的方法），这将导致实体框架可能无法解析的表达式树，因为它根本不知道新的扩展方法. 这可能是您在组合过滤器后看不到组合过滤器的原因（尽管我希望有一个例外）。

When does the solution with the Any operator work:

Any 运算符的解决方案何时起作用：

In the comments, you told that you encountered a System.NotSupportedException: Unable to create a constant value of type 'RatePeriod'. Only primitive types or enumeration types are supported in this context.This is the case when the RatePeriodobjects are in-memory objects and not tracked by the Entity Framework ObjectContextor DbContext. I made a small test solution that can be downloaded from here: https://dl.dropboxusercontent.com/u/14810011/LinqToEntitiesOrOperator.zip

在评论中，您告诉您遇到了System.NotSupportedException：无法创建“RatePeriod”类型的常量值。在此上下文中仅支持原始类型或枚举类型。当RatePeriod对象是内存中的对象并且不被实体框架ObjectContext或DbContext. 我做了一个小测试解决方案，可以从这里下载：https: //dl.dropboxusercontent.com/u/14810011/LinqToEntitiesOrOperator.zip

I used Visual Studio 2012 with LocalDB and Entity Framework 5. To see the results, open the class LinqToEntitiesOrOperatorTest, then open Test Explorer, build the solution and run all tests. You will recognize that ComplexOrOperatorTestWithInMemoryObjectswill fail, all the others should pass.

我将 Visual Studio 2012 与 LocalDB 和实体框架 5 一起使用。要查看结果，请打开类LinqToEntitiesOrOperatorTest，然后打开测试资源管理器，构建解决方案并运行所有测试。你会认识到那ComplexOrOperatorTestWithInMemoryObjects会失败，所有其他的都应该通过。

The context I used looks like this:

我使用的上下文如下所示：

public class DatabaseContext : DbContext
{
    public DbSet<Post> Posts { get; set; }
    public DbSet<RatePeriod> RatePeriods { get; set; }
}
public class Post
{
    public int ID { get; set; }
    public DateTime PostDate { get; set; }
}
public class RatePeriod
{
    public int ID { get; set; }
    public DateTime From { get; set; }
    public DateTime To { get; set; }
}

Well, it is as simple as it gets :-). In the test project, there are two important unit test methods:

好吧，它就这么简单:-)。在测试项目中，有两个重要的单元测试方法：

    [TestMethod]
    public void ComplexOrOperatorDBTest()
    {
        var allAffectedPosts =
            DatabaseContext.Posts.Where(
                post =>
                DatabaseContext.RatePeriods.Any(period => period.From < post.PostDate && period.To > post.PostDate));

        Assert.AreEqual(3, allAffectedPosts.Count());
    }

    [TestMethod]
    public void ComplexOrOperatorTestWithInMemoryObjects()
    {
        var inMemoryRatePeriods = new List<RatePeriod>
            {
                new RatePeriod {ID = 1000, From = new DateTime(2002, 01, 01), To = new DateTime(2006, 01, 01)},
                new RatePeriod {ID = 1001, From = new DateTime(1963, 01, 01), To = new DateTime(1967, 01, 01)}
            };

        var allAffectedPosts =
            DatabaseContext.Posts.Where(
                post => inMemoryRatePeriods.Any(period => period.From < post.PostDate && period.To > post.PostDate));
        Assert.AreEqual(3, allAffectedPosts.Count());
    }

Notice that the first method passes while the second one fails with the exception mentioned above, although both methods do exactly the same thing, except that in the second case I created rate period objects in memory the DatabaseContextdoes not know about.

请注意，第一个方法通过，而第二个方法因上述异常而失败，尽管这两种方法做的事情完全相同，但在第二种情况下，我在内存中创建了DatabaseContext不知道的rate period 对象。

What can you do to solve this problem?

你能做些什么来解决这个问题？

1. Do your RatePeriodobjects reside in the same ObjectContextor DbContext, respectively? Then use them right from it like I did in the first unit test mentioned above.

2. If not, can you load all your posts at once or would this result in an OutOfMemoryException? If not, you could use the following code. Notice the AsEnumerable()call that results in the Whereoperator being used against the IEnumerable<T>interface instead of IQueryable<T>. Effectively, this results in all posts being loaded into memory and then filtered:

   [TestMethod]
   public void CorrectComplexOrOperatorTestWithInMemoryObjects()
   {
       var inMemoryRatePeriods = new List<RatePeriod>
           {
               new RatePeriod {ID = 1000, From = new DateTime(2002, 01, 01), To = new DateTime(2006, 01, 01)},
               new RatePeriod {ID = 1001, From = new DateTime(1963, 01, 01), To = new DateTime(1967, 01, 01)}
           };
   
       var allAffectedPosts =
           DatabaseContext.Posts.AsEnumerable()
                          .Where(
                              post =>
                              inMemoryRatePeriods.Any(
                                  period => period.From < post.PostDate && period.To > post.PostDate));
       Assert.AreEqual(3, allAffectedPosts.Count());
   }
   

3. If the second solution is not possible, then I would recommend to write a TSQL stored procedure where you pass in your rate periods and that forms the correct SQL statement. This solution is also the most performant one.

1. 您的RatePeriod对象分别位于相同ObjectContext或 中DbContext吗？然后像我在上面提到的第一个单元测试中那样直接使用它们。

2. 如果没有，您可以一次加载所有帖子还是会导致OutOfMemoryException? 如果没有，您可以使用以下代码。请注意AsEnumerable()导致Where操作符被用于IEnumerable<T>接口而不是的调用IQueryable<T>。实际上，这会导致所有帖子都被加载到内存中，然后被过滤：

   [TestMethod]
   public void CorrectComplexOrOperatorTestWithInMemoryObjects()
   {
       var inMemoryRatePeriods = new List<RatePeriod>
           {
               new RatePeriod {ID = 1000, From = new DateTime(2002, 01, 01), To = new DateTime(2006, 01, 01)},
               new RatePeriod {ID = 1001, From = new DateTime(1963, 01, 01), To = new DateTime(1967, 01, 01)}
           };
   
       var allAffectedPosts =
           DatabaseContext.Posts.AsEnumerable()
                          .Where(
                              post =>
                              inMemoryRatePeriods.Any(
                                  period => period.From < post.PostDate && period.To > post.PostDate));
       Assert.AreEqual(3, allAffectedPosts.Count());
   }
   

3. 如果第二种解决方案是不可能的，那么我建议编写一个 TSQL 存储过程，在其中传递您的费率周期并形成正确的 SQL 语句。此解决方案也是性能最高的解决方案。

回答by hattenn

You can use a method like the following:

您可以使用如下方法：

Expression<Func<T, bool>> CombineWithOr<T>(Expression<Func<T, bool>> firstExpression, Expression<Func<T, bool>> secondExpression)
{
    // Create a parameter to use for both of the expression bodies.
    var parameter = Expression.Parameter(typeof(T), "x");
    // Invoke each expression with the new parameter, and combine the expression bodies with OR.
    var resultBody = Expression.Or(Expression.Invoke(firstExpression, parameter), Expression.Invoke(secondExpression, parameter));
    // Combine the parameter with the resulting expression body to create a new lambda expression.
    return Expression.Lambda<Func<T, bool>>(resultBody, parameter);
}

And then:

进而：

Expression<Func<T, bool>> resultExpression = n => false; // Always false, so that it won't affect the OR.
foreach (var ratePeriod in ratePeriods)
{
    var period = ratePeriod;
    Expression<Func<T, bool>> expression = (de => de.Date >= period.DateFrom && de.Date <= period.DateTo);
    resultExpression = CombineWithOr(resultExpression, expression);
}

// Don't forget to compile the expression in the end.
query = query.Where(resultExpression.Compile());

For more information, you may want to check out the following:

有关更多信息，您可能需要查看以下内容：

Combining two expressions (Expression<Func<T, bool>>)

组合两个表达式 (Expression<Func<T, bool>>)

http://www.albahari.com/nutshell/predicatebuilder.aspx

http://www.albahari.com/nutshell/predicatebuilder.aspx

Edit:The line Expression<Func<DocumentEntry, bool>> resultExpression = n => false;is just a placeholder. CombineWithOrmethod needs two methods to combine, if you write Expression<Func<DocumentEntry, bool>> resultExpression;', you can't use it in the call toCombineWithOrfor the first time in yourforeach` loop. It's just like the following code:

编辑：该行Expression<Func<DocumentEntry, bool>> resultExpression = n => false;只是一个占位符。CombineWithOr方法需要两个方法组合，如果你写Expression<Func<DocumentEntry, bool>> resultExpression;', you can't use it in the call toCombineWithOr for the first time in yourforeach`循环。就像下面的代码：

int resultOfMultiplications = 1;
for (int i = 0; i < 10; i++)
    resultOfMultiplications = resultOfMultiplications * i;

If there's nothing in resultOfMultiplicationsto begin with, you cannot use it in your loop.

如果resultOfMultiplications开始时没有任何内容，则不能在循环中使用它。

As to why the lambda is n => false. Because it doesn't have any effect in an ORstatement. For example, false OR someExpression OR someExpressionis equal to someExpression OR someExpression. That falsedoesn't have any effect.

至于为什么 lambda 是n => false. 因为它在OR语句中没有任何作用。例如，false OR someExpression OR someExpression等于someExpression OR someExpression。那false没有任何影响。

回答by hattenn

Anyways, I think dynamic LINQ query creation was not as simple as I thought. Try using Entity SQL, similar to the way below:

无论如何，我认为动态 LINQ 查询创建并不像我想象的那么简单。尝试使用 Entity SQL，类似于下面的方式：

var filters = new List<string>();
foreach (var ratePeriod in ratePeriods)
{
    filters.Add(string.Format("(it.Date >= {0} AND it.Date <= {1})", ratePeriod.DateFrom, ratePeriod.DateTo));
}

var filter = string.Join(" OR ", filters);
var result = query.Where(filter);

This may not be exactly correct (I haven't tried it), but it should be something similar to this.

这可能不完全正确（我没有尝试过），但它应该与此类似。