EF Core按列区分 [英] EF Core distinct by column

问题描述

在EF 6中，如果我想通过不同的姓氏来选择用户，例如，我可以这样做:

In EF 6, if I wanted to select users for instance by distinct last name I could do something like this:

var users = _context.User
            .GroupBy(x => x.LastName)
            .Select(x => x.FirstOrDefault())
            .OrderBy(x => x.LastName)
            .Take(10)
            .ToList();

在EF Core 3.1.6中，这个非常相同的查询给了我以下异常:

In EF Core 3.1.6 this very same query gives me the following exception:

System.InvalidOperationException: The LINQ expression '(GroupByShaperExpression:
KeySelector: (u.LastName), 
ElementSelector:(EntityShaperExpression: 
    EntityType: User
    ValueBufferExpression: 
        (ProjectionBindingExpression: EmptyProjectionMember)
    IsNullable: False
)
)
    .FirstOrDefault()' 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()

是否可以使用不使用AsEnumerable(或其他替代方法)的查询来将整个庞大表加载到内存中?我在下面使用的数据库是Microsoft SQL Server 2014，可以处理这种查询.

Is there any way to use that query without using AsEnumerable (or other alternatives) which would load this entire huge table into memory? The database I use underneath is Microsoft SQL Server 2014 which can handle this kind of query.

推荐答案

在EF Core 5中可能会支持这种类型的查询(当然，在EF Core GitHub存储库中肯定有未解决的问题).

This type of queries will probably be supported in EF Core 5 (there for sure are open issues in EF Core GitHub repository).

EF Core 3.x中的解决方法类似于

The workaround in EF Core 3.x is similar to How to select top N rows for each group in a Entity Framework GroupBy with EF 3.1 - (1)use a subquery to select the distinct key values and (2) then join/correlate it with the main query combined with limiting operator (in this case, Take(1)):

var users = _context.User.Select(x => x.LastName).Distinct() // (1)
    .SelectMany(key => _context.User.Where(x => x.LastName == key).Take(1)) // (2)
    // the rest is the same as the origonal
    .OrderBy(x => x.LastName)
    .Take(10)
    .ToList();

这篇关于EF Core按列区分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！