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.

Scuttlebutt

A noob question: is CompareProductItemVendorIds in the 1st example the same as CompareProductItemVendorIds(v) in the second? – Auto

@Kapol: Yes, that is where my Func<> is stored, which I described in the first paragraph. – Scuttlebutt

Take a look at LINQKit to help you compose queries from Expressions. – Career

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.

Geulincx

Thanks. Yes, I do know the difference between a Func<> and Expression<Func<>>. Are you saying that, in the first case, the list is materialized first, and then Where() is called on the materialized list? – Scuttlebutt

@JonathanWood yes, exactly. You get all data into memory, and then in-memory collection is filtered. You can check it with EF or SQL profiler – Geulincx

@JonathanWood yes, when you call Enumberable.Where on a IQueryable object it will materialize the results before performing the where. To get the list to not be materialized first you must call Queryable.Where which you can see only takes in a expression. – Avion

One last question: I now have an Expression<Func<>>, but I can't seem to find any way to incorporatee it into my LINQ query. Note: I am not using the .Where() syntax. My query uses the from v in x where ... format. – Scuttlebutt

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();

Avion