LINQ to Entities Does not Support Invoke

Question

I have the following query in LINQ to Entities:

var query = from p in db.Products
            where p.Price > 10M
            select p;

At this point the query has not executed, and I want to write a query that will return true/false based on some conditions:

return query.Any(p => p.IsInStock &&
               (p.Category == "Beverage" ||
               p.Category == "Other"));

This works fine; however, I would like to get some reuse out of my code. I have many methods that need to filter based on if the category is a Beverage or Other, so I tried creating a delegate:

Func<Product, bool> eligibleForDiscount = (product) => product.Category == "Beverage" || product.Category == "Other";

I wanted to substitute the inline check with the delegate:

return query.Any(p => p.IsInStock && eligibleForDiscount(p));

This gives me an error saying that LINQ to Entities doesn't support Invoke. Why can't I substitute the inline code for a delegate like this, and is there any way I can accomplish my reuse some other way?

possible duplicate of [The LINQ expression node type 'Invoke' is not supported in LINQ to Entities in entity framework](http://stackoverflow.com/questions/8741667/the-linq-expression-node-type-invoke-is-not-supported-in-linq-to-entities-in-e) — Tim Rogers, May 16 '12 at 15:42
Considering that question does not have an accepted answer and I have no idea what this PredicateBuilder is, I don't consider this question a duplicate. — Dismissile, May 16 '12 at 15:50

Chris Pfohl · Accepted Answer · 2015-10-19T17:04:29.717

6

Recall that under the hood Linq-to-{DATABASE} is just transforming the IQueryable you've produced into Sql.

You can't inline code like that because an Invoke (the method you're actually calling when you call a Func or Action) has no consistent way to transform it into a sql statement (you could be doing anything in there).

That said you can reuse parts by splitting it up:

var query = from p in db.Products
            where p.Price > 10M
            select p;

query = query.Where(p => p.IsInStock);
query = query.Where(p => p.Category == "Beverage" || p.Category == "Other");
return query.Any();

Both those can be put into methods that take an IQueryable<Product> and return the same (but filtered). Then you can reuse to your heart's content!

edited Oct 19 '15 at 17:04

answered May 16 '12 at 15:48

Chris Pfohl

18,220
9
68
111

So your suggestion would be to have a method that takes IQueryable and return IQueryable instead of a delegate. I guess they both do the same thing :) – Dismissile May 16 '12 at 15:51
They do exactly the same thing, and it's much more reusable...Methods and functions that Take IQueryable and return IQueryable are really powerful that way. – Chris Pfohl May 16 '12 at 15:59
1

As an added bonus, this can also be made into an extension method too which provides for custom slick looking query.WhereIsInAnyCategory("Beverage", "Other") (or query.WhereBeverageOrOther() ) type syntax on an IQueryable interface. – MerickOWA May 16 '12 at 16:06

Sergey Kalinichenko · Answer 2 · 2012-05-16T15:59:52.030

2

The problem is that IQueryable needs to generate a SQL expression to pass to RDBMS, and it cannot do it when all it has is an opaque predicate.

The obvious but inefficient way is to rewrite your query as follows:

return query.Where(p => p.IsInStock).AsEnumerable().Any(eligibleForDiscount);

A less trivial way would be as follows:

bool GotEligible(Expression<Func<Product,bool>> pred) {
    return query.Where(p => p.IsInStock).Any(pred);
}

Note how instead of a predicate this method takes a predicate expression. Now it is transparent to the EF, and can be converted to a SQL query without a problem.

edited May 16 '12 at 15:59

answered May 16 '12 at 15:52

Sergey Kalinichenko

714,442
84
1,110
1,523

btw, you want to get rid of the `(p)` in the `Any` clause – Chris Pfohl May 16 '12 at 15:59
And *probably* you meant `GotEligible` ;) – Chris Pfohl May 16 '12 at 16:01

score 0 · Answer 3 · 2016-01-14T10:57:03.787

As long as you stick with IQueryable, you can keep reusable querys in functions.

  public IQueryable<Product> EligibleForDiscount(IQueryable<Product> products)
  {
       return products.Where(p => product.Category == "Beverage" || 
                                  product.Category == "Other");
  }

Now call it like any other function:

  IQueryable<Product> query = (from p in db.Products
                               where p.Price > 10M
                               select p);

  query = EligibleForDiscount(query);

LINQ to Entities Does not Support Invoke

3 Answers3