How can I use a Predicate<T> in an EF Where() clause?

Question

I'm trying to use predicates in my EF filtering code.

This works:

IQueryable<Customer> filtered = customers.Where(x => x.HasMoney && x.WantsProduct);

But this:

Predicate<T> hasMoney = x => x.HasMoney;
Predicate<T> wantsProduct = x => x.WantsProduct;
IQueryable<Customer> filtered = customers.Where(x => hasMoney(x) && wantsProduct(x));

fails at runtime:

The LINQ expression node type 'Invoke' is not supported in LINQ to Entities.

I can't use the first option, as this is a simple example, and in reality, I'm trying to combine a bunch of predicates together (with and, not, or, etc.) to achieve what I want.

How can I get the EF Linq provider to "understand" my predicate(s)?

I get the same result if I use a Func<T, bool>. It works with an Expression<Func<T>>, but I can't combine expressions together for complex filtering. I'd prefer to avoid external libraries if possible.

UPDATE:
If this cannot be done, what options do I have? Perhaps expressions be combined / or'ed / and'ed somehow to achieve the same effect?

Solomonsolon

You would probably have to generate the expression tree dynamically – Camail

Maybe this will help you:#12967344 – Roice

Answer 1

Expression<Func<Customer, bool>> hasMoney = x => x.HasMoney;
Expression<Func<Customer, bool>> wantsProduct = x => x.WantsProduct;
IQueryable<Customer> filtered = customers.Where(hasMoney).Where(wantsProduct);

• Use Expression<T> to leave x => x.HasMoney as an expression tree, and not compile it to a .NET method
• Use Expression<Func<Customer, bool>> rather than Expression<Predicate<Customer>>, because that's what Queryable.Where expects
• Pass it directly in .Where, combining them using multiple .Where calls instead of &&.

It's possible to get more complex conditions (including not, or, etc.) working by rewriting them using .Union, .Except, etc.

An alternative is to use LINQKit's AsExpandable:

Expression<Func<Customer, bool>> hasMoney = x => x.HasMoney;
Expression<Func<Customer, bool>> wantsProduct = x => x.WantsProduct;
IQueryable<Customer> filtered = customers.AsExpandable().Where(x => hasMoney.Invoke(x) && wantsProduct.Invoke(x));

Astrogeology

How does LinqKit (a number of years old and isn't a natural fit for EF) compare to this – Solomonsolon

@BobbyB The resulting expression (what EF sees) would be exactly the same, so EF would also handle it exactly the same. They both have their advantages and disadvantages. You may prefer not having to type .AsExpandable(), whereas I prefer being able to use the regular && operator to check whether two conditions are both true. – Astrogeology

That "universal predicate builder" link doesnt seem to allow "&&" but linqkit also doesn't? I thought the idea behind that sort of thing was using And(), Or() as extension methods. – Solomonsolon

@BobbyB LINQKit does, that's the form I used in my answer. – Astrogeology

Ah I see now... Though keep in mind EF won't allow the Invoke(). – Solomonsolon

@BobbyB That's what the .AsExpandable() is for, it will make sure the .Invoke() gets removed before EF sees it. – Astrogeology

Answer 2

Unfortunately there's no way to use Predicate<T> in EF linq since it's impossible to map it on SQL query. This can be done with Expressions only because they can be parsed and converted to SQL.

In fact there are 4 language features that made linq possible:

1. Extension methods
2. Type inference
3. Closures
   and for linq2sql especially
4. Expressions

UPDATE:
The possible solution is building expressions programmatically. How to: Use Expression Trees to Build Dynamic Queries

Brann

Are there any good options then, rather than repeating lots of filtering code? – Solomonsolon