Unable to translate LINQ Query

I am having an issue with the following LINQ query:

var baselineDate = DateTime.Now.AddDays(-365);
        var salesPerformance = _context.SalesOrder.Where(p => p.OrderDate >= baselineDate).Where(p => p.CustomerId == customerId)
                               .GroupBy(p => p.OrderDate.Month)
                               .Select(g => new CustomerSalesPerformance
                               {
                                   Month = g.Key,
                                   Sales = g.Sum(i => i.SalesOrderItems.Sum(i => i.Qty * i.UnitPrice))
                               });

        return await salesPerformance.ToListAsync();

I am aiming to produce a report with sales per customer for the last year:

Jan: £13,500
Feb: £0.00
Mar: £56,000
etc

I am unable to see anything in this query which would not translate (such as a DateTime function).

The error message:

{“The LINQ expression ‘GroupByShaperExpression:rnKeySelector: DATEPART(month, s.OrderDate), rnElementSelector:EntityShaperExpression: rn EntityType: SalesOrderrn ValueBufferExpression: rn ProjectionBindingExpression: EmptyProjectionMemberrn IsNullable: Falsernrn .Sum(i => i.SalesOrderItemsrn .Sum(i => (Nullable)i.Qty * i.UnitPrice))’ could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to ‘AsEnumerable’, ‘AsAsyncEnumerable’, ‘ToList’, or ‘ToListAsync’. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.”}

My models:

SalesOrder
SalesOrderId (PK, int)
CustomerId (FK, int)
OrderDate (DateTime)

SalesOrderItem
SalesOrderItemId (PK, int)
Qty (int)
UnitPrice (decimal)
SalesOrderId (FK, int)

My DTO Model:

CustomerSalesPerformance
Month (int)
Sales (decimal?)

Answer

After GroupBy, you cannot use navigation properties. So just rewrite query to do that before.

var salesPerformance = 
    from so in _context.SalesOrder
    where so.OrderDate >= baselineDate && so.CustomerId == customerId)
    from sio in so.SalesOrderItems
    group sio by so.OrderDate.Month into g
    select new CustomerSalesPerformance
    {
        Month = g.Key,
        Sales = g.Sum(i => ii.Qty * i.UnitPrice)
    });

Advertisement

Answer