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 evaluating of an expression
decimal? total =
(from cartItems in storeDB.Carts
where cartItems.CartId == shoppingCartId
select (int?)cartItems.Count * cartItems.Product.GetPrice(store.RateId))
.Sum();
throw an exception:
LINQ to Entities does not recognize the method 'System.Decimal GetPrice(System.Guid)' method, and this method cannot be translated into a store expression.
I am using this very same code in other places and works just fine :
// Get the price for given rate
decimal price = product.GetPrice(rate.RateId);
Any idea how to solve it?
Try that:
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 equivalent in SQL, so you need to execute it on the result, not directly in the query. AsEnumerable
forces Linq to consider the query as an IEnumerable
(rather that IQueryable
) from this point, so what comes next is executed in memory, not in the DB.
The reason the exception is occurs is that the Entity Framework provider tries to create SQL statements for the extension method. When you're using the method by itself, it's just creating SQL for the contents of the extension method, which works fine.
The best way I encountered to fix this, other than calling GetPrice
in a loop on the results of the 'outer' query causing a N+1 query, is using LinqKit
To use it you define an expression tree, instead of the extension method like so:
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 can't be used as an extension method) as the GetPrice method you had.
To combine this expression tree with another one, 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();
The .Invoke()
call adds an invoke to the expression tree. The Expand()
call inlines this method, so you get one big expression tree that can be converted to SQL.
This approach will write a query that looks like the one below, but with a re-usable 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();
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With