Grouping and joining

GroupBy

GroupBy takes a key selector and produces one IGrouping<TKey, TItem> per distinct key, in the order the keys first appeared. Each group is itself a sequence, so the aggregate methods work inside the projection: this is the shape of every grouped report.

var sales = new[] { ("EU", "book", 12.50m), ("US", "pen", 3.00m), ("EU", "pen", 1.50m), ("APAC", "book", 9.00m), ("EU", "book", 7.00m), }; var byRegion = sales .GroupBy(s => s.Item1) .Select(g => new { Region = g.Key, Units = g.Count(), Total = g.Sum(s => s.Item3) }) .OrderByDescending(x => x.Total); foreach (var region in byRegion) { Console.WriteLine($"{region.Region,-5}{region.Units,3}{region.Total,8:F2}"); } Console.WriteLine($"total {byRegion.Sum(r => r.Total):F2}");

dotnet run

EU 3 21.00
APAC 1 9.00
US 1 3.00
total 33.00

Three sales in the EU, one each in APAC and the US, and the totals agree with the five amounts. The sum at the end adds the three group totals rather than the five sales, and it gives the same 33.00, which is a useful check on a report: a total of the totals must equal the total of the source.

Join two sequences

Join matches an item of the first sequence with the items of the second that have an equal key, and produces one row per pair. In query syntax the key is written twice, once on each side of equals: join order in orders on customer.Id equals order.CustomerId. A row whose key matches nothing on the other side simply does not appear.

var customers = new[] { new Customer(1, "Anna"), new Customer(2, "Borys"), new Customer(3, "Clara"), }; var orders = new[] { new Order(1, 1, 250m), new Order(2, 1, 40m), new Order(3, 3, 15m), new Order(4, 1, 8m), }; var report = from customer in customers join order in orders on customer.Id equals order.CustomerId orderby customer.Name, order.Amount descending select new { customer.Name, order.Amount }; foreach (var row in report) { Console.WriteLine($"{row.Name,-6}{row.Amount,8:F2}"); } Console.WriteLine($"rows: {report.Count()}"); record Customer(int Id, string Name); record Order(int Id, int CustomerId, decimal Amount);

dotnet run

Anna 250.00
Anna 40.00
Anna 8.00
Clara 15.00
rows: 4

Anna has three orders and Clara one, so four rows: Borys has no order and does not appear, which is the inner-join rule. The two records are declared below the statements, and the query reads them like any other type.

GroupJoin and an empty group

GroupJoin pairs every item of the first sequence with a sequence of its matches, and that sequence is empty rather than missing when there are none. It is how a left join is written in LINQ: every customer gets a row, and a customer without orders gets zero.

var customers = new[] { new Customer(1, "Anna"), new Customer(2, "Borys"), new Customer(3, "Clara"), }; var orders = new[] { new Order(1, 1, 250m), new Order(2, 1, 40m), new Order(3, 3, 15m), }; var withTotals = customers .GroupJoin( orders, customer => customer.Id, order => order.CustomerId, (customer, theirOrders) => new { customer.Name, Count = theirOrders.Count(), Total = theirOrders.Sum(o => o.Amount), }) .OrderBy(x => x.Name); foreach (var row in withTotals) { Console.WriteLine($"{row.Name,-6}{row.Count,3} orders{row.Total,10:F2}"); } Console.WriteLine($"customers: {withTotals.Count()}"); record Customer(int Id, string Name); record Order(int Id, int CustomerId, decimal Amount);

dotnet run

Anna 2 orders 290.00
Borys 0 orders 0.00
Clara 1 orders 15.00
customers: 3

The Sum of an empty group is zero and its Count is zero, so a left join needs no null check on the amount. The listing carries its own three customers and three orders and its own two records, because a program on this page must build from the first line to the last without a second file.

ToLookup

ToLookup groups once and returns the result, a ILookup<TKey, TItem> that can be indexed by key like a dictionary. The difference from Dictionary is the behaviour of a missing key: a lookup returns an empty sequence, so a loop over it runs zero times and no exception is thrown.

var orders = new[] { new Order("EU", 12.50m), new Order("US", 40m), new Order("EU", 8.25m), new Order("APAC", 5m), new Order("EU", 19.90m), }; var byRegion = orders.ToLookup(o => o.Region); foreach (var group in byRegion.OrderBy(g => g.Key)) { Console.WriteLine($"{group.Key,-5}{group.Count(),3}{group.Sum(o => o.Amount),10:F2}"); } Console.WriteLine($"missing region total: {byRegion["LATAM"].Sum(o => o.Amount):F2}"); Console.WriteLine($"regions: {byRegion.Count}"); record Order(string Region, decimal Amount);

dotnet run

APAC 1 5.00
EU 3 40.65
US 1 40.00
missing region total: 0.00
regions: 3

The lookup is built once and can be read many times, so a report that asks for the same region in several places pays for the grouping once. Its Count is a property, the number of keys, and the three regions printed are the groups it holds.

Exercises

  1. Group the sales by product instead of by region and print the three groups.
  2. Add a customer with no orders to the Join program and explain why the row count does not change.
  3. Rewrite the GroupJoin query as a query expression with join ... into.
  4. Look up a region that exists in the ToLookup program and print one item of it by index.

Questions

A new set of questions in a new order every time. Choose one answer for each, then check.

  1. What does GroupBy produce?

    Answer

    One group per distinct key, each a sequence of the matching items

    Each result element is an IGrouping<TKey, TItem>: a Key and the items that share it.

  2. What happens to a row of the first sequence with no match in a Join?

    Answer

    It does not appear in the result

    Join is an inner join: only pairs with equal keys produce a row.

  3. Which operator gives every item of the first sequence a row, even with no match?

    Answer

    GroupJoin

    GroupJoin pairs each item with its (possibly empty) match sequence, which is a left join.

  4. What does this program print? var a = new[] { 1, 2, 3, 4 }; var g = a.GroupBy(x => x % 2); foreach (var group in g) Console.WriteLine($"{group.Key}:{group.Count()}");

    Answer

    1:2 and then 0:2

    The keys appear in the order they occur in the source: 1 comes before 0, and each group has two items.

  5. What does a lookup return for a key that is not there?

    Answer

    An empty sequence

    ILookup is the reason a lookup needs no null check: a missing key has an empty group.

  6. What is the Sum of an empty group of decimals?

    Answer

    0

    Sum over an empty sequence of a numeric type returns zero, not an error.

  7. How is a left join written with GroupJoin in query syntax?

    Answer

    join ... into a group, then from the group with DefaultIfEmpty

    The into continuation plus a from over the group with DefaultIfEmpty produces the unmatched rows.

  8. Which method turns a sequence into a lookup?

    Answer

    ToLookup

    ToLookup runs the grouping now and returns an ILookup that can be indexed by key.

  9. What does this program print? var w = new[] { "ab", "cd", "ae" }; var g = w.GroupBy(s => s[0]); Console.WriteLine(g.Count());

    Answer

    2

    Two distinct first letters, a and c, make two groups.

  10. Why does a grouped report sum the group totals as a check?

    Answer

    The total of the totals must equal the total of the source

    A mismatch means an item was counted twice or dropped, which a reader can see at a glance.

The code on this page was built with the .NET SDK 10.0.303 and run on Alpine Linux 3.24.3 (x86_64) in a Docker container.

Article author: Arthur Isaev