Merging collections with join ( Join)

1. Introduction

Sometimes we have to work with two different collections that are connected by some common feature. For example, we have a list of orders and a separate collection of customers. How do you figure out which order belongs to which customer? For that, both collections need to be linked by a common identifier — like userId.

In relational databases, this is solved by the JOIN operation, which merges rows from different tables by matching keys. LINQ has a similar operator — it's also called Join.

Imagine two tables: the first one is a list of library readers with their IDs, and the second one is a list of book orders, where each order has a id of the reader. With join we "glue" these tables together by id, and as a result we get pairs like "reader + their order".

By the way, if you thought join is only for "databases", you're missing out! In programming, merging collections happens all the time, especially when you're working with external systems or structured files (JSON, XML, or those Excel tables from accounting).

2. The Join method signature and how it works

Here's what the Join method looks like:

public static IEnumerable<TResult> Join<TOuter, TInner, TKey, TResult>(
    this IEnumerable<TOuter> outer,                 // first collection (outer)
    IEnumerable<TInner> inner,                      // second collection (inner)
    Func<TOuter, TKey> outerKeySelector,            // how to get the key from the outer collection element
    Func<TInner, TKey> innerKeySelector,            // how to get the key from the inner one
    Func<TOuter, TInner, TResult> resultSelector)   // function to generate the result (new element)

At first glance it might look bulky, but let's break it down.
How it works:
For each element from the first collection (outer), LINQ looks for matching (by key) elements from the second (inner). When the keys match, resultSelector is called, and the result is saved to the final collection.

3. Example: Merging customers and their orders

Let's create a couple of classes and collections. This idea is a logical continuation of our learning app (let's say it's a little store we're building as we go through the course).

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; } = "";
}

public class Order
{
    public int Id { get; set; }
    public int CustomerId { get; set; }
    public string Product { get; set; } = "";
}

// Customers collection
var customers = new List<Customer>
{
    new Customer { Id = 1, Name = "Vasya" },
    new Customer { Id = 2, Name = "Petya" },
    new Customer { Id = 3, Name = "Masha" },
};

// Orders collection
var orders = new List<Order>
{
    new Order { Id = 101, CustomerId = 2, Product = "Book" },
    new Order { Id = 102, CustomerId = 1, Product = "Pen" },
    new Order { Id = 103, CustomerId = 2, Product = "Notebook" },
    new Order { Id = 104, CustomerId = 3, Product = "Eraser" },
};

Now we want to get all customers with their orders. For example, print: "Petya ordered Book", "Petya ordered Notebook", etc.

Using Join

// Merge customers with orders by customer.Id and order.CustomerId
var query = customers.Join(
    orders,
    customer => customer.Id,          // How to get the key from the customer
    order => order.CustomerId,        // How to get the key from the order
    (customer, order) => new          // What to do with matching pairs (create a new object)
    {
        CustomerName = customer.Name,
        Product = order.Product
    }
);

// Print the result
foreach (var item in query)
{
    Console.WriteLine($"{item.CustomerName} ordered {item.Product}");
}

What will be printed:

Vasya ordered Pen
Petya ordered Book
Petya ordered Notebook
Masha ordered Eraser

Notice: if a customer has several orders, they'll show up in the list several times — once for each order. That's exactly how it should be!

4. Table: Comparing Join and GroupBy + SelectMany

Join

GroupBy + SelectMany

Beginners often use Join where they actually need a "customer → list of orders" structure. But Join works row by row and doesn't group data. For that, there's GroupJoin — more on that in the next lecture. Or you can use GroupBy.

5. Join in Query Syntax (SQL-style LINQ)

LINQ supports two syntaxes: method-chaining (Method Syntax, like Join(...)) and the so-called SQL-style (Query Syntax), which visually looks like regular SQL queries.

For some devs — especially those who used to work with databases — this syntax can be more clear:

var query2 =
    from customer in customers
    join order in orders
        on customer.Id equals order.CustomerId
    select new
    {
        CustomerName = customer.Name,
        Product = order.Product
    };

foreach (var item in query2)
{
    Console.WriteLine($"{item.CustomerName} ordered {item.Product}");
}

Note:
In Query Syntax you use the keyword equals — the == operator doesn't work here!
This is a common trick question in interviews, especially for newbies 😉

6. Important details and gotchas

Sometimes, not all elements from the collections end up in the final result. That's because the Join method does what's called an "inner join", meaning it only merges elements where the keys match. If a customer doesn't have any orders, they just won't be in the final list. Likewise, if there's an order with a CustomerId that's not in the customers list, that order won't show up either.

But what if you want to get all customers, even those who don't have any orders? For that, there's a "left" join, which in LINQ is done with a combo of GroupJoin and SelectMany. We'll talk about that in the next lecture. In classic Join, both sides have to be present — otherwise, no match, and the element drops out of the result.

7. Working with multiple keys (composite key)

Sometimes you need to merge collections not by just one, but by several fields. For example: join products and their sales by "ProductCode + SaleYear".

In LINQ, you do this by creating anonymous objects as keys:

var sales = ...; // sales
var products = ...; // products

var query = products.Join(
    sales,
    prod => new { prod.Code, prod.Year },
    sale => new { sale.ProductCode, sale.Year },
    (prod, sale) => new { prod.Name, sale.Amount }
);

It's important that the types and property names in these anonymous objects match. If they're different, there won't be any matches — even if the values are actually the same.

8. What the merged collection looks like — diagram

Here's a diagram showing how join works (super simplified):

You can see from the diagram that each customer is joined with all their orders — as long as the keys match.

9. Quick notes on mistakes and quirks

One of the most common mistakes is mixing up the order of parameters, especially if the key types in both collections are the same. The compiler or LINQ won't throw an error, but the result might be empty or just weird.

Another common trap is trying to use Join to do a "left" join, so that all customers are in the result even if they don't have orders. But classic Join only works with matching pairs and doesn't include elements without matches.

2

Task

C# SELF, level 33, lesson 0

Locked

Simple merging of customer and order lists

Simple merging of customer and order lists

Open