Method GetPrice() cannot be translated into a store 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 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?

Solution 1

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.

Solution 2

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