C# – Method GetPrice() cannot be translated into a store expression

centity-frameworklinqlinq-to-entitiesnet

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?

Best Answer

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.

Related Solutions

C# – Nested LINQ returning a this method cannot be translated into a store expression exception

Don't call ToList on the inner query (the one for JobDetails). The error is "This .ToList method you speak of -- it can't be translated to T-SQL!"

This should work:

retval = ( from jm in entities.JobMasters
             where jm.UserId == userId && jm.IsRemote == false
             select new JobDto
             {
                 JobMasterId = jm.JobMasterId,
                 ExternalTaskId = jm.ExternalTaskId,
                 JobDetails =   from jd in entities.JobDetails
                                where jd.JobMasterId == jm.JobMasterId
                                select new JobDetailDto { ScreenFieldId = jd.ScreenFieldId, FieldValue = jd.FieldValue }
                 )
             }
        ).ToList();

Note that you can call ToList on the end of the query, as that part doesn't need to be translated to T-SQL.

.net – LINQ to Entities does not recognize the method

As you've figured out, Entity Framework can't actually run your C# code as part of its query. It has to be able to convert the query to an actual SQL statement. In order for that to work, you will have to restructure your query expression into an expression that Entity Framework can handle.

public System.Linq.Expressions.Expression<Func<Charity, bool>> IsSatisfied()
{
    string name = this.charityName;
    string referenceNumber = this.referenceNumber;
    return p => 
        (string.IsNullOrEmpty(name) || 
            p.registeredName.ToLower().Contains(name.ToLower()) ||
            p.alias.ToLower().Contains(name.ToLower()) ||
            p.charityId.ToLower().Contains(name.ToLower())) &&
        (string.IsNullOrEmpty(referenceNumber) ||
            p.charityReference.ToLower().Contains(referenceNumber.ToLower()));
}

Best Answer

Related Solutions

C# – Nested LINQ returning a this method cannot be translated into a store expression exception

.net – LINQ to Entities does not recognize the method

Related Topic