如何使用 EF 3.1 在实体框架 GroupBy 中为每个组选择前 N 行 [英] How to select top N rows for each group in a Entity Framework GroupBy with EF 3.1

问题描述

我需要使用实体框架获取表中每个组的前 10 行.基于 SO 上的其他解决方案，我尝试了两件事:

I need to get top 10 rows for each group in a table with entity framework. Based on other solution on SO, I tried 2 things:

var sendDocuments = await context.Set<DbDocument>
    .Where(t => partnerIds.Contains(t.SenderId))
    .GroupBy(t => t.SenderId)
    .Select(t => new
    {
        t.Key,
        Documents = t.OrderByDescending(t2 => t2.InsertedDateTime).Take(10)
    })                
    .ToArrayAsync();

错误:

System.InvalidOperationException: 'The LINQ expression
'(GroupByShaperExpression: KeySelector: (d.SenderId), 
ElementSelector:(EntityShaperExpression: 
    EntityType: DbDocument
    ValueBufferExpression: 
        (ProjectionBindingExpression: EmptyProjectionMember)
    IsNullable: False ) )
    .OrderByDescending(t2 => t2.InsertedDateTime)' could not be translated. Either rewrite the query in a form that can be translated,
> or switch to client evaluation explicitly by inserting a call to
> either AsEnumerable(), AsAsyncEnumerable(), ToList(), or
> ToListAsync().

和

var sendDocuments2 = await context.Set<DbDocument>
    .Where(t => partnerIds.Contains(t.SenderId))
    .GroupBy(t => t.SenderId)
    .SelectMany(t => t.OrderByDescending(t2 => t2.InsertedDateTime).Take(10))
    .ToArrayAsync();

错误:

System.InvalidOperationException: 'LINQ 表达式的处理't => t.OrderByDescending(t2 => t2.InsertedDateTime).AsQueryable().Take(10)' by 'NavigationExpandingExpressionVisitor' 失败.这可能表示 EF Core 中存在错误或限制.

System.InvalidOperationException: 'Processing of the LINQ expression 't => t .OrderByDescending(t2 => t2.InsertedDateTime) .AsQueryable() .Take(10)' by 'NavigationExpandingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core.

还有其他想法吗?

推荐答案

更新(EF Core 6.0):

EF Core 6.0 添加了对转换 GroupBy 结果集投影的支持，因此用于获取 (key, items) 的原始代码现在可以正常工作，即

EF Core 6.0 added support for translating GroupBy result set projection, so the original code for taking (key, items) now works as it should, i.e.

var query = context.Set<DbDocument>()
    .Where(e => partnerIds.Contains(e.SenderId))
    .GroupBy(e => e.SenderId)
    .Select(g => new
    {
        g.Key,
        Documents = g.OrderByDescending(e => e.InsertedDateTime).Take(10)
    });

然而，仍然不支持展平(通过 SelectMany)，因此如果您需要此类查询形状，则必须使用以下解决方法.

However flattening (via SelectMany) is still unsupported, so you have to use the below workaround if you need such query shape.

原始(EF Core 3.0/3.1/5.0):

这是一个常见问题，不幸的是，EF Core 3.0/3.1/5.0 不支持专门用于 GroupBy 的查询转换器.

This is a common problem, unfortunately not supported by EF Core 3.0/3.1/5.0 query translator specifically for GroupBy.

解决方法是通过关联 2 个子查询来手动进行摸索 - 一个用于键，一个用于相应的数据.

The workaround is to do the groping manually by correlating 2 subqueries - one for keys and one for corresponding data.

将其应用于您的示例将是这样的.

Applying it to your examples would be something like this.

如果您需要(键、项目)对:

If you need (key, items) pairs:

var query = context.Set<DbDocument>()
    .Where(t => partnerIds.Contains(t.SenderId))
    .Select(t => t.SenderId).Distinct() // <--
    .Select(key => new
    {
        Key = key,
        Documents = 
            context.Set<DbDocument>().Where(t => t.SenderId == key) // <--
                 .OrderByDescending(t => t.InsertedDateTime).Take(10)
                 .ToList() // <--
    });

如果您只需要包含每个键前 N 个项目的平面结果集:

If you need just flat result set containing top N items per key:

var query = context.Set<DbDocument>()
    .Where(t => partnerIds.Contains(t.SenderId))
    .Select(t => t.SenderId).Distinct() // <--
    .SelectMany(key => context.Set<DbDocument>().Where(t => t.SenderId == key) // <--
        .OrderByDescending(t => t.InsertedDateTime).Take(10)
    );

这篇关于如何使用 EF 3.1 在实体框架 GroupBy 中为每个组选择前 N 行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！