Why doesn't a separately instantiated Func<T,bool> predicate not translate into SQL with Entity Framework?

I have an EF Code First Db context that I'm using to query the database. I noticed some performance issues when passing in queries as Func<Product, bool>s from my Aggregate Repository and on investigating further it turned out that the queries were not being translated into SQL Queries.

After a little more digging I discovered the following.

var results = _context.Products
            .Where(p => p.ProductCode.Contains("AAA"))
            .Where(p => p.CategoryId == 1)
            .ToList();

This works exactly as expected. It generates some parametrized SQL with a Where Clause.

==================================================================

var results2 = _context.Products
            .Where(p => p.ProductCode.Contains("AAA") && p.CategoryId == 1)
            .ToList();

This also works as expected. It generates the same sql as above

==================================================================

Func<Product, bool> pred = (p => p.ProductCode.Contains("AAA") && p.CategoryId == 1);

var results3 = _context.Products.Where(pred).ToList();

This is broken. It doesn't generate the where clause in the SQL, it returns everything and then filters it in code.

Because in order to translate into SQL, it has to be an Expression<...>, not a Func<...>.

This is done automatically for you by the compiler, and since the overloads on the Linq-to-SQL classes takes expressions, not delegates, the compiler will automagically translate your code (which looks like a lambda or an anonymous method) into an expression object and pass that.

However, if you take care of building the function yourself, the compiler cannot do this, and Linq-to-SQL does not take anonymous methods, it only takes expressions.

What you can do is to execute the parts of your query that you can, and then filter the results through your function, but I would look into just changing the type of your value into an expression instead.

No sooner than I posted this ReSharper helped answer my question by showing me the overload method signature for the Where() extension method.

It takes both Func<T, bool> and Expression<Func<T, bool>>. If your declaring your predicates externally, you must use the Expression variation as the former is not translated into sql.

Here's why the query reads the whole table.

When a Func is used instead of an Expression, the compiler chooses methods on System.Linq.Enumerable - instead of System.Linq.Queryable. The Enumerable methods iterate the source collection (sometimes lazily) while the Queryable methods build up the expression tree.

Since the call to Where isn't part of the expression tree, the sql generator doesn't see it during the query translation.