CODEWITHSUNDEEP
c#linqlinq-to-sql
Fran CD
Fran CD

Reputation: 327

Linq-to-SQL combining two Expression<Func<T, bool>> ("where clause") with and/or

First, I must say that I have read several post about this at StackOverflow but I cannot get the desired result.

Let me explain the context (simplified): I'm using Linq-to-SQL to query customers with recent visits to the store and (optionally) get only those with certain amount of payments. Suppose I have a model with client, visits and payments classes.

So, focusing on the Where expression, I'm trying this:

Expression<Func<Entityes.Clients, bool>> filter = null;

filter = c => 
          c.Visits.Any(v => v.VisitDate > DateTime.Now.Date.AddMonths(-(int)visitsSince));


if (minPayment.HasValue && minPayment.Value > 0)
{
     filter.And(
               c => c.Payments.Sum(p => p.Quantity) > minPayment.Value);
}

The filter.And method is an extension method, recommended at this forum, below you can see the definition:

public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> expression1,
                                            Expression<Func<T, bool>> expression2)
{
  InvocationExpression invokedExpression =
      Expression.Invoke(expression2, expression1.Parameters.Cast<Expression>());

  return Expression.Lambda<Func<T, bool>>
      (Expression.And(expression1.Body, invokedExpression), expression1.Parameters);
}

However, this doesn't work for me as expected and results are not filtered by payment amount. There is no error with data or linq-to-sql model because this code works fine:

filter = c => 
           c.Visits.Any(v => v.VisitDate > DateTime.Now.Date.AddMonths(-(int)visitsSince)))
             && c => c.Payments.Sum(p => p.Quantity) > minPayment.Value;

However, I don't want to use the last code because I have some more complex scenarios in which I need to build the filter expression step-by-step with an 'and/or' combination of each part.

PD: I'm a "ADO.Net DataSets" guy trying to learn Linq-to-SQL and soon Entity Framework, I hope to be useful to the StackOverflow community soon.

Upvotes: 6

Views: 2784

Answers (3)

Andrew Cooper
Andrew Cooper

Reputation: 32576

The problem is this line:

filter.And(c => c.Payments.Sum(p => p.Quantity) > minPayment.Value);

The And method returns another delegate which you haven't assigned to anything. Try this:

filter = filter.And(c => c.Payments.Sum(p => p.Quantity) > minPayment.Value);

Just to clarify, And is a method which takes two expressions and combines their logic to create a new expression. It doesn't change the expression it's executed on, but returns the new expression as a return value. So in your code you're calling filter.And(...) but not doing anything with the return value, so filter still references the original lambda expression.

Upvotes: 1

Andrew Aitken
Andrew Aitken

Reputation: 338

Check out LinqKit and it's PredicateBuilder class: http://www.albahari.com/nutshell/predicatebuilder.aspx

Upvotes: 0

Krizz
Krizz

Reputation: 11542

You are not assigning the result of Anding to anything.

I suppose the respective part of your code should read as following:

if (minPayment.HasValue && minPayment.Value > 0)
{
   filter = filter.And(
            c => c.Payments.Sum(p => p.Quantity) > minPayment.Value);
}

The thing is that your (or SO's as you mentioned) And function returns the expression containing the conjunction of two expression. It does not alter the object it is invoked on.

Upvotes: 4

Related Questions