Method GetPrice() does not work as a store expression.

.net c# entity-framework linq linq-to-entities

Question

I have a class procedure.

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

evaluating an expression, and

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

Make a distinction:

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 use the exact same code elsewhere, and it works perfectly:

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

Have you have a solution for it?

1
6
6/19/2013 7:31:56 PM

Accepted Answer

Try it:

    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, thus you must run it on the output rather than the original query.AsEnumerable makes Linq treat the query as anIEnumerable (Instead ofIQueryable From this point on, everything is run in memory rather than the database.

11
7/30/2011 9:00:48 PM

Popular Answer

The Entity Framework provider attempts to generate SQL queries for the extension function, which is why the problem occurs. When used independently, the method just generates SQL for the extension method's contents, which is perfectly OK.

The only other option I saw to remedy this was to contactGetPrice using LinqKit in a loop on the outcomes of the "outer" query, resulting in a N+1 query

Instead of using the extension technique, you create an expression tree to utilize it 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();

Keep in mind that this generates an expression with the same signature as the GetPrice method you had, with the exception that it cannot be used as an extension method.

You need LinqKit to merge this expression tree with another one:

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() Invoke is added to the expression tree via call. TheExpand() Call this method inlined to create a single, large expression tree that can be translated into SQL.

The following query will be created using this method, however it will have a reusablepriceSelector :

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


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow