CODEWITHSUNDEEP
c#sql-serverentity-frameworkentity-framework-core
JeremyB
JeremyB

Reputation: 61

EF Core distinct by column

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();

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()

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.

Upvotes: 6

Views: 8699

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205609

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

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();

Upvotes: 12

Related Questions