.NET Core 3.1 - Linq - Select records grouped by a unique attribute

Question

In .Net framework I had this working query:

IEnumerable<Cars> LatestCars = await _context.Cars.Where(x => x.IsActive == true && x.IsDeleted == false)
            .GroupBy(y => y.ManufacturerId)
            .Select(z =>
                z.OrderByDescending(k => k.ReleaseDate)
                .FirstOrDefault()
            )
            .OrderByDescending(l => l.ReleaseDate)
            .Take(5)
            .ToListAsync();

This basicly gets the latest 5 cars released by distinct manufacturers.

But when I switched to .NET Core. This query is not working anymore. And I have this error when I run it:

System.InvalidOperationException: The LINQ expression '(GroupByShaperExpression:
KeySelector: (g.ManufacturerId), 
ElementSelector:(EntityShaperExpression: 
    EntityType: Cars
    ValueBufferExpression: 
        (ProjectionBindingExpression: EmptyProjectionMember)
    IsNullable: False
)
)
    .OrderByDescending(p => p.ReleaseDate)' 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().

Do you have any suggestions? Thank you.

Try to look at this: https://stackoverflow.com/questions/59965647/how-to-use-groupby-in-an-asynchronous-manner-in-ef-core-3-1 , it may help you — Karel Křesťan, Oct 25 '20 at 16:36

score 0 · Answer 1 · answered Oct 25 '20 at 21:24

EF Core, starting from version 3, throws an exception when an expression could only be executed locally. And actually the problem is not the .OrderByDescending() per se, but the fact that the query contains grouping, and grouping only supports Select() projections that aggregate the result to a scalar. Because only this can be translated into a SQL GROUP BY statement.

There is one handy way of dealing with this. If you have a Manufacturer model which contains an IEnumerable<Car> Cars navigational property, you can start from there, and that leads to a translatable query.

So, in your case this working query could be the following:

  Manufacturers.Select(m => m.Cars.OrderByDescending(c => c.ReleaseDate).FirstOrDefault())
    .OrderByDescending(c => c.ReleaseDate).Take(5)

The downside is that you can have null values in this list, if a manufacturer has 0 cars, so normally it's a good idea to filter those out, but in this case the OrderByDescending() pretty much mitigates this.

Another possible solution is to query a limited number of items, and then continue working locally:

  Cars.OrderByDescending(c => c.ReleaseDate).Take(100).ToList()
    .GroupBy(c => c.ManufacturerId).Select(g => g.First()).Take(5)

The downside of this is that you can potentially end up having less than 5 results.

.NET Core 3.1 - Linq - Select records grouped by a unique attribute

1 Answers1