Monty’s Gush

A universal PredicateBuilder

Posted on: February 10, 2011

I don’t use the famous Albahari PredicateBuilder because it doesn’t work so well with Entity Framework or the latest version of NHibernate.

This is because it relies on InvocationExpressions, which aren’t supported by many query translators, and the workaround requires an unnatural call to TomasAsExpandable method in all your queries.

A couple of years ago, Colin Meek on the EF team showed me how to compose predicates without using InvocationExpressions and since then I’ve used my own synthesized version of PredicateBuilder, with the same API as the awesome Albahari original.

The same API also allows a lovely point-free style of composition. Say you have a list of predicates you want to “or” together, use the standard Aggregate function like so:

var predicate = predicates.Aggregate(PredicateBuilder.Or);

If you need a PredicateBuilder that works as you’d expect with Entity Framework, NHibernate… as well as Linq to SQL, here it is. (It ought to work in Silverlight too. Let me know…!)

    /// <summary>
    /// Enables the efficient, dynamic composition of query predicates.
    /// </summary>
    public static class PredicateBuilder
    {
        /// <summary>
        /// Creates a predicate that evaluates to true.
        /// </summary>
        public static Expression<Func<T, bool>> True<T>() { return param => true; }

        /// <summary>
        /// Creates a predicate that evaluates to false.
        /// </summary>
        public static Expression<Func<T, bool>> False<T>() { return param => false; }

        /// <summary>
        /// Creates a predicate expression from the specified lambda expression.
        /// </summary>
        public static Expression<Func<T, bool>> Create<T>(Expression<Func<T, bool>> predicate) { return predicate; }

        /// <summary>
        /// Combines the first predicate with the second using the logical "and".
        /// </summary>
        public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second)
        {
            return first.Compose(second, Expression.AndAlso);
        }

        /// <summary>
        /// Combines the first predicate with the second using the logical "or".
        /// </summary>
        public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second)
        {
            return first.Compose(second, Expression.OrElse);
        }

        /// <summary>
        /// Negates the predicate.
        /// </summary>
        public static Expression<Func<T, bool>> Not<T>(this Expression<Func<T, bool>> expression)
        {
            var negated = Expression.Not(expression.Body);
            return Expression.Lambda<Func<T, bool>>(negated, expression.Parameters);
        }

        /// <summary>
        /// Combines the first expression with the second using the specified merge function.
        /// </summary>
        static Expression<T> Compose<T>(this Expression<T> first, Expression<T> second, Func<Expression, Expression, Expression> merge)
        {
            // zip parameters (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 the parameters in the first
            var secondBody = ParameterRebinder.ReplaceParameters(map, second.Body);

            // create a merged lambda expression with parameters from the first expression
            return Expression.Lambda<T>(merge(first.Body, secondBody), first.Parameters);
        }

        class ParameterRebinder : ExpressionVisitor
        {
            readonly Dictionary<ParameterExpression, ParameterExpression> map;

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

You’ll also need the ExpressionVisitor if you’re not on .NET 4.

40 Responses to "A universal PredicateBuilder"

Please, a pratical sample.

Something like a method for return a list of “QualquerCoisa” (entity set QuaisquerCoisas) with manually paramaters passing by Dictionary.

Thanks.

I’ve been using Albahari’s PredicateBuilder for some time, and was disappointed when it broke with Entity Framework. Thanks for this universal version. Good stuff.

This is absolutely amazing!! You’ve saved me A LOT of time…and head-pounding!! Thank you very much!

How easy would it be to add NOT as a method?

Hi Marco

public static Expression<Func<T, bool>> Not(this Expression<Func<T, bool>> expression)
{
var negated = Expression.Not(expression.Body);
return Expression.Lambda<Func<T, bool>>(negated, expression.Parameters);
}

Good idea – I’ve added it to the source code!
Pete

good stuff! I’m running into an issue where i assign a predicate value based on a method call, but I receive a not supported exception from EF. Is there a way around this through the helper? If not, I can just capture it in a closure…no biggie!

Works in SL4 for me with Entity Framework stuff.

[...] where I am constructing a dynamic LINQ query using PredicateBuilder (aside: check out this link for the best EF PredicateBuilder implementation). The problem is that this query takes a long time [...]

[...] LINQ query using PredicateBuilder based on user-specified filter criteria (aside: check out this link for the best EF PredicateBuilder implementation). The problem is that this query usually takes a [...]

Thanks alot. Its a silver bullet for our problem.

Does anyone have example code that works in Silverlight 4 to demostrate how to use this code for Oring expressions?

I was finally able to get a working prototype in Silverlight 4 with WCF RIA services. I am planning on creating a small test project to illustrate this functionality. Let me know if you need some code examples… I know that a small working example would have saved me many hours of trial and error.

There are several links in the article – the first is to a detailed page on the PredicateBuilder with examples of how to use it written by its authors Joseph and Ben Albahari. They also wrote an excellent accompanying book on C# which I can really recommend.

Pete.

Pete,

As a follow-up to your last email, I wanted to let you know that your PredicateBuilder works great with LINQPad as a dll. I tweaked a couple of methods for my own applcation, but otherwise the three code blocks are identical as downloaded from referenced sites.

The following is short description of what I did to link your code into LINQPad.

1. Added dll namespace PB by pressing F4 in LINQPad and selecting Additional Namespace Imports tab. The dll was added similarly, but I selected the Additional References and browsed to the source.

2. The added PB.dll contains a tweaked version of public static class PredicateBuilder. It also contains public class ParameterRebinder : ExpressionVisitor and public abstract class ExpressionVisitor.

3. I wrote two lambda expressions for the Items table ItemTitle field, then Ored the two expressions. The Ored expression was used to query my MySQL database.

4. The localQuery.Dump() results contained the correctly identified rows (10) from the table.

Expression<Func> result1 = r1 => r1.ItemTitle.Contains(“oil”);
Expression<Func> result2 = r2 => r2.ItemTitle.Contains(“painting”);
Expression<Func> result;
result = PB.PredicateBuilder.Or(result1, result2);
IQueryable localQuery = from i in Items.Where(result) select i;
localQuery.Dump();

Working with EF 4.1 Update 1.
Great work Pete!

Thanks a lot.
Great work!!!

Awesome! I’ve been using LinqKit for quite a while, but I really needed to be able to trace some generated SQL code, and your rewrite was just the ticket! Thanks!!

Quick question: I have an ObjectQuery that was based on some Entity SQL code. The SQL code sorts the data returned by the query. I can use the PredicateBuilder to create a filter condition to apply to the sorted data, but when I do that the sorting “disappears”. Which makes a subsequent call to Skip() very unhappy :).

Any thoughts on a workaround or fix?

- Mark

[...] MUCH trial and error then the inevitable restart using a different method I found a thing called PredicateBuilder by a gentleman named Pete Montgomery. His source code is based on an older version that apparently [...]

Unfortunately it doesn’t work for Entity Framework 4.1.

Andrey,

I have been using PredicateBuilder since January and I have Microsoft ADO.NET Entity Framework 4.1 installed. It appears to work fine for my application.

My query was wrong. Everything forks fine. Even on EF 4.2

Excellent thanks very much. Was having problems using LinqKits predicate builder with an Include extension method as LinqKits ExpandableQuery when cast to ObjectQuery (as required by the Include extension) returned null. Using your predicate builder instead causes no such problem. Thank you!

Do you still need to ref Linqkit.dll ?

thanks a lot man ur a life saver :D

Thanks bro! You’re da best. ^_^

Hi Pete
Thanks for sharing this – looks great. I am running into an issue using this with a subquery and wonder if you can help?
Using EF5 on Net 4.5, I’m composing a predicate with a bunch of ANDs:

var responsePredicate = Response.IsPublic();

responsePredicate = responsePredicate.And(Response.IsByAuthor(responseAuthorId));

Now I want just posts with at least one response matching the response predicate.

Attempt 1:
return opinions.Where(o => o.Responses.Any(responsePredicate));

This doesn’t compile.

Attempt 2 (as per Albahari):
return opinions.Where(o => o.Responses.Any(responsePredicate.Compile()));

This compiles but EF chokes on it. The Albahari site says that their AsExpandable() extension removes the Comile call at run time, so all is well.

But the USP for your PredicateBuilder is that AsExpandable() is not needed – so how do I get round this problem?

Any help gratefully appreciated!

James

I’m not sure right now but I think you’ll still need to use AsExpandable to get the predicate “deep” into queries like this. See http://www.albahari.com/nutshell/linqkit.aspx

[...] am attempting to use the PredicateBuilder found here http://petemontgomery.wordpress.com/2011/02/10/a-universal-predicatebuilder/ , which the author claims functions identically to the one included in LinqKit. My reason for using [...]

Could someone do some example with this new “PredicateBuilder” please?

Created a new Class composed of an Expression<Func> and duped a bunch of the Predicate Builder stuff, and now I can overload the & and | operators to work with the predicates as normal. ( .Where(easyPredicate1 && easyPredicate2); ) With implicit conversion overloads, can also slim down the original predicate builder and intermix Expressions and EasyPredicates. Not fully tested yet, but seems to be working. Enjoy!!

public class EasyPredicate
{
private Expression<Func> internalExpression;

public EasyPredicate(Expression<Func> expression)
{
internalExpression = expression;
}

public Expression<Func> ToExpressionFunc()
{
return internalExpression;
}

#region Predicate Bools
///
/// Creates a predicate that evaluates to true.
///
public static EasyPredicate True = EasyPredicate.Create(param => true);

///
/// Creates a predicate that evaluates to false.
///
public static EasyPredicate False = EasyPredicate.Create(param => false);
#endregion

///
/// Creates a predicate expression from the specified lambda expression.
///
public static EasyPredicate Create(Expression<Func> expression) { return new EasyPredicate(expression); }

///
/// Combines the first predicate with the second using the logical “and”.
///
public EasyPredicate And(Expression<Func> expression)
{
return Create(Compose(expression, Expression.AndAlso));
}
public EasyPredicate And(EasyPredicate predicate)
{
return And(predicate.internalExpression);
}

///
/// Combines the first predicate with the second using the logical “or”.
///
public EasyPredicate Or(Expression<Func> expression)
{
return Create(Compose(expression, Expression.OrElse));
}
public EasyPredicate Or(EasyPredicate predicate)
{
return Or(predicate.internalExpression);
}

///
/// Negates the predicate.
///
public EasyPredicate Not()
{
var negated = Expression.Not(internalExpression.Body);
return Create(Expression.Lambda<Func>(negated, internalExpression.Parameters));
}

#region Implicit conversion to and from Expression<Func>
public static implicit operator EasyPredicate(Expression<Func> expression)
{
return EasyPredicate.Create(expression);
}

public static implicit operator Expression<Func>(EasyPredicate expression)
{
return expression.internalExpression;
}
#endregion

#region Operator Overloads
public static EasyPredicate operator !(EasyPredicate predicate)
{
return predicate.Not();
}

public static EasyPredicate operator &(EasyPredicate first, EasyPredicate second)
{
return first.And(second);
}

public static EasyPredicate operator |(EasyPredicate first, EasyPredicate second)
{
return first.Or(second);
}

//Both should return false so that Short-Circuiting (Conditional Logical Operator ||)
public static bool operator true(EasyPredicate first) { return false; }
public static bool operator false(EasyPredicate first) { return false; }
#endregion

///
/// Combines the first expression with the second using the specified merge function.
///
Expression Compose(Expression second, Func merge)
{
// zip parameters (map from parameters of second to parameters of first)
var map = internalExpression.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 the parameters in the first
var secondBody = ParameterRebinder.ReplaceParameters(map, second.Body);

// create a merged lambda expression with parameters from the first expression
return Expression.Lambda(merge(internalExpression.Body, secondBody), internalExpression.Parameters);
}

class ParameterRebinder : ExpressionVisitor
{
readonly Dictionary map;

ParameterRebinder(Dictionary map)
{
this.map = map ?? new Dictionary();
}

public static Expression ReplaceParameters(Dictionary 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);
}
}
}

///
/// Enables the efficient, dynamic composition of query predicates.
///
public static class PredicateBuilder
{
///
/// Combines the first predicate with the second using the logical “and”.
///
public static EasyPredicate And(this Expression<Func> first, Expression<Func> second)
{
return first.ToEasyPredicate().And(second);
}

///
/// Combines the first predicate with the second using the logical “or”.
///
public static EasyPredicate Or(this Expression<Func> first, Expression<Func> second)
{
return first.ToEasyPredicate().Or(second);
}

///
/// Negates the predicate.
///
public static EasyPredicate Not(this Expression<Func> expression)
{
return expression.ToEasyPredicate().Not();
}

public static EasyPredicate ToEasyPredicate(this Expression<Func> expression)
{
return EasyPredicate.Create(expression);
}
}

Code didn’t paste well, but hey I tried. The type should be Expression < Func >

    public class EasyPredicate<TExpressionFuncType>
    {
        private Expression<Func<TExpressionFuncType, bool>> internalExpression;

        public EasyPredicate(Expression<Func<TExpressionFuncType, bool>> expression)
        {
            internalExpression = expression;
        }

        public Expression<Func<TExpressionFuncType, bool>> ToExpressionFunc()
        {
            return internalExpression;
        }

        #region Predicate Bools
        /// <summary>
        /// Creates a predicate that evaluates to true.
        /// </summary>
        public static EasyPredicate<TExpressionFuncType> True = EasyPredicate<TExpressionFuncType>.Create(param => true);

        /// <summary>
        /// Creates a predicate that evaluates to false.
        /// </summary>
        public static EasyPredicate<TExpressionFuncType> False = EasyPredicate<TExpressionFuncType>.Create(param => false);
        #endregion

        /// <summary>
        /// Creates a predicate expression from the specified lambda expression.
        /// </summary>
        public static EasyPredicate<TExpressionFuncType> Create(Expression<Func<TExpressionFuncType, bool>> expression) { return new EasyPredicate<TExpressionFuncType>(expression); }

        /// <summary>
        /// Combines the first predicate with the second using the logical "and".
        /// </summary>
        public EasyPredicate<TExpressionFuncType> And(Expression<Func<TExpressionFuncType, bool>> expression)
        {
            return Create(Compose(expression, Expression.AndAlso));
        }
        public EasyPredicate<TExpressionFuncType> And(EasyPredicate<TExpressionFuncType> predicate)
        {
            return And(predicate.internalExpression);
        }

        /// <summary>
        /// Combines the first predicate with the second using the logical "or".
        /// </summary>
        public EasyPredicate<TExpressionFuncType> Or(Expression<Func<TExpressionFuncType, bool>> expression)
        {
            return Create(Compose(expression, Expression.OrElse));
        }
        public EasyPredicate<TExpressionFuncType> Or(EasyPredicate<TExpressionFuncType> predicate)
        {
            return Or(predicate.internalExpression);
        }

        /// <summary>
        /// Negates the predicate.
        /// </summary>
        public EasyPredicate<TExpressionFuncType> Not()
        {
            var negated = Expression.Not(internalExpression.Body);
            return Create(Expression.Lambda<Func<TExpressionFuncType, bool>>(negated, internalExpression.Parameters));
        }

        #region Implicit conversion to and from Expression<Func<TExpressionFuncType, bool>>
        public static implicit operator EasyPredicate<TExpressionFuncType>(Expression<Func<TExpressionFuncType, bool>> expression)
        {
            return EasyPredicate<TExpressionFuncType>.Create(expression);
        }

        public static implicit operator Expression<Func<TExpressionFuncType, bool>>(EasyPredicate<TExpressionFuncType> expression)
        {
            return expression.internalExpression;
        }
        #endregion

        #region Operator Overloads
        public static EasyPredicate<TExpressionFuncType> operator !(EasyPredicate<TExpressionFuncType> predicate)
        {
            return predicate.Not();
        }

        public static EasyPredicate<TExpressionFuncType> operator &(EasyPredicate<TExpressionFuncType> first, EasyPredicate<TExpressionFuncType> second)
        {
            return first.And(second);
        }

        public static EasyPredicate<TExpressionFuncType> operator |(EasyPredicate<TExpressionFuncType> first, EasyPredicate<TExpressionFuncType> second)
        {
            return first.Or(second);
        }

        //Both should return false so that Short-Circuiting (Conditional Logical Operator ||)
        public static bool operator true(EasyPredicate<TExpressionFuncType> first) { return false; }
        public static bool operator false(EasyPredicate<TExpressionFuncType> first) { return false; }
        #endregion

        /// <summary>
        /// Combines the first expression with the second using the specified merge function.
        /// </summary>
        Expression<T> Compose<T>(Expression<T> second, Func<Expression, Expression, Expression> merge)
        {
            // zip parameters (map from parameters of second to parameters of first)
            var map = internalExpression.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 the parameters in the first
            var secondBody = ParameterRebinder.ReplaceParameters(map, second.Body);

            // create a merged lambda expression with parameters from the first expression
            return Expression.Lambda<T>(merge(internalExpression.Body, secondBody), internalExpression.Parameters);
        }

        class ParameterRebinder : ExpressionVisitor
        {
            readonly Dictionary<ParameterExpression, ParameterExpression> map;

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

    /// <summary>
    /// Enables the efficient, dynamic composition of query predicates.
    /// </summary>
    public static class PredicateBuilder
    {
        /// <summary>
        /// Combines the first predicate with the second using the logical "and".
        /// </summary>
        public static EasyPredicate<T> And<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second)
        {
            return first.ToEasyPredicate<T>().And(second);
        }

        /// <summary>
        /// Combines the first predicate with the second using the logical "or".
        /// </summary>
        public static EasyPredicate<T> Or<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second)
        {
            return first.ToEasyPredicate<T>().Or(second);
        }

        /// <summary>
        /// Negates the predicate.
        /// </summary>
        public static EasyPredicate<T> Not<T>(this Expression<Func<T, bool>> expression)
        {
            return expression.ToEasyPredicate<T>().Not();
        }

        public static EasyPredicate<T> ToEasyPredicate<T>(this Expression<Func<T, bool>> expression)
        {
            return EasyPredicate<T>.Create(expression);
        }
    }

I must be missing something, I couldn’t get this test to pass, both results are returned when only 1 is expeceted.


[TestClass]
public class TestPredicate
{
public class Foo
{
public string Name { get; set; }
}

private List foos { get; set; }

[TestInitialize]
public void Setup()
{
foos = new List(){new Foo(){Name = "Frank"},new Foo(){Name = "Bob"}};
}

[TestMethod]
public void Test()
{
var predicate = PredicateBuilder.True();
predicate.And(foo => foo.Name == "Frank");
var sut = foos.AsQueryable().Where(predicate);
Assert.AreEqual(1, sut.Count());
}
}

:( So I missed the assignment.

predicate = predicate.And(foo => foo.Name == "Frank");

Thanks a bunch for this. I spent an awfully long time (totally new to Expression trees etc) trying to figure out how to dynamically build EF-compatible expressions, and this is just perfect for it!

I couldn’t get LinqKit to recognize my Include(” ..”) statements either.
I just implemented this as a replacement and it works as expected. Great code, thanks very much. I wonder if the EF team should be made aware of it and maybe add it to the 6.0 source? It’s extremely useful for anyone using EF 4+.

Is it possible to call a database function inside a predicate this way? Say I want to do some complex evaluation in the DB, which returns say a scalar value, and compare it to a variable. A simplistic version would be select * from customer where func_cust_order_count(customer.custid) > some_variable_int;
How can I call the func_cust_order_count assuming it’s defined in my DB and my model has imported it using EF v5?

ok, the secret is that after your function is defined in your model browser, you need a method with the EdmFunction adornment. like this:
[EdmFunction("NorthWindModel.Store","MinUnitPriceByCategory")]
public static decimal? MinUnitPriceByCategory(int categoryID)
{
throw new NotImplementedException(“You can only call his method as part of a LINQ expression”);
}

Then to use it in your expression just call the method name you gave it.

[…] post is actually a repost of this great article A universal PredicateBuilder that I found very useful so I decided to share it […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: