LINQ GroupBy: 모두가 잘못 사용하는 운영자

LINQ GroupBy: The Operator Everyone Uses Wrong

GroupBy in LINQ looks like SQL GROUP BY. It isn’t. At least, not in the way you’d expect.

When you grasp the difference, you stop fighting the operator and start wielding it.

The SQL Mental Model (That Misleads You)

In SQL, GROUP BY collapses rows into aggregates:

SELECT CategoryId, COUNT(*) as Count, AVG(Price) as AvgPrice
FROM Products
GROUP BY CategoryId

Enter fullscreen mode Exit fullscreen mode

Result: one row per category with aggregate values. Clean.

So you write this in LINQ:

var groups = dbContext.Products
    .GroupBy(p => p.CategoryId)
    .ToList();

Enter fullscreen mode Exit fullscreen mode

And you expect… what? Aggregates? No. You get a list of IGrouping<int, Product> objects. Each grouping has a Key (the CategoryId) and contains all the products in that category.

foreach (var group in groups)
{
    Console.WriteLine($"Category {group.Key}:");
    foreach (var product in group)  // Iterate individual products
    {
        Console.WriteLine($"  - {product.Name}");
    }
}

Enter fullscreen mode Exit fullscreen mode

LINQ’s GroupBy is more powerful than SQL’s — it doesn’t force you to aggregate. But that power confuses SQL-trained developers.

The Aggregation Pattern

To get SQL-like grouped aggregates, you combine GroupBy with Select:

var categoryStats = dbContext.Products
    .GroupBy(p => p.CategoryId)
    .Select(g => new 
    {
        CategoryId = g.Key,
        Count = g.Count(),
        AvgPrice = g.Average(p => p.Price),
        MaxPrice = g.Max(p => p.Price)
    })
    .ToList();

Enter fullscreen mode Exit fullscreen mode

Now you get one row per category with computed values. This is what translates cleanly to SQL.

The Database vs In-Memory Split

Here’s where it gets tricky. Not all GroupBy operations translate to SQL:

// This works in SQL
var simple = dbContext.Products
    .GroupBy(p => p.CategoryId)
    .Select(g => new { g.Key, Count = g.Count() })
    .ToList();

// This might NOT work in SQL (depending on EF version)
var complex = dbContext.Products
    .GroupBy(p => p.CategoryId)
    .Select(g => new 
    { 
        g.Key, 
        Products = g.ToList()  // Trying to materialize full objects in group
    })
    .ToList();

Enter fullscreen mode Exit fullscreen mode

EF Core has historically struggled with “group then expand” patterns. The workaround is to group in memory:

var products = await dbContext.Products.ToListAsync();
var grouped = products
    .GroupBy(p => p.CategoryId)
    .Select(g => new { CategoryId = g.Key, Products = g.ToList() })
    .ToList();

Enter fullscreen mode Exit fullscreen mode

Fun fact: SQL’s GROUP BY was standardized in SQL-86 (yes, 1986). But the concept of grouping as collections — where each group is a container you can enumerate — comes from functional programming. LINQ merged both worlds, which is why it feels different from either pure SQL or pure functional code.

Grouping by Multiple Keys

Group by a composite key using anonymous types:

var grouped = dbContext.Products
    .GroupBy(p => new { p.CategoryId, p.SupplierId })
    .Select(g => new 
    {
        g.Key.CategoryId,
        g.Key.SupplierId,
        Count = g.Count()
    })
    .ToList();

Enter fullscreen mode Exit fullscreen mode

The anonymous type becomes your grouping key. All properties must match for items to land in the same group.

GroupBy with Element Selector

You can transform elements during grouping:

// Group products, but only keep their names
var productNamesByCategory = products
    .GroupBy(
        p => p.CategoryId,           // Key selector
        p => p.Name                   // Element selector
    );

foreach (var group in productNamesByCategory)
{
    Console.WriteLine($"Category {group.Key}: {string.Join(", ", group)}");
}

Enter fullscreen mode Exit fullscreen mode

The Lookup Alternative

When you’ll access groups multiple times by key, ToLookup is more efficient:

var lookup = products.ToLookup(p => p.CategoryId);

// Direct key access — O(1) instead of iterating
var electronics = lookup[5];
var clothing = lookup[7];

Enter fullscreen mode Exit fullscreen mode

ToLookup is like GroupBy but immediately materialized into a dictionary-like structure. Unlike Dictionary, it returns an empty collection for missing keys instead of throwing.

The “I Want This But Grouped” Pattern

Common scenario: you have a flat list, need it grouped for display:

var ordersByMonth = orders
    .GroupBy(o => new { o.OrderDate.Year, o.OrderDate.Month })
    .OrderByDescending(g => g.Key.Year)
    .ThenByDescending(g => g.Key.Month)
    .Select(g => new 
    {
        Period = $"{g.Key.Year}-{g.Key.Month:D2}",
        Orders = g.OrderBy(o => o.OrderDate).ToList(),
        Total = g.Sum(o => o.Amount)
    })
    .ToList();

Enter fullscreen mode Exit fullscreen mode

The Rule

1. For aggregates only? GroupBy + Select with aggregation functions — translates to SQL
2. Need full objects per group? Materialize first, then GroupBy in memory
3. Multiple key access? Use ToLookup instead of GroupBy
4. Composite keys? Anonymous types with all grouping columns

Next time, we’ll dive into Join operations — the LINQ equivalent of SQL JOINs, and why they’re often unnecessary when you have proper navigation properties. Hope to see you!