动态地添加新的lambda表达式来创建过滤器 [英] Dynamically add new lambda expressions to create a filter

问题描述

我需要做一些过滤上的对象集，以获得我需要通过这样的实体：

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

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

这工作得很好至今。

这是我的问题：

中的实体包含 DateFrom 属性和 DateTo 属性，这两者都是< STRONG> DATATIME 类型。他们代表着的的时间段的

The entities contains a DateFrom property and a DateTo property, which are both DataTime types. They represent a period of time.

我需要过滤的实体只拿到那些一个集合的一部分的的时间段的。集合中的期间的不必是连续，所以，中检索实体逻辑看起来像：

I need to filter the entities to get only those that are part of a collection of 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.

我曾尝试这样做：

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

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

但是，一旦我启动延迟执行，其转换为SQL这就像我希望它（每个时间尽可能多的时间周期的一个过滤器有集合中），但把它翻译成和比较，而不是或比较，这都没有返回单位，因为一个实体不能是多出部分不是一个时间段，效果显着。

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.

我要在这里建立某种动态的LINQ的聚合期间的过滤器。

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

更新

根据哈滕的回答，我已经添加了以下成员

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

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

宣布新CombineWithOr表达：

Declared a new CombineWithOr Expression :

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

我看着产生的SQL，它就像表达没有任何作用的。由此产生的SQL返回先前设定的过滤器，但不是组合过滤器。为什么呢？

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 ?

更新2

我想给feO2x的建议一试，所以我重写我的筛选查询是这样的：

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

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

正如你所看到的，我加入 AsEnumerable（）但是编译器给了我一个错误，它无法转换IEnumerable的回IQueryable的，所以我增加了 ToQueryable（）我查询的末尾：

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 :

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.

虽然分析结果SQL，我可以看到过滤的不是SQL查询的一部分，因为它在此过程中过滤器在内存中的日期。我猜你已经知道这一点，那就是你打算什么建议。

While profiling the resulting SQL, I can see that the filtering is not part of the SQL query because 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.

您的建议的作品，但是，由于它取的所有实体从数据库（也有他们的成千上万）内存过滤之前，它很慢，从数据库取回数额巨大。

Your suggestion works, BUT, since it fetches all the entities from 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.

我真正想要的是发送周期过滤作为结果的SQL查询的一部分，所以它不会返回一个巨大的量在过滤过程完成之前的实体。

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.

推荐答案

尽管好的建议，我不得不去与 LinqKit 之一。其中一个原因是，我将不得不重复同一种谓词聚集在代码中的许多其他地方。使用LinqKit是最容易的，更何况，我可以把它写的代码只有几行完成

Despite the good suggestions, I had to go with the LinqKit one. 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我的问题：

Here is how I solved my problem using 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);
}

和我启动延迟执行（请注意，我称之为 AsExpandable（）之前）：

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

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

我看着产生的SQL，并在翻译我的谓词到SQL做得很好。

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

这篇关于动态地添加新的lambda表达式来创建过滤器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！