LINQ Joins in C#

Back to: LINQ Tutorial For Beginners and Professionals

LINQ Joins in C#

In this article, I will give you an overview of LINQ Joins in C#. Please read our previous article, discussing the LINQ ToLookup Method in C# with Examples. As part of this article, we will discuss the following pointers.

1. What are LINQ Join Operations?
2. Why do we need to perform the Join Operations?
3. What are the different methods available to perform Join Operations in LINQ?
4. Finally, what are the different types of LINQ Joins?

If you have any experience in database systems like SQL Server, Oracle, MySQL, etc., then you may be familiar with SQL Joins. The LINQ Joins are not different. They are also used to merge the data from two or more data sources (tables or objects) into a single result set based on some common property. Using LINQ Joins, it is also possible to fetch the data based on some conditions.

What are LINQ Joins?

As per the Microsoft documentation, joining two data sources is the association of objects in one data source with objects that share a common attribute in another data source.

We can simplify the above definition as Join operations are used to fetch the data from two or more data sources based on some common properties present in the data sources.

Why do we need to Perform Join Operations?

Let us understand why we need to perform Join Operations with an example. For example, let’s say we have the following three data sources (Employee, Department, and Address).

Now, we need to fetch the data from the above three data sources, as shown below.

As you can see, the above result set contains the data from all three data sources. The most important point you need to understand is while performing join, you need to have a common property in all the data sources involved. In this case, the common property between the Employee and the Department is the Department Id, which is present in both data sources. In the same line, Address ID is the common property between the Employee and Address data sources. So, in scenarios like this, we need to use LINQ Join to fetch the data. 

What are the Methods Available in LINQ to Perform the Join Operations?

In C#, you can use LINQ (Language Integrated Query) to perform joins on collections or sequences of data. LINQ provides several methods for performing joins, similar to SQL joins, to combine data from two or more collections based on a common key or condition. The primary join methods in LINQ are Join, GroupJoin, and SelectMany. Here’s how you can use them:

Join Method:

The Join method combines two sequences based on matching keys from each sequence. It returns a new sequence of combined elements.

Syntax:

var result = from item1 in collection1
join item2 in collection2 on item1.Key equals item2.Key
select new { Item1 = item1, Item2 = item2 };

Example:

using System;
using System.Collections.Generic;
using System.Linq;
namespace LINQDemo
{
    public class Program
    {
        static void Main()
        {
            var customers = new List<Customer>
            {
                new Customer { Id = 1, Name = "Alice" },
                new Customer { Id = 2, Name = "Bob" }
            };

            var orders = new List<Order>
            {
                new Order { OrderId = 101, CustomerId = 1 },
                new Order { OrderId = 102, CustomerId = 2 }
            };

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

            foreach (var item in result)
            {
                Console.WriteLine($"Customer: {item.CustomerName}, Order ID: {item.OrderId}");
            }

            Console.ReadKey();
        }
    }

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

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

GroupJoin Method:

The GroupJoin method is similar to Join, but it groups elements from the first sequence and associates them with matching elements from the second sequence.

Syntax:

var result = from item1 in collection1
join item2 in collection2 on item1.Key equals item2.Key into grouped
select new { Item1 = item1, GroupedItems = grouped };

Example:

using System;
using System.Collections.Generic;
using System.Linq;
namespace LINQDemo
{
    public class Program
    {
        static void Main()
        {
            var departments = new List<Department>
            {
                new Department { DepartmentId = 1, Name = "HR" },
                new Department { DepartmentId = 2, Name = "IT" }
            };

            var employees = new List<Employee>
            {
                new Employee { EmployeeId = 101, DepartmentId = 1, Name = "Alice" },
                new Employee { EmployeeId = 102, DepartmentId = 2, Name = "Bob" },
                new Employee { EmployeeId = 103, DepartmentId = 1, Name = "Charlie" }
            };

            var result = from department in departments
                         join employee in employees on department.DepartmentId equals employee.DepartmentId into grouped
                         select new { DepartmentName = department.Name, Employees = grouped };

            foreach (var item in result)
            {
                Console.WriteLine($"Department: {item.DepartmentName}");
                foreach (var employee in item.Employees)
                {
                    Console.WriteLine($"- Employee: {employee.Name}");
                }
            }

            Console.ReadKey();
        }
    }

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

    public class Employee
    {
        public int EmployeeId { get; set; }
        public int DepartmentId { get; set; }
        public string Name { get; set; }
    }
}

SelectMany Method:

The SelectMany method is used to perform multiple joins or flatten nested collections.

Syntax:

var result = from item1 in collection1
from item2 in item1.SubCollection
select new { Item1 = item1, Item2 = item2 };

Example:

using System;
using System.Collections.Generic;
using System.Linq;
namespace LINQDemo
{
    public class Program
    {
        static void Main()
        {
            var students = new List<Student>
            {
                new Student { Id = 1, Name = "Alice", Courses = new List<string> { "Math", "Physics" } },
                new Student { Id = 2, Name = "Bob", Courses = new List<string> { "History", "English" } }
            };

            var result = from student in students
                         from course in student.Courses
                         select new { StudentName = student.Name, CourseName = course };

            foreach (var item in result)
            {
                Console.WriteLine($"Student: {item.StudentName}, Course: {item.CourseName}");
            }


            Console.ReadKey();
        }
    }

    public class Student
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public List<string> Courses { get; set; }
    }
}

These examples demonstrate using LINQ join methods to combine data from multiple collections based on common keys or conditions. You can use these methods to perform various types of joins and create structured, combined datasets for your applications. In our upcoming articles, we will discuss implementing different types of joins using the above three methods.

What are the Different Types of LINQ Joins Available in C#?

We can perform different types of joins, such as Inner, Left, Right, Full, and Cross Join in LINQ. For a better understanding, please have a look at the below image.

Inner Join: The Inner Join returns only the matching rows from both the data sources involved in the join by removing the non-matching records.

Left Outer Join: The LEFT OUTER JOIN retrieves all the matching rows from both the data sources involved in the join and non-matching rows from the left side data source. In this case, the un-matching data will take a null value.

Right Outer Join: The RIGHT OUTER JOIN retrieves all the matching rows from the data sources involved in the join and non-matching rows from the right-side data source. In this case, the un-matching data will take NULL values.

Full Outer Join: The Full Outer Join retrieves all the matching and non-matching rows from both data sources involved in the JOIN. The non-matching data in such cases will take the NULL values.

Cross Join: In Cross Join, each record of a data source is joined with each record of the other data source.

In the next article, I will discuss the LINQ Inner Join in C# with Examples. In this article, I try to give an overview of LINQ Join in C#, and I hope you enjoy this article.