Group join with group join

1. Introduction

Imagine a classic task: you have two collections that are logically related. For example, a list of product categories and a list of the products themselves. You need to get all products for each category. Or, say, you have a list of company departments and a list of employees, and you want to show all employees for each department.

In SQL, this is called a "group join" (GROUP JOIN or, more precisely, LEFT OUTER JOIN with grouping). In LINQ, there's a special operator for this – GroupJoin. It's kind of a mix between a regular join (Join), where each left record matches exactly one right, and grouping by key. GroupJoin links each element of one collection with all related elements from the second collection as a collection.

Analogy

If a regular Join is like matching “dad and son” pairs by last name, then GroupJoin is like building a tree: for each dad, attach a list of all his kids.

Diagram

           Categories                   Products
        +--------------+         +---------------------+
        | Id | Name    |         | Name      | CatId   |
        +----+---------+         +-----------+---------+
        | 1  | Bread   |   --->  | Baton     | 1       |
        | 2  | Drinks  |         | Sausage   | 3       |
        | 3  | Meat    |         | Pepsi     | 2       |
        |    |         |         | Tea       | 2       |
        +----+---------+         +-----------+---------+

After GroupJoin:

• Bread — [Baton]
• Drinks — [Pepsi, Tea]
• Meat — [Sausage]

2. Method signature and basic concepts

Extension method

public static IEnumerable<TResult> GroupJoin<TOuter, TInner, TKey, TResult>(
    this IEnumerable<TOuter> outer,                   // "Outer" collection (for example, categories)
    IEnumerable<TInner> inner,                        // "Inner" collection (for example, products)
    Func<TOuter, TKey> outerKeySelector,              // How to get the key from the outer element
    Func<TInner, TKey> innerKeySelector,              // How to get the key from the inner element
    Func<TOuter, IEnumerable<TInner>, TResult> resultSelector // Factory for creating the result object/record
)

• outer: the collection you loop through and to which you attach elements (like categories).
• inner: the collection from which you pick the attached elements (like products).
• outerKeySelector: lambda that returns the key for the "left" element.
• innerKeySelector: lambda that returns the key for the "right" element.
• resultSelector: function that lets you define what the result looks like for each pair (left+group of rights).

3. Practical example: categories and products

Let's say we have these models:

public class Category
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class Product
{
    public string Name { get; set; }
    public int CategoryId { get; set; }
}

Example collections:

var categories = new List<Category>
{
    new Category { Id = 1, Name = "Bread" },
    new Category { Id = 2, Name = "Drinks" },
    new Category { Id = 3, Name = "Meat" }
};

var products = new List<Product>
{
    new Product { Name = "Baton", CategoryId = 1 },
    new Product { Name = "Pepsi", CategoryId = 2 },
    new Product { Name = "Tea", CategoryId = 2 },
    new Product { Name = "Sausage", CategoryId = 3 }
};

Using GroupJoin (Method Syntax)

var groupJoin = categories.GroupJoin(
    products,
    category => category.Id,                    // category key
    product => product.CategoryId,              // product key
    (category, prods) => new                   // build result on the fly
    {
        CategoryName = category.Name,
        Products = prods.Select(p => p.Name).ToList() // list of product names for this category
    }
);

How to loop through the result:

foreach (var group in groupJoin)
{
    Console.WriteLine($"Category: {group.CategoryName}");
    foreach (var product in group.Products)
    {
        Console.WriteLine($"  - {product}");
    }
}

Output:

Category: Bread
  - Baton
Category: Drinks
  - Pepsi
  - Tea
Category: Meat
  - Sausage

4. GroupJoin: Query Syntax

LINQ supports a syntax similar to SQL. For group join, you use the join ... into ... keyword, and this query works almost the same as the example above.

var groupJoin2 = from c in categories
                 join p in products on c.Id equals p.CategoryId into prodGroup
                 select new
                 {
                     CategoryName = c.Name,
                     Products = prodGroup.Select(p => p.Name).ToList()
                 };

This is a lot like an SQL query with LEFT OUTER JOIN ... GROUP BY.

Visual diagram: how GroupJoin works

[Category]         [Product]           Grouping (GroupJoin)

  Bread   -------->   Baton         =>   Bread:    [Baton]
  Drinks  -------->   Pepsi         =>   Drinks:   [Pepsi, Tea]
  Drinks  -------->   Tea
  Meat    -------->   Sausage       =>   Meat:     [Sausage]

Each category gets its own “pocket” (IEnumerable<Product>), where all products of that category go.

5. Features and gotchas

GroupJoin vs. regular Join

The difference between a regular Join and GroupJoin is in the number of results. Join returns one pair for each match, while GroupJoin returns one element for each element of the outer collection, and inside it is a collection of all matching elements.

If in our scheme there’s a category with no products, with GroupJoin it’ll still show up, just its product collection will be empty. This is just like LEFT OUTER JOIN in SQL (left outer join).

Here’s an example with a category that has no products:

categories.Add(new Category { Id = 4, Name = "Cheeses" });

var groupJoin3 = categories.GroupJoin(
    products,
    c => c.Id,
    p => p.CategoryId,
    (c, prods) => new
    {
        CategoryName = c.Name,
        Products = prods.Select(p => p.Name).ToList()
    });

foreach (var group in groupJoin3)
{
    Console.WriteLine($"Category: {group.CategoryName}");
    if (group.Products.Count == 0)
        Console.WriteLine("  (No products)");
    else
        foreach (var product in group.Products)
            Console.WriteLine($"  - {product}");
}

Category: Bread
  - Baton
Category: Drinks
  - Pepsi
  - Tea
Category: Meat
  - Sausage
Category: Cheeses
  (No products)

This kind of scenario is super common in business apps: you need to show all categories (or groups), even if some of them have no items.

Implementing via GroupBy? Nope!

A lot of people get confused and try to “fake” GroupJoin using double GroupBy. Don’t do it — GroupJoin is made for exactly this, and does the “left join” natively.

6. Using with real data

On top of previous lectures, let’s add to our learning app the ability to output a report: “For each category — list its products.” This is a super common task in online stores, CRMs, accounting or reporting systems.

Let’s add code to our demo app:

// Let's say we already have Category and Product classes and collections created

Console.WriteLine("CATEGORY AND PRODUCT REPORT:");
var categoryReport = categories.GroupJoin(
    products,
    cat => cat.Id,
    prod => prod.CategoryId,
    (cat, prods) => new
    {
        cat.Name,
        ProductNames = prods.Select(p => p.Name).ToList()
    });

foreach (var row in categoryReport)
{
    Console.WriteLine($"Category: {row.Name}");
    if (row.ProductNames.Count == 0)
        Console.WriteLine("  (No products)");
    else
        foreach (var prodName in row.ProductNames)
            Console.WriteLine($"  - {prodName}");
}

7. Nested groupings and working with aggregates

You can combine GroupJoin with aggregate functions to make more complex reports.

Example: Count the number of products in each category

var reportWithCount = categories.GroupJoin(
    products,
    category => category.Id,
    product => product.CategoryId,
    (category, prods) => new
    {
        Category = category.Name,
        Count = prods.Count()   // Aggregate function!
    });

foreach (var rec in reportWithCount)
{
    Console.WriteLine($"{rec.Category}: {rec.Count} products");
}

Let’s say the categories and products collections have these values:

var categories = new[]
{
    new { Id = 1, Name = "Fruits" },
    new { Id = 2, Name = "Vegetables" },
    new { Id = 3, Name = "Dairy products" }
};

var products = new[]
{
    new { Id = 1, Name = "Apple", CategoryId = 1 },
    new { Id = 2, Name = "Banana", CategoryId = 1 },
    new { Id = 3, Name = "Carrot", CategoryId = 2 }
};

Console output will be:

Category: Fruits — 2 product(s)
Category: Vegetables — 1 product(s)
Category: Dairy products — 0 product(s)

8. GroupJoin and typical beginner mistakes

A common mistake is to expect that the result of GroupJoin will be a flat table of pairs, like with a regular Join. By "flat" here we mean a structure where each row is a pair: one outer element and one matching inner (like an SQL table after INNER JOIN).

This especially confuses folks who’ve worked a bit with databases: they expect GroupJoin to act like LEFT JOIN, but return pairs in rows, not groups. But GroupJoin returns an element from the outer collection and a collection of related inner elements — basically, a nested structure.

Don’t forget to “unwrap” nested collections when you need to — for example, with SelectMany if you want a regular sequence of pairs.

Another common mistake is to forget that for elements with no match, the subgroup will just be an empty list. That’s default behavior, not a bug — but it’s important to remember so you’re not surprised when “nothing happens” in the output.

When should you use GroupJoin, and when not?

Use GroupJoin:

• When you have two data sets (like departments and employees, categories and products) and you want to show them hierarchically: for each “parent” all its “kids.”
• For building complex reports where you need to show all main elements even if there are no “children.”
• When you need something like SQL’s LEFT OUTER JOIN with grouping by key.

Don’t use GroupJoin where you just need to intersect collections or get exactly one pair per match — for that, use regular Join.