C#: relational queries with LINQ join and group

Join two collections on a key, sort the result, and aggregate it by group, as SQL does.

using System;
using System.Linq;

class Program {
  static void Main() {
    var departments = new[] { new { Id = 1, Title = "Eng" }, new { Id = 2, Title = "Ops" } };
    var staff = new[] {
      new { Name = "Ada", Dept = 1, Salary = 120 },
      new { Name = "Bo", Dept = 2, Salary = 90 },
      new { Name = "Cy", Dept = 1, Salary = 100 },
      new { Name = "Di", Dept = 3, Salary = 80 },
    };

    var joined = (from e in staff
                  join d in departments on e.Dept equals d.Id
                  orderby e.Name
                  select new { e.Name, d.Title, e.Salary }).ToList();
    foreach (var row in joined) {
      Console.WriteLine(row.Name + " " + row.Title + " " + row.Salary);
    }

    var totals = joined.GroupBy(r => r.Title)
                       .Select(g => g.Key + ": " + g.Sum(r => r.Salary))
                       .OrderBy(text => text);
    foreach (var line in totals) {
      Console.WriteLine(line);
    }
  }
}

Ada Eng 120
Bo Ops 90
Cy Eng 100
Eng: 220
Ops: 90

Explanation

LINQ (Language Integrated Query) brings relational-style queries into C#. Its query syntax mirrors SQL: from names a row, join ... on ... equals ... matches rows by key, orderby sorts, and select shapes the result. The same operations are available as methods such as Join, GroupBy, and OrderBy.

The join here is an inner join: the employee whose department does not exist is dropped. GroupBy collects rows with the same key, much like SQL GROUP BY, and Sum is the aggregate. LINQ to Objects works on in-memory data. Entity Framework translates similar queries into SQL for a real database.

—

—

Ada Eng 120
Bo Ops 90
Cy Eng 100

Ada Eng 120
Bo Ops 90
Cy Eng 100

Ada Eng 120
Bo Ops 90
Cy Eng 100
Eng: 220
Ops: 90

Cost and common mistakes

Join builds a lookup on the inner collection, so it takes about O(n + m) time; the sort adds O(r log r) for r result rows.

• LINQ queries are lazy: they run when enumerated. Call ToList() when you need the result stored once.
• Enumerating a lazy query several times repeats the work, and it can give different results if the source changes.

Try it yourself

How would you keep Di with a missing department, like a SQL LEFT JOIN?

Further reading

• C# documentation (external)
• .NET API browser (external)
• Database System Concepts (Silberschatz, Korth, Sudarshan) (external)