如何在dotnet core Entity Framework中从多个表中分组数据(GroupBy)？

Question

我正在开发一个类似于eBay的市场Web应用程序。典型情况是：

用户提供一个或多个特定类型的商品组成的报价，之后其他用户会对该报价进行竞标。

以下是简化的图示：

在SQL Fiddle (这里) 上，您可以查看CREATE TABLE和INSERT INTO语句。

示例数据：有两份报价。其中一份报价（ID为1）由一种“手表”类型的物品组成。另外还有一份报价（ID为2），由一种“耳机”类型的物品组成。 两份报价都有出价记录。关于手表的报价有两条记录：一条100美元，另一条120美元。耳机的出价有两条记录，分别是50美元和80美元。

我的目标是获得每种类型的平均出价。在这个示例中，这意味着我想得到手表的平均出价为110美元，耳机的平均出价为65美元。要使用T-SQL实现这一点，我会编写以下查询语句：

SELECT t.name,
       avg(amount)
FROM bid b
LEFT JOIN offer o ON b.OfferId = o.id
LEFT JOIN offeritem oi ON o.id = oi.OfferId
LEFT JOIN itemType t ON oi.itemtypeid = t.Id
GROUP BY t.name

所以，我的问题是 - 如何在dotnet core 3.0 EntityFramework中实现这一点

使用GroupBy，像这样：

_context.Bids
    .Include(b => b.Offer)
        .ThenInclude(o => o.OfferItems)
            .ThenInclude(os => os.ItemType)
    .GroupBy(b => b.Offer.OfferItems.First().ItemType.Name);

抛出异常:

不支持客户端分组(GroupBy)。

当我尝试使用投影(Projection)时，像这样:

_context.Bids
    .Include(b => b.Offer)
        .ThenInclude(o => o.OfferItems)
            .ThenInclude(os => os.ItemType)
    .GroupBy(b => b.Offer.OfferItems.First().ItemType.Name)
    .Select(g => new
    {
        Key = g,
        Value = g.Average(b => b.Amount)
    });

我又遇到了异常。

处理 LINQ 时出现失败。这可能表明 EF Core 中存在错误或限制。

编辑:

这种方法

_context.Bids
    .Include(b => b.Offer)
        .ThenInclude(o => o.OfferItems)
            .ThenInclude(os => os.ItemType)
    .GroupBy(b => new { b.Offer.OfferItems.First().ItemType.Name}, b => b.Amount)
    .Select(g => new
    {
        Key = g.Key.Code,
        Value = g.Average()
    });

这次也抛出了异常：

无法在用于 GROUP BY 子句的分组列表中使用聚合或子查询表达式。

是否有一种方法可以对这些数据进行分组（获取简单平均值），还是应该再查询一次并迭代整个集合，然后自己进行计算？那肯定会降低性能（我原本希望可以进行服务器分组，但正如您所见，我遇到了上述问题）。有什么想法吗？谢谢！

- Nino

---

经过一番深入挖掘，我要冒个险并希望它能起作用。你可以尝试把 include 替换成 join 吗？我一直看到人们做这件事情的例子，但他们使用的是 join 而不是 include。Join 和 include 本质上是不同的。我还读到一个限制是同时使用 includes 和 groupbys 的问题在于 includes 要求输出保持不变。但是 groupby 会改变输出类型。所以……这有点冒险，但我抱着一线希望。 - Davey van Tilburg

感谢您的建议，@DaveyvanTilburg。我没有尝试使用Join，因为Radik的方法看起来很有前途，所以我用那种方法解决了它。 - Nino

很好！很高兴你得到了答案 :) - Davey van Tilburg

Answer 1

在您的情况下，很难将子查询隐藏在分组中。您可以尝试以下方法。

var joined =
    context.Bid
        .SelectMany(x =>
            x.Offer.OfferItem
                .Select(y => new
                {
                    Amount = x.Amount,
                    Name = y.ItemType.Name
                })
                .Take(1));

var grouped = from i in joined
    group i by i.Name into groups
    select new
    {
        Key = groups.Key,
        Amount = groups.Average(x => x.Amount)
    };

它给了我一个查询。

SELECT [t].[Name] AS [Key], AVG([t].[Amount]) AS [Amount]
  FROM [Bid] AS [b]
  INNER JOIN [Offer] AS [o] ON [b].[OfferId] = [o].[Id]
  CROSS APPLY (
      SELECT TOP(1) [b].[Amount], [i].[Name], [o0].[Id], [i].[Id] AS [Id0], [o0].[OfferId]
      FROM [OfferItem] AS [o0]
      INNER JOIN [ItemType] AS [i] ON [o0].[ItemTypeId] = [i].[Id]
      WHERE [o].[Id] = [o0].[OfferId]
  ) AS [t]
  GROUP BY [t].[Name]

- Radik

---

谢谢你的回答。很有道理：将表格扁平化和去规范化，这样EF就可以对其进行分组。我猜我花了太多时间来让它工作，所以像你这样的解决方案逃脱了我的注意力。不管怎样，这个方法可行。非常感谢你。 - Nino