c# - EF Core 2.1 RC 1 GroupBy 仍在本地评估

Question

我按照 official guide 将 ASP.NET Core 项目从版本 2.0.x 升级到 2.1.0-rc1来自 ASP.NET 博客。 EF Core 包也更新到相同的版本。我使用的数据库提供程序是 Microsoft.EntityFrameworkCore.SqlServer .我期待着这次升级，因为我需要很多 LINQ GroupBy translation特征。我的一个查询如下:

await _db.Documents
         .ApplyFilter(options)
         .Where(x => x.SubscriptionId == subscriptionId && x.Status != DocumentStatus.Deleted)
         .GroupBy(document => new { document.SubscriptionId })
         .Select(group => new {
            Total = group.Sum(x => x.Total),
            TotalDiscount = group.Sum(x => x.TotalDiscount),
            TotalNet = group.Sum(x => x.TotalNet != null ? x.TotalNet.Value * (decimal)x.CurrencyRate : 0),
            SubTotal = group.Sum(x => x.SubTotal != null ? x.SubTotal.Value * (decimal)x.CurrencyRate : 0),
            TotalSalesTax = group.Sum(x => x.TotalSalesTax != null ? x.TotalSalesTax.Value * (decimal)x.CurrencyRate : 0),
            TotalTax = group.Sum(x => x.TotalTax != null ? x.TotalTax.Value * (decimal)x.CurrencyRate : 0),
            TotalPayable = group.Sum(x => x.TotalPayable != null ? x.TotalPayable.Value * (decimal)x.CurrencyRate : 0)
        })
        .ToListAsync();

当这个查询运行时，我仍然收到一条警告:

EFCore Linq Query Could not be translated and will be evaluated locally.

所以我想问一下我是不是做错了什么(非常)错误，或者这个功能还没有像广告上说的那样可用？你们有没有尝试过类似的东西？预先感谢您的帮助。

---

编辑

为了测试问题，按照评论中的 Ivan Stoev 的建议尝试了以下查询(从 Sum 方法中删除了条件检查)，但结果是一样。

await _db.Documents
         .ApplyFilter(options)
         .Where(x => x.SubscriptionId == subscriptionId && x.Status != DocumentStatus.Deleted)
         .GroupBy(document => new { document.SubscriptionId })
         .Select(group => new {
            Total = group.Sum(x => x.Total),
            TotalDiscount = group.Sum(x => x.TotalDiscount),
            TotalNet = group.Sum(x => x.TotalNet),
            SubTotal = group.Sum(x => x.SubTotal),
            TotalSalesTax = group.Sum(x => x.TotalSalesTax),
            TotalTax = group.Sum(x => x.TotalTax),
            TotalPayable = group.Sum(x => x.TotalPayable)
          })
          .ToListAsync();

---

解决方案

经过实验，我发现了以下内容。我发布它以防有人遇到同样的问题。以下查询完美地转换为 T-SQL 语句，因此不会在客户端进行评估。

await (from d in _db.Documents.ApplyFilter(options)
       where d.SubscriptionId == subscriptionId && d.Status != DocumentStatus.Deleted
       group d by d.SubscriptionId into g
       select new {
          Total = g.Sum(x => x.Total/* * Convert.ToDecimal(x.CurrencyRate)*/),
          TotalDiscount = g.Sum(x => x.TotalDiscount/* * Convert.ToDecimal(x.CurrencyRate)*/),
          TotalNet = g.Sum(x => x.TotalNet/* * Convert.ToDecimal(x.CurrencyRate)*/),
          SubTotal = g.Sum(x => x.SubTotal/* * Convert.ToDecimal(x.CurrencyRate)*/),
          TotalSalesTax = g.Sum(x => x.TotalSalesTax/* * Convert.ToDecimal(x.CurrencyRate)*/),
          TotalTax = g.Sum(x => x.TotalTax/* * Convert.ToDecimal(x.CurrencyRate)*/),
          TotalPayable = g.Sum(x => x.TotalPayable/* * Convert.ToDecimal(x.CurrencyRate)*/)
       }).ToListAsync();

明显的区别是我使用LINQ 查询语法 而不是扩展方法。我还注释掉了与 CurrencyRate 属性的乘法，因为 它也会导致在本地评估查询。这看起来很奇怪，因为查询语法也在底层转换为扩展方法。

我还在 Entity Framework Core 的 Github 仓库中开了一个 issue 你可以找到here

Answer 1

在我的例子中，分组是针对此查询在客户端评估的:

await context.MyCollection
.GroupBy(x => x.Tag.Id)
.ToDictionaryAsync(x => x.Key, x => x.Count());

将其更改为以下使其在服务器端进行评估:

await context.MyCollection
.GroupBy(x => x.Tag.Id)
.Select(g => new {g.Key, Count = g.Count()})
.ToDictionaryAsync(arg => arg.Key, arg => arg.Count);