如何在LINQ子查询中使用GroupBy()？
如何在LINQ子查询中使用GroupBy()？
提问于 2021-07-14 13:26:47
我有非常简单的LINQ表达式
IQueryable<FreeBetDTO> records = UnitOfWork.FreeBets
.Include(f => f.FreeBetCategories)
.Include(f => f.FreeBetCards)
.Where(f => f.FreeBetCards.Any(cards => cards.UserId == request.UserId))
.Select(f => new FreeBetDTO
{
FreeBetId = f.FreeBetId
LineCategories = f.FreeBetCategories
.GroupBy(g => new { g.LineCategoryID, g.Title })
.Select(c =>
new LineCategoryDTO
{
LineCategoryID = c.Key.LineCategoryID,
Title = c.Key.Title
}).AsEnumerable()
});当我执行它时，我捕捉到错误：
System.InvalidOperationException: Unable to translate collection subquery in projection since it uses 'Distinct' or 'Group By' operations and doesn't project key columns of all of it's tables which are required to generate results on client side. Missing column: t.ID. Either add column(s) to the projection or rewrite query to not use 'GroupBy'/'Distinct' operation.
at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.
....问题在这里，.GroupBy(g => new { g.LineCategoryID, g.Title })。如果我不分组记录，错误就会消失。我用GroupBy()和Distinct()做了很多案子。但不明白为什么会发生这种事。因为我只需要像这样分组。
回答 1
Stack Overflow用户
回答已采纳
发布于 2021-07-14 13:52:19
错误消息说，您必须在投影中包含Id列。但是你不能用GroupBy做这件事。因此，将查询重写为两个步骤(删除，不需要包含)：
var rawRecords = UnitOfWork.FreeBets
.Where(f => f.FreeBetCards.Any(cards => cards.UserId == request.UserId))
.Select(f => new
{
FreeBetId = f.FreeBetId
LineCategories = f.FreeBetCategories.Select(c => new { c.Id, c.LineCategoryID, c.Title })
.ToList()
})
.AsEnumerable();
var records = rawRecords
.Select(f => new FreeBetDTO
{
FreeBetId = f.FreeBetId
LineCategories = f.LineCategories.GroupBy(g => new { g.LineCategoryID, g.Title })
.Select(c =>
new LineCategoryDTO
{
LineCategoryID = c.Key.LineCategoryID,
Title = c.Key.Title
})
});类似的查询，但更优化：
var query =
from f in UnitOfWork.FreeBets
from c in f.FreeBetCards
where f.FreeBetCards.Any(cards => cards.UserId == request.UserId)
select new { f.FreeBetId, c.LineCategoryID, c.Title };
query = query.Distinct();
var records = query.AsEnumerable()
.GroupBy(f => f.FreeBetId)
.Select(g => new FreeBetDTO
{
FreeBetId = g.Key
LineCategories = g.Select(c =>
new LineCategoryDTO
{
LineCategoryID = c.LineCategoryID,
Title = c.Title
})
.AsEnumerable()
});页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/68379009
复制相关文章
相似问题
腾讯云开发者
