Skip to content
Tutorials Center
My learning

Topics

Lesson 22 of 36 ยท Collections and LINQ

LINQ Grouping, Joins and Lazy Evaluation

Master advanced LINQ in VB.NET - Group By, Join, Group Join, Distinct, Aggregate, ToLookup, SelectMany and deferred execution pitfalls, with runnable C# stand-ins using the same operators.

Once you can filter and sort, the next questions are relational ones: "how many orders per customer?", "which customer placed this order?", "what is every tag across all posts?". LINQ answers them with Group By, Join, Group Join and SelectMany. You also need to understand deferred execution, because it explains most surprising LINQ behavior.

VB.NET cannot be run on this site; VB code is read-only and the runnable blocks are C# stand-ins calling the same System.Linq operators (GroupBy, Join, GroupJoin, SelectMany).

Group By

VB
Dim byDept = From e In employees
             Group e By e.Dept Into Members = Group, Total = Sum(e.Salary), N = Count()
             Select Dept, N, Total

Group By splits a sequence into groups sharing a key. Into names what you compute per group: the members themselves (Group) and aggregates such as Count() and Sum().

C# Group By with aggregates

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Join

An inner join pairs items from two sequences whose keys match; items without a partner disappear.

VB
Dim rows = From o In orders
           Join c In customers On o.CustomerId Equals c.Id
           Select c.Name, o.Total

Note VB's Equals keyword: the left key must come from the left sequence and the right key from the right one.

C# Inner join of orders and customers

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Group Join: a left join in LINQ

To keep customers with no orders, use Group Join, which yields one result per left item together with its (possibly empty) group of matches.

VB
Dim report = From c In customers
             Group Join o In orders On c.Id Equals o.CustomerId Into Mine = Group
             Select c.Name, Count = Mine.Count(), Sum = Mine.Sum(Function(x) x.Total)
C# Group Join keeps unmatched items

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

SelectMany: flatten nested sequences

SelectMany (VB: a second From clause) turns a sequence of collections into one flat sequence.

VB
Dim allTags = From p In posts
              From t In p.Tags
              Select t
Dim unique = allTags.Distinct()

ToLookup and ToDictionary

ToDictionary(keySelector) builds a one-value-per-key map and throws on duplicate keys. ToLookup(keySelector) allows several values per key and returns an empty group for unknown keys, which makes it a handy read-only "multi-dictionary".

C# SelectMany, Distinct and ToLookup

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Deferred execution and its traps

LINQ builds a recipe; it cooks only when you enumerate. Consequences:

  1. The query sees later changes. If you add to the source after defining the query but before enumerating, the new item is included.
  2. Every enumeration repeats the work. Enumerating a query twice runs it twice (and re-runs any side effects or database calls).
  3. Closures capture variables, not values. A lambda uses the variable's value at enumeration time.

Cure: call ToList()/ToArray() once when you need a stable snapshot.

C# Seeing deferred execution

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which operation keeps customers who have no orders?

    Group Join returns each left item with its possibly empty group of matches.
  2. 2. What does SelectMany do?

    It projects each element to a collection and concatenates the results.
  3. 3. A query is defined, then the source list grows, then the query is enumerated. Does it include the new item?

    The query runs at enumeration time and sees the current source.
  4. 4. How do you take a stable snapshot of a query's result?

    ToList/ToArray execute the query immediately and store the results.

Summary

  • Group By ... Into computes per-group aggregates; Join is an inner join; Group Join behaves like a left join.
  • SelectMany flattens; ToLookup is a multi-value dictionary.
  • LINQ is deferred: it runs on enumeration, every time. Materialize with ToList for a snapshot.

Next: Windows Forms Overview