我有非常简单的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()
做了很多案子。但不明白为什么会发生这种事。因为我只需要像这样分组。
发布于 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()
});
https://stackoverflow.com/questions/68379009
复制相似问题