How does GroupBy translate to SQL in EF Core?

Most GroupBy patterns translate well, but a few do NOT and silently load everything into memory.

Translates cleanly:

var counts = await db.Orders
    .GroupBy(o => o.CustomerId)
    .Select(g => new { CustomerId = g.Key, Total = g.Sum(o => o.Amount), Count = g.Count() })
    .ToListAsync();

SQL: SELECT customer_id, SUM(amount), COUNT(*) FROM orders GROUP BY customer_id.

Does NOT translate cleanly:

var byCustomer = await db.Orders
    .GroupBy(o => o.CustomerId)
    .ToListAsync();   // ⚠ pre-EF8: client-side eval; modern versions throw or warn

SQL doesn't return groups — it returns aggregated rows. EF Core 8+ throws when it can't translate; older versions silently downloaded the whole table.

The workaround when you really need the groups:

var rows = await db.Orders.ToListAsync();
var byCustomer = rows.GroupBy(o => o.CustomerId).ToList();   // in-memory grouping

Acceptable for small data sets, terrible for large ones.

Avoid: GroupBy + .OrderBy(g => g.First().Date) — the inner First is per-group, can't always be pushed down.

Rule: end every database GroupBy with a .Select(g => new { ... }) that contains only g.Key and aggregation methods (Sum, Count, Min, Max, Average). Anything else, double-check the generated SQL.