LINQ to Entities does not support calling

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 request was not completed, and I want to write a request that returns true / false based on some conditions:

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

This works great; however, I would like to get some reuse from my code. I have many methods that need to be filtered if the category is a drink or another, so I tried to create a delegate:

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

I would like to replace the inline check with a delegate:

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

This gives me an error saying LINQ to Entities does not support Invoke. Why can't I replace the inline code for such a delegate, and is there any way to reuse it in another way?

Answer 1

Recall that under the hood, Linq-to-{DATABASE} simply converts the IQueryable that you created in Sql.

You cannot embed such code because Invoke (the method that you actually call when you call Func or Action ) does not have a consistent way of converting it into a sql statement (you could do something there).

This means that you can reuse the parts by separating them:

 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 can be placed in methods that accept an IQueryable<Product> and return the same (but filtered). Then you can reuse your heart content!

Answer 2

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

An obvious but inefficient way is to rewrite the query as follows:

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

A less trivial way would be this:

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

Note that instead of a predicate, this method accepts a predicate expression. Now it is transparent to EF and can be converted to an SQL query without any problems.

Answer 3

As long as you use IQueryable, you can store reusable queries in functions.

  public IQueryable<Product> EligibleForDiscount(IQueryable<Product> products) { return products.Where(p => product.Category == "Beverage" || product.Category == "Other"); } 

Now call it like any other function:

  IQueryable<Product> query = (from p in db.Products where p.Price > 10M select p); query = EligibleForDiscount(query);