CODEWITHSUNDEEP
c#linq-to-entities
Dismissile
Dismissile

Reputation: 33071

LINQ to Entities Does not Support Invoke

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?

Upvotes: 7

Views: 4329

Answers (3)

user1023602
user1023602

Reputation:

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

Upvotes: 0

Chris Pfohl
Chris Pfohl

Reputation: 19064

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!

Upvotes: 6

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726569

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.

Upvotes: 2

Related Questions