GetPrice () method cannot be translated into a storage expression

Question

I have a class method:

public static class ProductExtensions { public static decimal GetPrice(this Product product, Guid rateId) { return product.Prices .Where(p => p.Rate.RateId == rateId) .Select(b => b.UnitPrice) .DefaultIfEmpty(product.UnitPrice) .First(); } } 

and expression scores

  decimal? total = (from cartItems in storeDB.Carts where cartItems.CartId == shoppingCartId select (int?)cartItems.Count * cartItems.Product.GetPrice(store.RateId)) .Sum(); 

throws an exception:

LINQ to Entities does not recognize the 'System.Decimal GetPrice (System.Guid)' method, and this method cannot be translated into a storage expression.

I use this same code elsewhere and works great:

  // Get the price for given rate decimal price = product.GetPrice(rate.RateId); 

Any idea how to solve it?

Answer 1

Try the following:

  decimal? total = (from cartItems in storeDB.Carts where cartItems.CartId == shoppingCartId select new { cartItems.Count, cartItems.Product}) .AsEnumerable() .Sum(x => (int?)x.Count * cart.Product.GetPrice(store.RateId)); 

GetPrice has no SQL equivalent, so you need to execute it as a result, not directly in the query. AsEnumerable forces Linq to consider the query as IEnumerable (rather, IQueryable ) from this point, so the following will be executed in memory, not in the database.

Answer 2

The reason the exception is thrown is because the Entity Framework provider is trying to create SQL queries for the extension method. When you use this method on its own, it simply creates SQL for the contents of the extension method, which works great.

The best way I came across is to fix this, besides calling GetPrice in the result loop of the “external” query calling the N + 1 query, uses LinqKit

To use it, you define an expression tree instead of an extension method as follows:

 static Expression<Func<Product, Guid, decimal>> priceSelector = (product, rateId) => product.Prices .Where(p => p.Rate.RateId == rateId) .Select(b => b.UnitPrice) .DefaultIfEmpty(product.UnitPrice) .First(); 

Note that this creates an expression with the same signature (except that it cannot be used as an extension method) as the GetPrice method you had.

To combine this expression tree with another, you need LinqKit :

 decimal? total = (from cartItems in storeDB.Carts where cartItems.CartId == shoppingCartId select (int?)cartItems.Count * priceSelector.Invoke(cartItems.Product, store.RateId)) .Expand() .Sum(); 

Calling .Invoke() adds a call to the expression tree. Calling Expand() enables this method, so you get one large expression tree that can be converted to SQL.

This approach will write a query similar to the following, but with a reusable priceSelector :

 decimal ? total = (from cartItems in storeDB.Carts where cartItems.CartId == shoppingCartId select (int?)cartItems.Count * product.Prices .Where(p => p.Rate.RateId == rateId) .Select(b => b.UnitPrice) .DefaultIfEmpty(product.UnitPrice) .First()).Sum();