Using Expression<Func<>> in a LINQ Query

Question

I want to define a Func<ProductItemVendor, bool> filter expression named CompareProductItemVendorIds, which can be used throughout my application, mostly within Entity Framework/LINQ queries.

I've learned that in order to be able to use this filter in a LINQ query, I must declare it as Expression<Func<>> instead of just Func<>. I understand the reason for this, and it's easy for me to do this.

But I'm having the following problems using that expression in my queries.

First, code such as:

ProductItem.ProductItemVendors.FirstOrDefault(CompareProductItemVendorIds)

Note: ProductItem is a database entity, and its ProductItemVendors property is a navigation collection.

Produces the error:

`Instance argument: cannot convert from 'System.Collections.Generic.ICollection' to 'System.Linq.IQueryable'

And second, code such as:

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

Produces the error:

'CompareProductItemVendorIds' is a 'variable' but is used like a 'method'

So I have my nice shiny new Expression<Func<>>. How can I use it in my LINQ queries?

Genius

The expression is a description of a function, not a function itself. Why can't you use Func<> directly? – Eraser

@Cameron: Because then I get the error The LINQ expression node type 'Invoke' is not supported in LINQ to Entities. Please see https://mcmap.net/q/1469586/-using-func-lt-gt-in-linq-query. – Genius

I tried te following and it worket : Func<ProductItemVendor, bool> CompareProductItemVendorIds = new Func<ProductItemVendor, bool>(r => r.ID > 10); var x = ProductItemVendors.FirstOrDefault(CompareProductItemVendorIds); – Rockies

@Rockies That's a linq to objects query, not using a query provider. This is an EF question. – Romaromagna

@Seb: My understanding in this case is that it returns all items in the query and then looks for a match in memory. That's a really inefficient way to go. I'm trying to avoid that. – Genius

Sorry my mistake, just saw the EF tag ... – Rockies

Jon, your examples are completely lacking in information, particularly the first one. You have provided no information about the types of anything involved when you have a type mismatch error. As to your second point, your earlier question's answer covers that in detail. – Romaromagna

@Servy: Sorry, ProductItem is a database entity, and it's ProductItemVendors property is a navigation collection. – Genius

@JonathanWood Again, still not enough information to replicate the problem. – Romaromagna

The reason why you are getting the first error is becuase i think as soon as you calling the FirstOrDefault the object or result is no longer expression tree it object but you passing CompareProductItemVendorIds which is expression for expression tree try changing it to this ProductItem.ProductItemVendors.Where(CompareProductItemVendorIds).FirstOrDefault(); fascinatedwithsoftware.com/blog/post/2011/12/02/… explains difference between expression tress and Linq expressions. – Smote

@Servy: Sorry, I'm not sure what to do. I can't provide the entire database schema or the thousands of lines of code. I thought the principles involved would be standard across EF. Can you clarify the type of information I should include? Also, I'm not sure what you meant about my second "point". I don't see how any of this post duplicated my previous question. I would love your help. – Genius

CompareProductItemVendorIds is now an Expression? Then you should share the code that build it. – Scalawag

@JonathanWood You can look through the help center for advice on how to create a short, simple, reproducible example program when asking an SO question. By the second half of your question I mean everything after the first error message, which is the exact same example from your earlier question, and to which the answer to that question (still) exactly answers that case. – Romaromagna

@Romaromagna Not quite, if he made it an Expression<T>. – Scalawag

Show your full "nice shiny new Expression<Func<>>". – Curve

@Servy: My first question was about how Func<> worked with Where() but not in a query. The answer was that the most performant way to get it to work was to make it an Expression<Func<>>. So now I have an Expression<Func<>> but I can't use it in my query. (I think it will work fine in Where(), but that's not the type of query I'm using.) I've reviewed the helpful answers in the other question, but do not see where they address this. – Genius

@haim770: Can you think of any manner in which the internal contents of the expression would affect the nature of this question? If so, I will be happy to post it. – Genius

I didn't mean the expression body, but the signature. I assume it's Expression<Func<ProductItemVendor, bool>>? – Curve

@haim770: Yes, exactly. – Genius

ProductItem.ProductItemVendors.FirstOrDefault(CompareProductItemVendorIds) is just an expression, could you give the entire statement? (context matter ALOT when it comes to EF) – Carriecarrier

@flindeberg: Entire statement is ProductItemVendor productItemVendor = ProductItem.ProductItemVendors.FirstOrDefault(CompareProductItemVendorIds);, but I don't see how that can be helpful. – Genius

@JonathanWood, trust me, it does. – Carriecarrier

@JonathanWood Go back and read your previous question and the answers again. There is quite a lot more information there than just "use an Expression". There's half a page of text there. – Romaromagna

@Servy: I did that. I'm not seeing an answer to the information I'm trying to get now. – Genius

You haven't provided enough information for the first half of your question to be answerable. The second half is answered by using the actual solution that the earlier answer showed as the way you should solve the problem, rather than as the example he showed as how not to solve the problem. For some reason you choose to use the example of what not to do as what to do. – Romaromagna

@Servy: I don't know why this has to be so cryptic. The suggestion was that I need to use Expression<Func<>> instead of just Func<>. So now I have an Expression<Func<>> but couldn't see a way to incorporate it into the type of queries I'm using. I've reviewed the previous question and don't see an answer to that. Just saying I missed it isn't going to be helpful any more. I appreciate your previous help. I found other workarounds for this. I guess we'll just let it go. – Genius

@JonathanWood - What workaround did you find? It would be helpful if you posted that as an answer. – Mertz

@Aducci: I just restructured the code so it wasn't so much an issue. And I probably got some poorer performance as a result. – Genius

Answer 1

ProductItem is already an Entity, so you can't use your Expression, you need to use Compile() to get the Func<> from your Expression<Func<>> since ProductItemVendors is no longer an IQueryable

ProductItem.ProductItemVendors.FirstOrDefault(CompareProductItemVendorIds.Compile())

You would have to use your Expression on the ProductItemVendorsContext like this:

var item = Context.ProductItemVendors.FirstOrDefault(CompareProductItemVendorIds);

---

You cant use an Expression inside query syntax, you need to use method sytanx

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

Mertz

Returning Func<T> would cause the query to be executed using Linq-to-Objects and not Linq-to-Entities which is undesired in this case. – Curve

I think this is a very bad approach. I'm using Entity Framework, and the expression needs to be compiled into SQL. This suggestion compiles to machine code, loads all items from the database, and then filters that data in memory. – Genius

@JonathanWood - You have already loaded the Entity in memory. You can't have an Entity and a subset of its navigation property at the same time. That is not how the Entityframework works. – Mertz

@Aducci: You can load ProductItem into memory without loading every related ProductItemVendor. Loading all related entities whenever one is loaded isn't what EF does. It would use lazy loading here. – Genius

@JonathanWood - It is all or nothing, you can't partially load it. You would need to query the ProducItem repository directly – Mertz

@Aducci: Are you saying that any time I load a ProductItem, I am automatically loading every related ProductItemVendor? What if there are thousands and thousands of them? – Genius

@JonathanWood - Depending on how you have it setup. I use Include on the navigation properties I want loaded. It seems like you are using lazy loading, so that would mean you are loading the ProductItemVendors navigation property the first time you use it. – Mertz

I just went through the discussions in this question: https://mcmap.net/q/1469586/-using-func-lt-gt-in-linq-query, about how I need an Expression<Func<>>, so it seems very strange that, now that I have an Expression<Func<>>, I need to compile it into a Func<>, which is what I started with. – Genius

This is the important hint: You cant use an Expression inside query syntax, you need to use method sytanx I would consider removing everything above that line in the post, it just confuses readers. – Partite

Answer 2

The first case;

ProductItemVendor productItemVendor = ProductItem.ProductItemVendors.FirstOrDefault(CompareProductItemVendorIds);

Produces the error:

`Instance argument: cannot convert from 'System.Collections.Generic.ICollection' to 'System.Linq.IQueryable'

Happens because it is an ICollection, the entity has already been loaded. Given dbContext or objectContext lazy loading vs explicit loading is implemented a bit differently, I assume you are using explicit loading though. If you change the loading to lazy the type of ProductItemVendors will be IQueryable and what you are trying will succeed.

Given the second case, the expression must be compilable to SQL, else you get a lot of weird errors, probably it's possible that that is the case here.

It's hard to give you more explicit help given the information in the question, I cannot recreate it easily. If you can create a MWE-solution and upload it somewhere I can have a look, but I'm afraid I can't help more here.

Carriecarrier

Answer 3

I don't think composing expressions like that is supported by default.
You could try LINQKit

Here's an example from the LINQKit page; one Expression<Func<>> inside another:

Call Invoke to call the inner expression Call Expand on the final result. For example:

Expression<Func<Purchase,bool>> criteria1 = p => p.Price > 1000;
Expression<Func<Purchase,bool>> criteria2 = p => criteria1.Invoke (p)
                                                 || p.Description.Contains ("a");

Console.WriteLine (criteria2.Expand().ToString()); 

(Invoke and Expand are extension methods in LINQKit.) Here's the output:

p => ((p.Price > 1000) || p.Description.Contains("a"))

Notice that we have a nice clean expression: the call to Invoke has been stripped away.

Imperfective