How to make a GroupBy call on the server side EF Core 3

Question

Way too many questions were asked on this topic and of course I've tried to clarify my misunderstanding, googling existing answers, but it seems like I need a bit more details.

As it turned out for me, before EF Core 2 GroupBy was executing on the client side, want you that or not. But in EF 3 Core that behavior had been changed and now you have to execute it on the client explicitly, by calling ToList or something else and then using IEnumerable's GroupBy. So with this approach we always know the risks of probable big memory allocations.

https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.x/breaking-changes#linq-queries-are-no-longer-evaluated-on-the-client

But I want to execute GroupBy on the server side. That's the thing. So what do I have to do? Create a procedure? Also I'm interested why wasn't it executing on the server side at the very beginning?

My case is very simple: I want to get records of some groups with the maximum column value. Like that guy on the screenshot tried to do that, but on the server level.

Answer 1

The closest thing I came to is:

var query = _dbContext.ReportVersions
    .Select(version => version.VersionGroupId).Distinct()
    .SelectMany(key => _dbContext.Reports.Where(report => report.ReportVersion.VersionGroupId == key)
        .OrderByDescending(report => report.ReportVersion.CreatedAt)
        .Select(report => new
        {
            Id = report.Id,
           // etc
        })
        .Take(1))
    .ToListAsync(cancellationToken);

It reminds group by, as you can see firstly I'm looking for unique values (VersionGroupId), after that I'm looking for reports, which have that VersionGroupId, and order them. Now I've got all records in the "group" and I'm taking top one of each.

It's done with a subquery, but with this approach you don't load unwanted entities in memory.