Dynamically add new lambda expressions to create a filter

Question

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

That works quite well so far.

Here is my problem :

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.

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.

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

---

Update

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

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

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 ?

---

Update 2

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

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.

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.

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.

Answer 1

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.

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

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

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.