LINQ to Entities: Combining Predicates
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 ExpressionVisitorimplementation 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...
Comments
Anonymous
May 02, 2008
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).Anonymous
May 03, 2008
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.Anonymous
May 13, 2008
A beta of Visual Studio 2008 SP1 was released on Monday and the ADO.NET Entity Framework (EF) is nowAnonymous
December 07, 2008
I talked a little bit about patterns using InvocationExpression in a previous post (you might want toAnonymous
March 25, 2009
Imagine if you have a table of People and you want to retrieve only those whose the Firstname is in aAnonymous
April 04, 2009
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 JoeAnonymous
June 09, 2009
I talked a little bit about patterns using InvocationExpression in a previous post (you might want toAnonymous
June 21, 2009
Imagine if you have a table of People and you want to retrieve only those whose the Firstname is in aAnonymous
January 11, 2011
Great and very useful article. Thank you!Anonymous
January 24, 2011
The comment has been removedAnonymous
February 10, 2011
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...!Anonymous
May 25, 2012
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, AndrejAnonymous
May 03, 2014
Hello Colin, although we are in 2014 (after 6 years), but this article help me so much to handle a filter object thanks TaramanAnonymous
May 17, 2014
how could you solve the problem stackoverflow.com/.../dynamic-linq-building-expression.Anonymous
May 28, 2015
Good article, thanks! It looks like ParameterRebinder has to return replacement rather then assign it to param expression, which is just a local copy of reference p = replacement;