LINQ: grouping, joining and flattening

Summarize data with GroupBy, combine sequences with Join and SelectMany, and meet query syntax.

Beyond filtering and sorting

Real reports ask questions like how much did each customer spend?, which products appear in which orders?, what are all the tags across all posts? LINQ has operators for summarizing, combining and flattening data — the same ideas as SQL's GROUP BY and JOIN, which you'll meet in Backend Fundamentals.

Sample data:

record Order(int Id, string Customer, string Product, int Quantity, decimal UnitPrice);
record Customer(string Name, string City);
 
var orders = new List<Order>
{
    new(1, "Ada", "Pen", 10, 1.5m),
    new(2, "Bob", "Laptop", 1, 999m),
    new(3, "Ada", "Notebook", 3, 4m),
    new(4, "Cy",  "Pen", 2, 1.5m),
};
var customers = new List<Customer> { new("Ada", "London"), new("Bob", "Paris"), new("Cy", "Paris") };

GroupBy: summarize per key

GroupBy splits a sequence into groups sharing a key. Each group has a .Key and is itself a sequence:

var spending = orders
    .GroupBy(o => o.Customer)
    .Select(g => new
    {
        Customer = g.Key,
        Orders = g.Count(),
        Total = g.Sum(o => o.Quantity * o.UnitPrice),
    })
    .OrderByDescending(x => x.Total);
 
foreach (var s in spending)
    Console.WriteLine($"{s.Customer}: {s.Orders} orders, {s.Total:F2}");
// Bob: 1 orders, 999.00
// Ada: 2 orders, 27.00
// Cy: 1 orders, 3.00

ToDictionary and ToLookup

Turn a query into a fast lookup:

Dictionary<string, decimal> totals = orders
    .GroupBy(o => o.Customer)
    .ToDictionary(g => g.Key, g => g.Sum(o => o.Quantity * o.UnitPrice));
 
Console.WriteLine(totals["Ada"]);   // 27.0
 
var byProduct = orders.ToLookup(o => o.Product);   // like a dictionary of groups
foreach (var o in byProduct["Pen"]) Console.WriteLine(o.Customer);   // Ada, Cy

ToDictionary throws on duplicate keys; ToLookup allows many values per key.

Join: combine two sequences

Match elements from two sources by a shared key — like SQL's inner join:

var ordersWithCity = orders.Join(
    customers,
    order => order.Customer,          // key from the first sequence
    customer => customer.Name,        // key from the second
    (order, customer) => $"#{order.Id} {order.Product} → {customer.City}");
// #1 Pen → London, #2 Laptop → Paris, ...

SelectMany: flatten

When each element contains a collection, Select gives you a sequence of collections. SelectMany flattens them into one sequence:

var posts = new[]
{
    new { Title = "LINQ tips", Tags = new[] { "csharp", "linq" } },
    new { Title = "Async basics", Tags = new[] { "csharp", "async" } },
};
 
var allTags = posts.SelectMany(p => p.Tags).Distinct();   // csharp, linq, async

More useful operators

var pairs = names.Zip(scores, (n, s) => $"{n}: {s}");     // combine two sequences pairwise
var batches = items.Chunk(100);                            // split into arrays of 100
var total = numbers.Aggregate((acc, n) => acc * n);        // general fold — here, a product
bool same = list1.SequenceEqual(list2);
var both = a.Intersect(b); var either = a.Union(b); var onlyA = a.Except(b);

Query syntax

LINQ also has a SQL-like query syntax. The compiler translates it into the same method calls:

var bigSpenders =
    from o in orders
    where o.Quantity * o.UnitPrice > 10
    orderby o.Customer
    select new { o.Customer, o.Product };
 
var joined =
    from o in orders
    join c in customers on o.Customer equals c.Name
    group o by c.City into cityGroup
    select new { City = cityGroup.Key, Orders = cityGroup.Count() };

Most C# code uses method syntax, but query syntax can be easier to read for joins and multi-step groupings. Every query-syntax query can be written with methods; not every method (like Count, First or Distinct) has a query keyword.

Tip

In Track 10, the exact same LINQ queries run against a database: Entity Framework Core translates Where, GroupBy and Join into SQL.

Try it

Program.csCtrl + Enter to run

Loading editor…

C# runtime not loaded

Key takeaways

GroupBy creates groups with a Key; aggregate each group with Count, Sum, etc.
ToDictionary / ToLookup turn results into fast lookups.
Join matches two sequences by key; SelectMany flattens nested collections.
Query syntax (from … where … select) compiles to the same method calls.