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

Question

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.

Answer 1

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.