LINQ Joins

In LINQ, joins work similarly to SQL joins, allowing you to combine data from two collections based on a related key. Below is an explanation of each type of join: Inner Join, Left Join, Right Join, and Outer Join, along with examples in LINQ.

Let's assume we have the following two classes and two collections, customers and orders:

csharp
public class Customer
{
    public int CustomerId { get; set; }
    public string Name { get; set; }
}

public class Order
{
    public int OrderId { get; set; }
    public int CustomerId { get; set; }
    public string Product { get; set; }
}

List<Customer> customers = new List<Customer>
{
    new Customer { CustomerId = 1, Name = "Alice" },
    new Customer { CustomerId = 2, Name = "Bob" },
    new Customer { CustomerId = 3, Name = "Charlie" }
};

List<Order> orders = new List<Order>
{
    new Order { OrderId = 101, CustomerId = 1, Product = "Laptop" },
    new Order { OrderId = 102, CustomerId = 1, Product = "Smartphone" },
    new Order { OrderId = 103, CustomerId = 2, Product = "Tablet" }
};

1. Inner Join

An inner join returns only the matching records from both collections where the CustomerId in customers matches the CustomerId in orders.

LINQ Example:

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

Explanation:

• Only records where CustomerId exists in both customers and orders are returned.

Result:

plaintext
CustomerName: Alice, Product: Laptop
CustomerName: Alice, Product: Smartphone
CustomerName: Bob, Product: Tablet

2. Left Join

A left join returns all records from the left collection (customers) and the matching records from the right collection (orders). If no match is found, null values are returned for the orders.

LINQ Example:

csharp
var leftJoin = from customer in customers
               join order in orders
               on customer.CustomerId equals order.CustomerId into customerOrders
               from order in customerOrders.DefaultIfEmpty()
               select new
               {
                   CustomerName = customer.Name,
                   Product = order?.Product // Null if no matching order
               };

Explanation:

• into customerOrders creates a group join, gathering matching orders.
• DefaultIfEmpty() provides null for orders when there's no match, achieving a left join.

Result:

plaintext
CustomerName: Alice, Product: Laptop
CustomerName: Alice, Product: Smartphone
CustomerName: Bob, Product: Tablet
CustomerName: Charlie, Product: null

3. Right Join

A right join in LINQ (similar to SQL) isn’t directly supported, but we can simulate it by reversing the left join.

LINQ Example:

csharp
var rightJoin = from order in orders
                join customer in customers
                on order.CustomerId equals customer.CustomerId into orderCustomers
                from customer in orderCustomers.DefaultIfEmpty()
                select new
                {
                    CustomerName = customer?.Name, // Null if no matching customer
                    Product = order.Product
                };

Explanation:

• We start with orders instead of customers, and we join with customers.
• DefaultIfEmpty() provides null for customers if there is no match, simulating a right join.

Result:

plaintext
CustomerName: Alice, Product: Laptop
CustomerName: Alice, Product: Smartphone
CustomerName: Bob, Product: Tablet

If there were an order with no matching customer, it would appear with a null for CustomerName.

4. Full Outer Join

A full outer join includes all records from both collections, with null values for non-matching elements. This isn’t directly supported in LINQ, so we simulate it by combining a left join and a right join, excluding duplicates.

LINQ Example:

csharp
var leftJoin = from customer in customers
               join order in orders
               on customer.CustomerId equals order.CustomerId into customerOrders
               from order in customerOrders.DefaultIfEmpty()
               select new
               {
                   CustomerName = customer.Name,
                   Product = order?.Product
               };

var rightJoin = from order in orders
                join customer in customers
                on order.CustomerId equals customer.CustomerId into orderCustomers
                from customer in orderCustomers.DefaultIfEmpty()
                select new
                {
                    CustomerName = customer?.Name,
                    Product = order.Product
                };

// Full outer join: combine left and right joins, remove duplicates
var fullOuterJoin = leftJoin.Union(rightJoin);

Explanation:

• Perform both the left join and right join.
• Use Union to combine results, removing duplicates to achieve a full outer join.

Result:

plaintext
CustomerName: Alice, Product: Laptop
CustomerName: Alice, Product: Smartphone
CustomerName: Bob, Product: Tablet
CustomerName: Charlie, Product: null

This approach includes all records from both collections, with null for non-matching items.