Why does EntityFramework's LINQ parser handle an externally defined predicate differently?

Question

I'm using Microsoft's Entity Framework as an ORM and am wondering how to solve the following problem. I want to get a number of Product objects from the Products collection where the Product.StartDate is greater than today. (This is a simplified version of the whole problem.)

I currently use:

var query = dbContext.Products.Where(p => p.StartDate > DateTime.Now);

When this is executed, after using ToList() for example on query, it works and the SQL created is effectively:

SELECT * FROM Product WHERE StartDate > (GetDate());

However, I want to move the predicate to a function for better maintainability, so I tried this:

private Func<Product, bool> GetFilter()
{
  Func<Product, bool> filter = p => p.StartDate > DateTime.Now;
  return filter;
}
var query = dbContext.Products.Where(GetFilter());

This also works from a code point of view insofar as it returns the same Product set but this time the SQL created is analogous to:

SELECT * FROM Product;

The filter is moved from the SQL Server to the client making it much less efficient.

So my questions are:

• Why is this happening, why does the LINQ parser treat these two formats so differently?
• What can I do to take advantage of having the filter separate but having it executed on the server?

Answer 1

You need to use an Expression<Func<Product, bool>> in order for it to work like you intend. A plain Func<Product, bool> tells LINQ that you want it to run the Where in MSIL in your program, not in SQL. That's why the SQL is pulling in the whole table, then your .NET code is running the predicate on the entire table.

Answer 2

You are returning a Func, but to inject the predicate into the SQL, LINQ requires an expression tree. It should work if you change the return type of your method (and of your local variable, of course) to Expression<Func<Product, bool>>.

Answer 3

Since in second case filter func maybe arbitrary LINQ to EF can't parse you filter to SQL and has to resolve it on client side.