Using Func<> in LINQ Query

Question

I have a Func<ProductItemVendor, bool> stored in CompareProductItemVendorIds. I would like to use that expression in a LINQ query.

It appears the following is legal:

var results =
    Repository.Query<ProductItemVendor>().Where(CompareProductItemVendorIds);

However, the following is not legal:

var results = from v in Repository.Query<ProductItemVendor>()
              where CompareProductItemVendorIds(v)
              select v;

This code produces an error:

The LINQ expression node type 'Invoke' is not supported in LINQ to Entities.

Questions:

1. Why are these statements so different that my Func<> is legal in one but not the other? I thought they both basically did the same thing.

2. How can I make this work? Do I have to explicity create my Func<> as an Expression<Func<>> instead?

Please see my related question at Using Expression<Func<>> in a LINQ Query.

Answer 1

There is big difference between Expression<Func<T,bool>> and Func<T,bool>. First one is an expression tree. You can think of it as description of code. Linq to Entities requires expression trees. Because it needs to build SQL query. So it needs description of code to translate same actions into SQL.

Second one, Func<T,bool> is a simple method with specified signature. Nothing special here. Why its legal here:

Repository.Query<ProductItemVendor>().Where(CompareProductItemVendorIds);

It's simple. There are two Where extension methods. One fore IQueryable<T>, which expects expression tree (which will be translated into SQL query). And another is extension for IEnumerable<T> which expects ordinal method for in-memory collection filtering (usual C# code). Thus you don't have expression tree, latter one is chosen. No SQL generated here. This is your case.

Now second query:

from v in Repository.Query<ProductItemVendor>()
where CompareProductItemVendorIds(v)
select v

Actually it's not same query. It's equivalent to

Repository.Query<ProductItemVendor>().Where(v => CompareProductItemVendorIds(v));

And here you have lambda expression, which can be converted into expression tree. And another Where extension is used - one for IQueryable<T>. So, Linq to Entities tries to convert this expression tree to SQL. But what it should convert? Yes, invocation of some in-memory method. And, of course, Linq to Entities fails to do that.

In order to make your query work, you should use Expression<Func<T,bool>>. You can build it manually, or you can use lambda expression.

Answer 2

The reason your first version works is Linq is being too smart for it's own good. The equivalent of your first version is actually

IEnumerable<ProductItemVendor> temp = Repository.Query<ProductItemVendor>().AsEnumerable();
var results = temp.Where(CompareProductItemVendorIds);

So when you perform your query you are returning every row in your database then performing a Where in memory on the local computer.

To get the Where clause to be performed on the database you must change the type of CompareProductItemVendorIds to be Expression<Func<ProductItemVendor, bool>>.

There is no way to "convert" from Func<TIn, TOut> to Expression<Func<TIn. Tout>>, you must rewrite your code to initially be a expression, you could then convert to Func<TIn, TOut> by calling CompareProductItemVendorIds.Compile()

Expression<Func<ProductItemVendor, bool>> CompareProductItemVendorIds = //...
Func<ProductItemVendor, bool> CompareProductItemVendorIdsAsFunc = CompareProductItemVendorIds.Compile();