EF Core 2.1 evaluates locally when Sum complex and Grouping

Question

I'm using EF Core 2.1 and query don't evaluates on SQL server side.

Model using in this query is:

public class V_TurnoverByDivision
{      
    public long Id { get; set; }
    public decimal LineAmount { get; set; }
    public DateTime? PostingDate { get; set; }
    public decimal Quantity { get; set; }
    public decimal UnitCostLcy { get; set; }
    public string DivisionCode { get; set; }
    public string DivisionDescription { get; set; }
    public string TopDivisionCode { get; set; }
    public string TopDivisionDescription { get; set; }
    public decimal RUCAmount { get; set; }
}

This LINQ statement is run completely in SQL Server:

return query
  .GroupBy(g => new { g.DivisionCode, g.DivisionDescription, g.TopDivisionCode, g.TopDivisionDescription, g.PostingDate })
  .Select(s =>
                    new V_TurnoverByDivision
                    {
                        DivisionCode = s.Key.DivisionCode,
                        DivisionDescription = s.Key.DivisionDescription,
                        TopDivisionCode = s.Key.TopDivisionCode,
                        TopDivisionDescription = s.Key.TopDivisionDescription,
                        PostingDate = s.Key.PostingDate,
                        LineAmount = s.Sum(ss => ss.LineAmount),
                        RUCAmount = s.Sum(ss => ss.LineAmount - (ss.Quantity * ss.UnitCostLcy))
                    });

and generates the following SQL

SELECT
    [v].[BIInvCNLinesID]
   ,[v].[DivisionCode]
   ,[v].[DivisionDescription]
   ,[v].[LineAmount]
   ,[v].[PostingDate]
   ,[v].[Quantity]
   ,[v].[TopDivisionCode]
   ,[v].[TopDivisionDescription]
   ,[v].[UnitCostLcy]
FROM [V_TurnoverByDivision] AS [v]
WHERE [v].[PostingDate] >= @__firstDayOfcurrentMonth_0
ORDER BY [v].[DivisionCode], [v].[DivisionDescription], [v].[TopDivisionCode], [v].[TopDivisionDescription], [v].[PostingDate]

This LINQ statement works but performs the GroupBy in memory

and I get warrnings in the Output windows

Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'Sum()' could not be translated and will be evaluated locally.

BUT WHEN I use this query

                  return query
                .GroupBy(g => new { g.DivisionCode, g.DivisionDescription, g.TopDivisionCode, g.TopDivisionDescription, g.PostingDate })
                .Select(s =>
                    new V_TurnoverByDivision
                    {
                        DivisionCode = s.Key.DivisionCode,
                        DivisionDescription = s.Key.DivisionDescription,
                        TopDivisionCode = s.Key.TopDivisionCode,
                        TopDivisionDescription = s.Key.TopDivisionDescription,
                        PostingDate = s.Key.PostingDate,
                        LineAmount = s.Sum(ss => ss.LineAmount)
                    });
            };

and SQL generates query that should be:

SELECT
    [v].[DivisionCode]
   ,[v].[DivisionDescription]
   ,[v].[TopDivisionCode]
   ,[v].[TopDivisionDescription]
   ,[v].[PostingDate]
   ,SUM([v].[LineAmount]) AS [LineAmount]
FROM [V_TurnoverByDivision] AS [v]
WHERE [v].[PostingDate] >= @__firstDayOfcurrentMonth_0
GROUP BY [v].[DivisionCode]
        ,[v].[DivisionDescription]
        ,[v].[TopDivisionCode]
        ,[v].[TopDivisionDescription]
        ,[v].[PostingDate]

How to solve problem with:

RUCAmount = s.Sum(ss => ss.LineAmount - (ss.Quantity * ss.UnitCostLcy))

Answer 1

This is EF Core GroupBy translation limitation (probably will be resolved in some future version). In order to be translatable to SQL, the aggregate method expression should be simple property accessor.

That's why

s.Sum(ss => ss.LineAmount)

translates, but

s.Sum(ss => ss.LineAmount - (ss.Quantity * ss.UnitCostLcy))

doesn't.

Hence the solution is to pre-select the expression(s) needed for aggregates. One way to do that is to use the GroupBy overload with element selector:

return query
   .GroupBy(e => new // Key
   { 
       e.DivisionCode,
       e.DivisionDescription,
       e.TopDivisionCode,
       e.TopDivisionDescription,
       e.PostingDate
   },
   e => new // Element
   {
       e.LineAmount,
       RUCAmount = e.LineAmount - (e.Quantity * e.UnitCostLcy) // <--
   })
  .Select(g => new V_TurnoverByDivision
  {
      DivisionCode = g.Key.DivisionCode,
      DivisionDescription = g.Key.DivisionDescription,
      TopDivisionCode = g.Key.TopDivisionCode,
      TopDivisionDescription = g.Key.TopDivisionDescription,
      PostingDate = g.Key.PostingDate,
      LineAmount = g.Sum(e => e.LineAmount),
      RUCAmount = g.Sum(e => e.RUCAmount) // <--
  });