LINQ to Entities Does not Support Invoke

Question

I have the following query in LINQ to Entities:

var query = from p in db.Products
            where p.Price > 10M
            select p;

At this point the query has not executed, and I want to write a query that will return true/false based on some conditions:

return query.Any(p => p.IsInStock &&
               (p.Category == "Beverage" ||
               p.Category == "Other"));

This works fine; however, I would like to get some reuse out of my code. I have many methods that need to filter based on if the category is a Beverage or Other, so I tried creating a delegate:

Func<Product, bool> eligibleForDiscount = (product) => product.Category == "Beverage" || product.Category == "Other";

I wanted to substitute the inline check with the delegate:

return query.Any(p => p.IsInStock && eligibleForDiscount(p));

This gives me an error saying that LINQ to Entities doesn't support Invoke. Why can't I substitute the inline code for a delegate like this, and is there any way I can accomplish my reuse some other way?

Answer 1

Recall that under the hood Linq-to-{DATABASE} is just transforming the IQueryable you've produced into Sql.

You can't inline code like that because an Invoke (the method you're actually calling when you call a Func or Action) has no consistent way to transform it into a sql statement (you could be doing anything in there).

That said you can reuse parts by splitting it up:

var query = from p in db.Products
            where p.Price > 10M
            select p;

query = query.Where(p => p.IsInStock);
query = query.Where(p => p.Category == "Beverage" || p.Category == "Other");
return query.Any();

Both those can be put into methods that take an IQueryable<Product> and return the same (but filtered). Then you can reuse to your heart's content!

Answer 2

The problem is that IQueryable needs to generate a SQL expression to pass to RDBMS, and it cannot do it when all it has is an opaque predicate.

The obvious but inefficient way is to rewrite your query as follows:

return query.Where(p => p.IsInStock).AsEnumerable().Any(eligibleForDiscount);

A less trivial way would be as follows:

bool GotEligible(Expression<Func<Product,bool>> pred) {
    return query.Where(p => p.IsInStock).Any(pred);
}

Note how instead of a predicate this method takes a predicate expression. Now it is transparent to the EF, and can be converted to a SQL query without a problem.