如何使用EF 3.1为Entity Framework GroupBy中的每个组选择前N行 [英] How to select top N rows for each group in a Entity Framework GroupBy with EF 3.1
问题描述
我需要使用实体框架为表中的每个组获取前10行。
基于SO上的其他解决方案,我尝试了两件事:
var sendDocuments =等待上下文。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:'LINQ表达式
'(GroupByShaperExpression:KeySelector:(d.SenderId),
ElementSelector:(EntityShaperExpression:
EntityType:DbDocument
ValueBufferExpression :
(ProjectionBindingExpression:EmptyProjectionMember)
IsNullable:False))
.OrderByDescending(t2 => t2.InsertedDateTime)'无法翻译。
>以一种可以翻译的形式重写查询。或通过插入对
>的调用来明确切换到客户评估。 AsEnumerable(),AsAsyncEnumerable(),ToList()或
> ToListAsync()。
和
var sendDocuments2 =等待上下文。
.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)通过 NavigationExpandingExpressionVisitor失败。
还有其他想法吗?
这是一个常见问题,很遗憾,EF Core 3.0 / 3.1查询翻译器不专门为 GroupBy
提供支持。 / p>
解决方法是通过关联2个子查询来手动进行摸索-一个用于键,另一个用于对应的数据。
将其应用于示例将是这样。
如果您需要(键,项)对:
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个项目的简单结果集,则:
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)
);
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();
error:
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().
and
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();
error:
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.
Any other idea?
This is a common problem, unfortunately not supported by EF Core 3.0/3.1 query translator specifically for GroupBy
.
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() // <--
});
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为Entity Framework GroupBy中的每个组选择前N行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!