求和和分组时，EF Core 2.1在本地评估

内沙塞尔维亚

我正在使用EF Core 2.1，并且查询在SQL Server端不进行评估。

该查询中使用的模型是：

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

此LINQ语句在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))
                    });

并生成以下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]

该LINQ语句有效，但在内存中执行GroupBy

我在“输出”窗口中收到警告

Microsoft.EntityFrameworkCore.Query：警告：LINQ表达式'Sum（）'无法翻译，将在本地进行评估。

但是何时使用此查询

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

SQL生成的查询应为：

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]

如何解决问题：

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

伊万·斯托夫

这是EF CoreGroupBy翻译限制（可能会在将来的某些版本中解决）。为了可以转换为SQL，聚合方法表达式应该是简单的属性访问器。

这就是为什么

s.Sum(ss => ss.LineAmount)

翻译，但是

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

没有。

因此，解决方案是预先选择聚集所需的表达式。一种方法是对元素选择器使用GroupBy重载：

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

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。