CODEWITHSUNDEEP
c#linqgroup-bysubqueryef-core-5.0
Pavel
Pavel

Reputation: 57

GroupBy in subquery with MAX() in C#

I Have this code. It works fine but when I have two same maximal values it appear 2 times. So I need to use OrderBy. But I dont know how. Thanks for any help.

IQueryable<PerformanceRealization> pr = _context.PerformanceRealization
    .Where(u => u.Deadline == _context.PerformanceRealization
        .Where(x => x.GroupRealizationId == u.GroupRealizationId)
        .Max(x => x.Deadline)
    )
    .Select(u => u);

Here is the SQL code with GROUP BY

SELECT PR.GroupRealizationId
FROM Stores.PerformanceRealization PR
LEFT JOIN Stores.GroupRealization ON Stores.GroupRealization.Id = PR.GroupRealizationId
WHERE PR.Deadline = (SELECT MAX(Deadline) 
                     FROM Stores.PerformanceRealization PR2 
                     WHERE PR.GroupRealizationId = PR2.GroupRealizationId)
GROUP BY PR.GroupRealizationId

Upvotes: 1

Views: 245

Answers (1)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112259

You can select the first object from the group

IQueryable<PerformanceRealization> pr2 = pr
    .GroupBy(x => x.GroupRealizationId)
    .Select(g => g.First());

If you need a specific object from the group, then you can order by another column

IQueryable<PerformanceRealization> pr2 = pr
    .GroupBy(x => x.GroupRealizationId)
    .Select(g => g.OrderBy(x => x.SomeColumn).First());

for SomeColumn having the smallest value. For the greatest value, use OderByDescending instead.

Of course, you can integrate this approach into the first query:

IQueryable<PerformanceRealization> pr = _context.PerformanceRealization
    .Where(u => u.Deadline == _context.PerformanceRealization
        .Where(x => x.GroupRealizationId == u.GroupRealizationId)
        .Max(x => x.Deadline)
    )
    .GroupBy(x => x.GroupRealizationId)
    .Select(g => g.OrderBy(x => x.SomeColumn).First());

Note, you don't need to have a Select at the end like .Select(u => u). Since it has no effect, you can just drop it.

If your EF Core version cannot handle it (as revealed in a comment), then transition to LINQ-to-Objects with AsEnumerable(), but do the filtering in EF Core to minimize the number of records sent to the front-end:

IQueryable<PerformanceRealization> pr = _context.PerformanceRealization
    .Where(u => u.Deadline == _context.PerformanceRealization
        .Where(x => x.GroupRealizationId == u.GroupRealizationId)
        .Max(x => x.Deadline)
    )
    .AsEnumerable() // <===== transition from LINQ-to-EF-Core to LINQ-to-Objects
    .GroupBy(x => x.GroupRealizationId)
    .Select(g => g.OrderBy(x => x.SomeColumn).First());

Upvotes: 1

Related Questions