LINQ Grouping and Joins

Beyond filtering and sorting, LINQ can group elements by a key, count and total within each group, combine data from several collections, and flatten nested ones. These operations turn raw lists into reports — sales per region, orders per customer, products with their suppliers.

Sample data

record Customer(int Id, string Name, string City);
record Order(int Id, int CustomerId, string Product, decimal Amount);

List<Customer> customers =
[
    new(1, "Ana", "Lisbon"), new(2, "Ben", "Leeds"), new(3, "Cleo", "Lisbon"), new(4, "Dev", "Pune"),
];
List<Order> orders =
[
    new(101, 1, "Keyboard", 49.99m), new(102, 2, "Monitor", 219.00m), new(103, 1, "Mouse", 19.50m),
    new(104, 3, "Desk", 340.00m), new(105, 1, "Webcam", 64.00m), new(106, 2, "Cable", 9.99m),
];

Each example below uses these two lists (they're repeated in each program so you can copy and run it on its own).

GroupBy

GroupBy splits a sequence into groups that share a key. Each group has a Key and is itself a sequence of its elements:

List<Customer> customers = [new(1, "Ana", "Lisbon"), new(2, "Ben", "Leeds"), new(3, "Cleo", "Lisbon"), new(4, "Dev", "Pune")];

foreach (var group in customers.GroupBy(c => c.City))
{
    Console.WriteLine($"{group.Key} ({group.Count()}): {string.Join(", ", group.Select(c => c.Name))}");
}

record Customer(int Id, string Name, string City);

Output

Lisbon (2): Ana, Cleo
Leeds (1): Ben
Pune (1): Dev

Summarising groups

Usually you'll project each group into a summary with Select:

List<Order> orders =
[
    new(101, 1, "Keyboard", 49.99m), new(102, 2, "Monitor", 219.00m), new(103, 1, "Mouse", 19.50m),
    new(104, 3, "Desk", 340.00m), new(105, 1, "Webcam", 64.00m), new(106, 2, "Cable", 9.99m),
];

var perCustomer = orders
    .GroupBy(o => o.CustomerId)
    .Select(g => new { CustomerId = g.Key, Orders = g.Count(), Total = g.Sum(o => o.Amount), Largest = g.Max(o => o.Amount) })
    .OrderByDescending(x => x.Total);

foreach (var row in perCustomer)
    Console.WriteLine($"Customer {row.CustomerId}: {row.Orders} orders, {row.Total,7:C}, largest {row.Largest:C}");

record Order(int Id, int CustomerId, string Product, decimal Amount);

Output

Customer 3: 1 orders, $340.00, largest $340.00
Customer 2: 2 orders, $228.99, largest $219.00
Customer 1: 3 orders, $133.49, largest $64.00

Inside the Select, g is one group: g.Key is the customer ID it was grouped by, and aggregate methods like Count, Sum and Max run over just that group's orders. The result is one summary row per customer, which can then be sorted like any other sequence.

CountBy and AggregateBy

For the very common "count per key" and "total per key" jobs, .NET 9 added shortcuts that skip building the groups:

string[] words = "the quick brown fox jumps over the lazy dog the end".Split(' ');

foreach (var (word, count) in words.CountBy(w => w).Where(p => p.Value > 1))
    Console.WriteLine($"{word}: {count}");

(string Region, decimal Sales)[] sales = [("North", 120m), ("South", 80m), ("North", 45m), ("South", 20m), ("East", 60m)];
foreach (var (region, total) in sales.AggregateBy(s => s.Region, 0m, (sum, s) => sum + s.Sales))
    Console.WriteLine($"{region}: {total}");

Output

the: 3
North: 165
South: 100
East: 60

Join

Join matches elements from two sequences by a key — like an SQL inner join. Elements without a match on the other side are left out:

List<Customer> customers = [new(1, "Ana", "Lisbon"), new(2, "Ben", "Leeds"), new(3, "Cleo", "Lisbon"), new(4, "Dev", "Pune")];
List<Order> orders =
[
    new(101, 1, "Keyboard", 49.99m), new(102, 2, "Monitor", 219.00m), new(103, 1, "Mouse", 19.50m),
    new(104, 3, "Desk", 340.00m), new(105, 1, "Webcam", 64.00m), new(106, 2, "Cable", 9.99m),
];

var lines = orders.Join(customers,
    o => o.CustomerId,          // key from orders
    c => c.Id,                  // key from customers
    (o, c) => $"#{o.Id} {c.Name,-5} {o.Product,-9}{o.Amount,8:C}");

foreach (string line in lines)
    Console.WriteLine(line);

record Customer(int Id, string Name, string City);
record Order(int Id, int CustomerId, string Product, decimal Amount);

Output

#101 Ana   Keyboard   $49.99
#102 Ben   Monitor   $219.00
#103 Ana   Mouse      $19.50
#104 Cleo  Desk      $340.00
#105 Ana   Webcam     $64.00
#106 Ben   Cable       $9.99

Dev has no orders, so Dev doesn't appear.

Left joins and GroupJoin

To keep every customer, including those with no orders, use a left join. .NET 10 adds a LeftJoin method for exactly this; for unmatched elements the inner value is null. GroupJoin instead pairs each customer with the collection of their orders:

List<Customer> customers = [new(1, "Ana", "Lisbon"), new(2, "Ben", "Leeds"), new(4, "Dev", "Pune")];
List<Order> orders = [new(101, 1, "Keyboard", 49.99m), new(103, 1, "Mouse", 19.50m), new(106, 2, "Cable", 9.99m)];

var left = customers.LeftJoin(orders, c => c.Id, o => o.CustomerId,
    (c, o) => $"{c.Name}: {o?.Product ?? "(no orders)"}");
Console.WriteLine(string.Join("; ", left));

var grouped = customers.GroupJoin(orders, c => c.Id, o => o.CustomerId,
    (c, os) => new { c.Name, Count = os.Count(), Total = os.Sum(o => o.Amount) });
foreach (var g in grouped)
    Console.WriteLine($"{g.Name,-4} {g.Count} orders {g.Total,7:C}");

record Customer(int Id, string Name, string City);
record Order(int Id, int CustomerId, string Product, decimal Amount);

Output

Ana: Keyboard; Ana: Mouse; Ben: Cable; Dev: (no orders)
Ana  2 orders  $69.49
Ben  1 orders   $9.99
Dev  0 orders   $0.00

SelectMany: flattening

SelectMany maps each element to a sequence and joins all those sequences into one — perfect for nested collections:

var teams = new Dictionary<string, string[]>
{
    ["Red"] = ["Ana", "Ben"],
    ["Blue"] = ["Cleo", "Dev", "Eli"],
};

var everyone = teams.SelectMany(t => t.Value);
Console.WriteLine(string.Join(", ", everyone));

var labelled = teams.SelectMany(t => t.Value, (team, player) => $"{player} ({team.Key})");
Console.WriteLine(string.Join(", ", labelled));

string[] sentences = ["LINQ is neat", "so is C#"];
Console.WriteLine(sentences.SelectMany(s => s.Split(' ')).Count());

Output

Ana, Ben, Cleo, Dev, Eli
Ana (Red), Ben (Red), Cleo (Blue), Dev (Blue), Eli (Blue)
6

Zip, Chunk and Aggregate

string[] days = ["Mon", "Tue", "Wed", "Thu"];
int[] steps = [8200, 10400, 6100, 12000];

foreach (var (day, count) in days.Zip(steps))
    Console.Write($"{day}:{count} ");
Console.WriteLine();

foreach (int[] batch in Enumerable.Range(1, 7).Chunk(3))
    Console.WriteLine($"[{string.Join(", ", batch)}]");

int product = Enumerable.Range(1, 5).Aggregate((acc, n) => acc * n);
string csv = days.Aggregate((acc, d) => acc + "," + d);
Console.WriteLine($"{product} {csv}");

Output

Mon:8200 Tue:10400 Wed:6100 Thu:12000
[1, 2, 3]
[4, 5, 6]
[7]
120 Mon,Tue,Wed,Thu

Query syntax for groups and joins

Query syntax shines for multi-step queries. let stores an intermediate value; group … by … into continues the query with each group:

List<Customer> customers = [new(1, "Ana", "Lisbon"), new(2, "Ben", "Leeds"), new(3, "Cleo", "Lisbon")];
List<Order> orders =
[
    new(101, 1, "Keyboard", 49.99m), new(102, 2, "Monitor", 219.00m), new(103, 1, "Mouse", 19.50m),
    new(104, 3, "Desk", 340.00m), new(105, 1, "Webcam", 64.00m),
];

var byCity =
    from o in orders
    join c in customers on o.CustomerId equals c.Id
    let withTax = o.Amount * 1.2m
    group withTax by c.City into g
    orderby g.Key
    select new { City = g.Key, Total = g.Sum() };

foreach (var row in byCity)
    Console.WriteLine($"{row.City}: {row.Total:C}");

record Customer(int Id, string Name, string City);
record Order(int Id, int CustomerId, string Product, decimal Amount);

Output

Leeds: $262.80
Lisbon: $568.19

Try it yourself

Group the words by their first letter, then print each letter with its words sorted alphabetically.

Output

A: apple, avocado
B: banana, blueberry
P: peach, pear, plum

PreviousLINQ Basics NextIterators & yield