LINQ to Entities: Combining Predicates

LINQ to Entities: Combining Predicates

Rate This
  • Comments 14

Someone asked a great question on the ADO.NET Entity Framework forums yesterday: how do I compose predicates in LINQ to Entities? I’ll give three answers to the question.

Answer 1: Chaining query operators

Basically, you have some query and you have some predicates you want to apply to that query (“the car is red”, “the car costs less than $10”). If both conditions need to be satisfied, you can just chain together some calls to Where (“the car is red and costs less than $10”):

Expression<Func<Car, bool>> theCarIsRed = c => c.Color == "Red";

Expression<Func<Car, bool>> theCarIsCheap = c => c.Price < 10.0;

IQueryable<Car> carQuery = ;

var query = carQuery.Where(theCarIsRed).Where(theCarIsCheap);

 

If you’re willing to exceed the $10 budget for cars that are red, you can chain Unions instead (“the car is red or the car costs less than $10”):

var query2 = carQuery.Where(theCarIsRed).Union(carQuery.Where(theCarIsCheap));

 

This last query has a couple of problems: it’s inefficient (because of the unions) and it eliminates duplicates in the results, something that would not happen if I applied a single predicate.

Answer 2: Build expressions manually

The LINQ Expressions API includes factory methods that allow you to build up the predicate by hand. I can define the conditions (with respect to a “car” parameter) as follows:

ParameterExpression c = Expression.Parameter(typeof(Car), "car");

Expression theCarIsRed = Expression.Equal(Expression.Property(c, "Color"), Expression.Constant("Red"));

Expression theCarIsCheap = Expression.LessThan(Expression.Property(c, "Price"), Expression.Constant(10.0));

Expression<Func<Car, bool>> theCarIsRedOrCheap = Expression.Lambda<Func<Car, bool>>(

    Expression.Or(theCarIsRed, theCarIsCheap), c);

var query = carQuery.Where(theCarIsRedOrCheap);

 

Building queries by hand isn’t very convenient. If you’re already building expressions from scratch, this is a good approach but otherwise I’d suggest something different…

Answer 3: Composing Lambda Expresions

The Albaharis suggest combining bodies of lambda expressions in their C# 3.0 book (a great resource for all things C# and LINQ). This allows you to describe the parts of the expression using the lambda syntax and build an aggregate expression:

Expression<Func<Car, bool>> theCarIsRed = c1 => c1.Color == "Red";

Expression<Func<Car, bool>> theCarIsCheap = c2 => c2.Price < 10.0;

Expression<Func<Car, bool>> theCarIsRedOrCheap = Expression.Lambda<Func<Car, bool>>(

    Expression.Or(theCarIsRed.Body, theCarIsCheap.Body), theCarIsRed.Parameters.Single());

var query = carQuery.Where(theCarIsRedOrCheap);

 

I’m taking the bodies of the two conditions and Oring them in a new lambda expression. There is a subtle problem however: the parameter for the merged expression (c1) is taken from “theCarIsRed”, which leaves us with a dangling parameter (c2) from “theCarIsCheap”. The resulting query is invalid. How can I force “theCarIsCheap” to use the same parameter? The answer is to invoke the expression using the common parameter:

ParameterExpression p = theCarIsRed.Parameters.Single();

Expression<Func<Car, bool>> theCarIsRedOrCheap = Expression.Lambda<Func<Car, bool>>(

    Expression.Or(theCarIsRed.Body, Expression.Invoke(theCarIsCheap, p)), p);

 

Here’s the problem: LINQ to Entities does not support InvocationExpressions. Rather than invoking the expression with c1, I can manually rebind the parameter. Matt Warren’s series of articles on IQueryable providers includes an ExpressionVisitor implementation that makes it easy to rewrite expression trees. If you do any LINQ expression manipulation, this class is a crucial tool. Here’s an implementation of the visitor that rebinds parameters:

public class ParameterRebinder : ExpressionVisitor {

    private readonly Dictionary<ParameterExpression, ParameterExpression> map;

 

    public ParameterRebinder(Dictionary<ParameterExpression, ParameterExpression> map) {

        this.map = map ?? new Dictionary<ParameterExpression, ParameterExpression>();

    }

 

    public static Expression ReplaceParameters(Dictionary<ParameterExpression, ParameterExpression> map, Expression exp) {

        return new ParameterRebinder(map).Visit(exp);

    }

 

    protected override Expression VisitParameter(ParameterExpression p) {

        ParameterExpression replacement;

        if (map.TryGetValue(p, out replacement)) {

            p = replacement;

        }

        return base.VisitParameter(p);

    }

}

 

Now I can write a general utility method to compose lambda expressions without using invoke (I’ll call it Compose), and leverage it to implement EF-friendly And and Or builder methods:

public static class Utility {

    public static Expression<T> Compose<T>(this Expression<T> first, Expression<T> second, Func<Expression, Expression, Expression> merge) {

        // build parameter map (from parameters of second to parameters of first)

        var map = first.Parameters.Select((f, i) => new { f, s = second.Parameters[i] }).ToDictionary(p => p.s, p => p.f);

 

        // replace parameters in the second lambda expression with parameters from the first

        var secondBody = ParameterRebinder.ReplaceParameters(map, second.Body);

 

        // apply composition of lambda expression bodies to parameters from the first expression 

        return Expression.Lambda<T>(merge(first.Body, secondBody), first.Parameters);

    }

 

    public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second) {

        return first.Compose(second, Expression.And);

    }

 

    public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second) {

        return first.Compose(second, Expression.Or);

    }

}

 

To combine lambda expressions, I can write:

Expression<Func<Car, bool>> theCarIsRed = c => c.Color == "Red";

Expression<Func<Car, bool>> theCarIsCheap = c => c.Price < 10.0;

Expression<Func<Car, bool>> theCarIsRedOrCheap = theCarIsRed.Or(theCarIsCheap);

var query = carQuery.Where(theCarIsRedOrCheap);

 

I’ll use this last answer as an excuse to discuss variations on the visitor pattern in a future post...

Leave a Comment
  • Please add 6 and 8 and type the answer here:
  • Post
  • This is pretty much true of the entire expression tree stack, independent of LINQ to Entities (eg, it applies to LINQ to SQL as well as other expression-tree based providers).

  • Good point. Despite the title, these techniques are generally applicable to the LINQ to * family. Note that LINQ to SQL supports the simpler version of answer 3 using Expression.Invoke: there is no need to manually rebind the parameters.

  • A beta of Visual Studio 2008 SP1 was released on Monday and the ADO.NET Entity Framework (EF) is now

  • I talked a little bit about patterns using InvocationExpression in a previous post (you might want to

  • Imagine if you have a table of People and you want to retrieve only those whose the Firstname is in a

  • Update: I've enhanced LINQKit so that it automatically rebinds InvocationExpressions. This means that you can now use PredicateBuilder with Entity Framework - simply by inserting a call to AsExpandable().

    Details here:

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

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

    Joe

  • I talked a little bit about patterns using InvocationExpression in a previous post (you might want to

  • Imagine if you have a table of People and you want to retrieve only those whose the Firstname is in a

  • Great and very useful article. Thank you!

  • Thank you so much, been banging my head against a wall for the last day and a half trying to get the EF equivalent of a (non existent) ContainsAll(List<string>) expression without having to materialise the entities on the server first for further in memory processing.  Using this technique nailed it for my specific implementation in about an hour.

  • As the original question-asker, I thought I'd post a link to the updated PredicateBuilder I I've been using since Colin originally posted this (almost three years ago! eek!).

    petemontgomery.wordpress.com/.../a-universal-predicatebuilder

    Additionally, there's a slight issue with the And and Or methods above, which should use Expression.AndAlso and Expression.OrElse to create logical (not bitwise) operator expressions. This is not apparently a problem for the EF translator, though.

    Thanks again Colin...!

  • Hello, I've a bit more complicated situation: I'm trying to replace an expression subtree within my custom expression visitor - a method call should become a lambda expression (design idea: avoid replicating a LOT of LINQ in our project, e.g. mark the 'the same LINQ should be inserted here' with some code-marker, e.g. some fake method-call). Now, the problem is, the newly generated LINQ expression in the custom visitor, doesn't bind its lambda parameter to the originally supplied method-call argument. I've placed a question to stackoverflow but didn't get any answer: stackoverflow.com/.../how-to-bind-parameters-in-replaced-expression-nodes-in-entity-framework-on-the-f. Would you please look at it and may be answer it there? Thank you very much, Andrej

  • Hello Colin,

    although we are in 2014 (after 6 years), but this article help me so much to handle a filter object

    thanks

    Taraman

  • how could you solve the problem stackoverflow.com/.../dynamic-linq-building-expression.

Page 1 of 1 (14 items)