LINQ Join Operators

In C#, LINQ provides several methods for joining data from multiple sequences. These methods allow you to combine data based on a common key or relationship. The main join operators in LINQ are as following:

1. Join
2. GroupJoin

Join

It is used to perform an inner join between two sequences based on a common key

1. Inner Join

In LINQ query syntax (also known as query expression syntax), you can perform join operations using the join keyword. Here’s how you can use query syntax to achieve the same results as the method-based joins.

1. Inner Join

To perform an inner join, use the join keyword to combine elements from two sequences based on a common key.

Example

Using the Person and Department classes.

using System;
using System.Collections.Generic;
using System.Linq;

public class Person
{
    public string Name { get; set; }
    public int DepartmentId { get; set; }
}

public class Department
{
    public int Id { get; set; }
    public string DepartmentName { get; set; }
}

class Program
{
    static void Main()
    {
        List<Person> people = new List<Person>
        {
            new Person { Name = "John", DepartmentId = 1 },
            new Person { Name = "Jane", DepartmentId = 2 },
            new Person { Name = "Bill", DepartmentId = 1 }
        };

        List<Department> departments = new List<Department>
        {
            new Department { Id = 1, DepartmentName = "HR" },
            new Department { Id = 2, DepartmentName = "IT" }
        };

        var innerJoinQuery = from person in people
                             join department in departments
                             on person.DepartmentId equals department.Id
                             select new
                             {
                                 PersonName = person.Name,
                                 DepartmentName = department.DepartmentName
                             };

        foreach (var item in innerJoinQuery)
        {
            Console.WriteLine($"Person: {item.PersonName}, Department: {item.DepartmentName}");
        }
    }
}

Output

2. Left Outer Join

To perform a left outer join, you need to use a combination of join and DefaultIfEmpty to include elements from the left sequence even if there are no matching elements in the right sequence.

Example

using System;
using System.Collections.Generic;
using System.Linq;

public class Person
{
    public string Name { get; set; }
    public int DepartmentId { get; set; }
}

public class Department
{
    public int Id { get; set; }
    public string DepartmentName { get; set; }
}

class Program
{
    static void Main()
    {
       List<Person> people = new List<Person>
      {
         new Person { Name = "Rohatash", DepartmentId = 1 },
         new Person { Name = "Mohit", DepartmentId = 2 },
         new Person { Name = "Saurav", DepartmentId = 1 }
    };

     List<Department> departments = new List<Department>
     {
         new Department { Id = 1, DepartmentName = "HR" },
         new Department { Id = 2, DepartmentName = "IT" },
         new Department { Id = 3, DepartmentName = "AT" }
     };

        var leftOuterJoinQuery = from department in departments
                                 join person in people
                                 on department.Id equals person.DepartmentId into peopleGroup
                                 from person in peopleGroup.DefaultIfEmpty() // Ensures all departments are included
                                 select new
                                 {
                                     DepartmentName = department.DepartmentName,
                                     PersonName = person?.Name // Handle null if no matching person
                                 };

        foreach (var item in leftOuterJoinQuery)
        {
            Console.WriteLine($"Department: {item.DepartmentName}, Person: {item.PersonName}");
        }
    }
}

Output

3. Cross Join

LINQ does not provide a direct way to perform a cross join, but you can achieve it by using from clauses with multiple sequences.

Example

using System;
using System.Collections.Generic;
using System.Linq;

public class Person
{
    public string Name { get; set; }
    public int DepartmentId { get; set; }
}

public class Department
{
    public int Id { get; set; }
    public string DepartmentName { get; set; }
}

class Program
{
    static void Main()
    {
        List<Person> people = new List<Person>
        {
            new Person { Name = "Rohatash", DepartmentId = 1 },
            new Person { Name = "Mohit", DepartmentId = 2 },
            new Person { Name = "Saurav", DepartmentId = 1 }
        };

        List<Department> departments = new List<Department>
        {
            new Department { Id = 1, DepartmentName = "HR" },
            new Department { Id = 2, DepartmentName = "IT" },
             new Department { Id = 3, DepartmentName = "AT" }
        };

        var crossJoinQuery = from person in people
                             from department in departments
                             select new
                             {
                                 PersonName = person.Name,
                                 DepartmentName = department.DepartmentName
                             };

        foreach (var item in crossJoinQuery)
        {
            Console.WriteLine($"Person: {item.PersonName}, Department: {item.DepartmentName}");
        }
    }
}

Output

2. GroupJoin

The GroupJoin method in LINQ query syntax is used to perform a left outer join but returns the results grouped into collections. This is useful when you want to process or display the results in a hierarchical or grouped format.

Example

using System;
using System.Collections.Generic;
using System.Linq;

public class Person
{
    public string Name { get; set; }
    public int DepartmentId { get; set; }
}
public class Department
{
    public int Id { get; set; }
    public string DepartmentName { get; set; }
}
class Program
{
    static void Main()
    {
        List<Person> people = new List<Person>
        {
            new Person { Name = "Rohatash", DepartmentId = 1 },
            new Person { Name = "Mohit", DepartmentId = 2 },
            new Person { Name = "Saurav", DepartmentId = 1 }
        };

        List<Department> departments = new List<Department>
        {
            new Department { Id = 1, DepartmentName = "HR" },
            new Department { Id = 2, DepartmentName = "IT" },
             new Department { Id = 3, DepartmentName = "AT" }
        };

        var groupJoinQuery = from department in departments
                             join person in people
                             on department.Id equals person.DepartmentId into peopleGroup
                             select new
                             {
                                 DepartmentName = department.DepartmentName,
                                 People = peopleGroup // Collection of people grouped by department
                             };

        foreach (var department in groupJoinQuery)
        {
            Console.WriteLine($"Department: {department.DepartmentName}");
            foreach (var person in department.People)
            {
                Console.WriteLine($"  Person: {person.Name}");
            }
        }
    }
} 

Output