首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
社区首页 >问答首页 >如何在LINQ子查询中使用GroupBy()?

如何在LINQ子查询中使用GroupBy()?
EN

Stack Overflow用户
提问于 2021-07-14 13:26:47
回答 1查看 479关注 0票数 0

我有非常简单的LINQ表达式

代码语言:javascript
代码运行次数:0
运行
复制
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()
    });

当我执行它时,我捕捉到错误:

代码语言:javascript
代码运行次数:0
运行
复制
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()做了很多案子。但不明白为什么会发生这种事。因为我只需要像这样分组。

EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2021-07-14 13:52:19

错误消息说,您必须在投影中包含Id列。但是你不能用GroupBy做这件事。因此,将查询重写为两个步骤(删除,不需要包含):

代码语言:javascript
代码运行次数:0
运行
复制
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
                })
    });

类似的查询,但更优化:

代码语言:javascript
代码运行次数:0
运行
复制
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()
    });
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/68379009

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档