如何使 EF Core 3 以与 EF Core 7 相同的方式翻译外键分组？

huangapple
go评论93阅读模式
英文:

How can I make EF Core 3 translate the group by foreign key the same way efcore7 does it?

问题

我有以下的实体和数据库上下文：

  1. public class Item
  2. {
  3.     public int Id { get; set; }
  5.     public int? ReceiptId { get; set; }
  6.     public int ItemTypeId { get; set; }
  7. }
  9. public class ItemType
  10. {
  11.     public int Id { get; set; }
  12.     public string Name { get; set; }
  14.     public List<Item> Items { get; set; }
  15. }
  17. public class Receipt
  18. {
  19.     public int Id { get; set; }
  20.     public string ReceiptInfo { get; set; }
  22.     public List<Item> Items { get; set; }
  23. }

我试图获取收据的列表，但不想包含它们包含的项目，而是想要它们包含“itemType”和每种项目的数量。我编写了以下的 LINQ 查询，它有效：

  1. var result = _databaseContext.Receipts.Select(r => new
  2. {
  3.     r.Id,
  4.     r.ReceiptInfo,
  5.     ItemInfo = r.Items.GroupBy(item => item.ItemTypeId)
  6.                       .Select(group => new
  7.                       {
  8.                           IdItemType = group.Key,
  9.                           AmountOfItems = group.Count(),
  10.                       }).ToList()
  11. });

在 EF Core 7 中，它被转换为以下的 SQL 查询：

  1. SELECT [r].[Id], [r].[ReceiptInfo], [t].[IdItemType], [t].[AmountOfItems]
  2. FROM [Receipts] AS [r]
  3. OUTER APPLY 
  4.     (SELECT [i].[ItemTypeId] AS [IdItemType], COUNT(*) AS [AmountOfItems]
  5.      FROM [Items] AS [i]
  6.      WHERE [r].[Id] = [i].[ReceiptId]
  7.      GROUP BY [i].[ItemTypeId]) AS [t]
  8. ORDER BY [r].[Id]

然而，我需要在一个不支持 EF Core 3.1 以前版本的旧项目中执行此操作。在那里，查询被转换为不同的方式，并且我会收到以下错误：

选择列表中的列 'Receipts.Id' 无效，因为它既不包含在聚合函数中，也不包含在 GROUP BY 子句中。

英文:

I have the following entities and a database context

  1. public class Item
  2. {
  3.     public int Id { get; set; }
  5.     public int? ReceiptId { get; set; }
  6.     public int ItemTypeId { get; set; }
  7. }
  9. public class ItemType
  10. {
  11.     public int Id { get; set; }
  12.     public string Name { get; set; }
  14.     public List&lt;Item&gt; Items { get; set; }
  15. }
  17. public class Receipt
  18. {
  19.     public int Id { get; set; }
  20.     public string ReceiptInfo { get; set; }
  22.     public List&lt;Item&gt; Items { get; set; }
  23. }

I'm trying to get a the list of receipts, but instead of containing the items they contain, I want them to have the itemType and the amount of items for each. I have written the following linq query, which works:

  1. var result = _databaseContext.Receipts.Select(r =&gt; new
  2.             {
  3.                 r.Id,
  4.                 r.ReceiptInfo,
  5.                 ItemInfo = r.Items.GroupBy(item =&gt; item.ItemTypeId)
  6.                                   .Select(group =&gt; new
  7.                                   {
  8.                                       IdItemType = group.Key,
  9.                                       AmountOfItems = group.Count(),
  10.                                   }).ToList()
  12.             });

With EF Core 7, it is translated to the following SQL query:

  1. SELECT [r].[Id], [r].[ReceiptInfo], [t].[IdItemType], [t].[AmountOfItems]
  2. FROM [Receipts] AS [r]
  3. OUTER APPLY 
  4.     (SELECT [i].[ItemTypeId] AS [IdItemType], COUNT(*) AS [AmountOfItems]
  5.      FROM [Items] AS [i]
  6.      WHERE [r].[Id] = [i].[ReceiptId]
  7.      GROUP BY [i].[ItemTypeId]) AS [t]
  8. ORDER BY [r].[Id]

Yet, I need to do this in an older project which doesn't support a version older than 3.1 for EF Core.

There it translates the query differently and I get this error

> Column 'Receipts.Id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

答案1

得分: 1

在 EF Core 3.1 的情况下，您需要在客户端端对加载的详细项进行后处理：

  1. var rawData = _databaseContext.Receipts.Select(r => new
  2.     {
  3.         r.Id,
  4.         r.ReceiptInfo,
  5.         RawItemInfo = r.Items.Select(item => new
  6.         {
  7.             IdItemType = item.ItemTypeId
  8.         }).ToList()
  9.     })
  10.     .ToList();
  12. var result = rawData
  13.     .Select(r => new
  14.     {
  15.         r.Id,
  16.         r.ReceiptInfo,
  17.         ItemInfo = r.RawItemInfo
  18.             .GroupBy(item => item.ItemTypeId)
  19.             .Select(group => new
  20.             {
  21.                 IdItemType = group.Key,
  22.                 AmountOfItems = group.Count(),
  23.             }).ToList()
  24.     });
英文:

In case of EF Core 3.1, you have to postprocess loaded detail items on the client side:

  1. var rawData = _databaseContext.Receipts.Select(r =&gt; new
  2.     {
  3.         r.Id,
  4.         r.ReceiptInfo,
  5.         RawItemInfo = r.Items.Select(item =&gt; new
  6.         {
  7.             IdItemType = item.ItemTypeId
  8.         }).ToList()
  9.     })
  10.     .ToList();
  12. var result = rawData
  13.     .Select(r =&gt; new
  14.     {
  15.         r.Id,
  16.         r.ReceiptInfo,
  17.         ItemInfo = r.RawItemInfo
  18.             .GroupBy(item =&gt; item.ItemTypeId)
  19.             .Select(group =&gt; new
  20.             {
  21.                 IdItemType = group.Key,
  22.                 AmountOfItems = group.Count(),
  23.             }).ToList()
  24.     });

答案2

得分: 1

如您所见，EFC 7中的GroupBy支持已经大幅改进。EFC 3仅支持在查询根部分使用聚合操作的GroupBy。

因此，要在EFC 3中运行它，您需要将查询强制转换为受支持的形式。为了获得相同的分组级别，查询从Items开始，对三个元素进行分组和聚合，而不是两个：

  1. var result = _databaseContext.Items
  2.     .GroupBy(item => new { item.ReceiptId, item.Receipt.ReceiptInfo, item.ItemTypeId })
  3.     .Select(group => new
  4.     {
  5.         Id = group.Key.ReceiptId,
  6.         ReceiptInfo = group.Key.ReceiptInfo,
  7.         IdItemType = group.Key.ItemTypeId,
  8.         NrOfItems = group.Count(),
  9.     })

这将返回与原始查询相同的数据，并在数据库中进行数据的聚合。要获得相同的结果形状，需要在内存中进行一些后处理（即在调用AsEnumerable()后）：

  1.     .AsEnumerable()
  2.     .GroupBy(x => new { x.Id, x.Receipt.ReceiptInfo })
  3.     .Select(g => new
  4.     {
  5.         g.Key.Id,
  6.         g.Key.ReceiptInfo,
  7.         ItemInfo = g.Select(x => new { x.IdItemType, x.NrOfItems })
  8.        
  9.     });

这需要添加一个名为Item.Receipt的导航属性。

英文:

As you see, GroupBy support has improved drastically in EFC 7. EFC 3 only supports GroupBy with aggregates at the query root.

Therefore, to make it run in EFC 3 you need to force the query into the supported shape. To get the same grouping level, the query starts at Items and groups + aggregates once over three elements instead of two:

  1. var result = _databaseContext.Items
  2.     .GroupBy(item =&gt; new { item.ReceiptId, item.Receipt.ReceiptInfo, item.ItemTypeId })
  3.     .Select(group =&gt; new
  4.     {
  5.         Id = group.Key.ReceiptId,
  6.         ReceiptInfo = group.Key.ReceiptInfo,
  7.         IdItemType = group.Key.ItemTypeId,
  8.         NrOfItems = group.Count(),
  9.     })

That returns the same data as the original query and does the reduction of data (aggregate) in the database. To get the same result shape, it needs some post-processing in-memory (i.e. after calling AsEnumerable()):

  1.     .AsEnumerable()
  2.     .GroupBy(x =&gt; new { x.Id, x.Receipt.ReceiptInfo })
  3.     .Select(g =&gt; new
  4.     {
  5.         g.Key.Id,
  6.         g.Key.ReceiptInfo,
  7.         ItemInfo = g.Select(x =&gt; new { x.IdItemType, x.NrOfItems })
  8.        
  9.     });

This requires adding a navigation property Item.Receipt.

huangapple
  • 本文由 发表于 2023年2月19日 21:15:20
  • 转载请务必保留本文链接：https://go.coder-hub.com/75500389.html
  • .net
  • c#
  • ef-core-3.1
  • entity-framework-core
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定