C#: relational queries with LINQ join and group

Before you start

C# anonymous types and collections.

Join two collections on a key, sort the result, and aggregate it by group, as SQL does.
Predict the sample output and explain the state changes.
Complete the practice task and check the boundary cases.

The concept

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.

Code example

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);
    }
  }
}

Output

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

Example details

Follow the code

Open the walkthrough to inspect the example one step at a time.

Show guided walkthrough

Guided walkthroughReady

1using System;

2using System.Linq;

4class Program {

5 static void Main() {

6 var departments = new[] { new { Id = 1, Title = "Eng" }, new { Id = 2, Title = "Ops" } };

7 var staff = new[] {

8 new { Name = "Ada", Dept = 1, Salary = 120 },

9 new { Name = "Bo", Dept = 2, Salary = 90 },

10 new { Name = "Cy", Dept = 1, Salary = 100 },

11 new { Name = "Di", Dept = 3, Salary = 80 },

12 };

14 var joined = (from e in staff

15 join d in departments on e.Dept equals d.Id

16 orderby e.Name

17 select new { e.Name, d.Title, e.Salary }).ToList();

18 foreach (var row in joined) {

19 Console.WriteLine(row.Name + " " + row.Title + " " + row.Salary);

20 }

22 var totals = joined.GroupBy(r => r.Title)

23 .Select(g => g.Key + ": " + g.Sum(r => r.Salary))

24 .OrderBy(text => text);

25 foreach (var line in totals) {

26 Console.WriteLine(line);

27 }

28 }

29}

Follow the example

Start to inspect the source, values, and output. These prepared steps explain the published sample; grouped loop steps describe multiple iterations.

View the complete dry-run table

Prepared trace of the published program. Some rows group repeated iterations.
What happens	State after step	Output so far
Match each employee to the department with the same id. Di has department 3, which does not exist, so Di is dropped.	`—`	—
Sort the joined rows by name.	`—`	—
Print the three joined rows.	`—`	Ada Eng 120 Bo Ops 90 Cy Eng 100
Group the joined rows by department title.	`—`	Ada Eng 120 Bo Ops 90 Cy Eng 100
Sum the salaries per group: Eng has 120 + 100, Ops has 90.	`—`	Ada Eng 120 Bo Ops 90 Cy Eng 100 Eng: 220 Ops: 90

Behind the scenes

How C# processes this code

C# compiles to code for the .NET runtime, which invokes the program entry point.

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?

Reveal answer and reasoning

Use a group join with DefaultIfEmpty: join ... into matches, then from d in matches.DefaultIfEmpty(), and handle the null department.