LINQ Inner Join in C#

Back to: LINQ Tutorial For Beginners and Professionals

LINQ Inner Join in C# with Examples

In this article, I will discuss the LINQ Inner Join in C# with Examples. Please read our previous article before proceeding to this article, where we discussed the basics of LINQ Join in C#. This is the most common join used in real-time applications. At the end of this article, you will understand the following pointers:

1. What is LINQ Inner Join in C#?
2. How to Implement Inner Join in LINQ?
3. Examples to Understand LINQ Inner Join in C#
4. Using LINQ Join Method to Perform Inner Join 
5. Using LINQ Query Syntax to Perform Inner Join
6. When to Use LINQ Inner Join in C#?

What is LINQ Inner Join in C#?

As per the Microsoft documentation, An Inner Join produces a result set in which each element of the first collection appears once for every matching element in the second collection. If an element in the first collection does not have any matching element in the second collection, then it does not appear in the result set.

In simple words, we can say that the LINQ Inner Join in C# returns only the matching elements from both data sources while the non-matching elements are removed from the result set. So, if you have two data sources, and when you perform the Inner Join, only the matching elements in both data sources are included in the result set. For a better understanding of Inner Join, please look at the following image, which shows the pictorial representation of Inner Join.

Note: A common property should exist in both data sources while performing the Inner Join, and based on the common property, the matching records are retrieved.

How to Implement Inner Join in LINQ?

We need to use the LINQ Join Method to implement Inner Join in C#. The LINQ Join Method operates on two data sources or two sequences, or you can also say two collections, such as inner collection and outer collection. The Join Method returns a new collection containing data from both collections, the same as the SQL join, based on a common property. Two overloaded versions of the Join method are available in LINQ, and their signature is as follows.

The only difference between the above two overloaded versions is that the second version takes a comparer as an extra parameter. So, while working with LINQ Join Method (with LINQ Method Syntax) or join operator (with LINQ Query Syntax), we need to understand the following five things.

1. Outer Data Source: This is the first data source or collection involved in the Join Operation.
2. Inner Data Source: This is the second data source or collection involved in the Join Operation.
3. Outer Key Selector: This will be the common key in the outer data source.
4. Inner Key Selector: This will be the common key in the inner data source.
5. Result Selector: Project the data into a result set, including the properties from both Inner and Outer Data Sources.

Examples to Understand LINQ Inner Join in C#:

Let us understand How to Implement LINQ Inner Join with examples using C# language. For this, we will use the following two model classes, i.e., Employee and Address. So, create a class file named Employee.cs and copy and paste the following code. This class has 3 properties, i.e., Id, Name, and AddressId. We have also created one method to return a collection of Employees, which will be our first data source for the Inner Join.

using System.Collections.Generic;
namespace LINQJoin
{
    public class Employee
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public int AddressId { get; set; }

        public static List<Employee> GetAllEmployees()
        {
            return new List<Employee>()
            {
                new Employee { ID = 1, Name = "Preety", AddressId = 1 },
                new Employee { ID = 2, Name = "Priyanka", AddressId = 2 },
                new Employee { ID = 3, Name = "Anurag", AddressId = 3 },
                new Employee { ID = 4, Name = "Pranaya", AddressId = 4 },
                new Employee { ID = 5, Name = "Hina", AddressId = 5 },
                new Employee { ID = 6, Name = "Sambit", AddressId = 6 },
                new Employee { ID = 7, Name = "Happy", AddressId = 7},
                new Employee { ID = 8, Name = "Tarun", AddressId = 8 },
                new Employee { ID = 9, Name = "Santosh", AddressId = 9 },
                new Employee { ID = 10, Name = "Raja", AddressId = 10},
                new Employee { ID = 11, Name = "Sudhanshu", AddressId = 11}
            };
        }
    }
}

Now, create another class file named Address.cs and copy and paste the following code. This class has 2 properties, i.e., Id and AddressLine. We have also created one method to return a collection of addresses, which will be our second data source for the Inner Join.

using System.Collections.Generic;
namespace LINQJoin
{
    public class Address
    {
        public int ID { get; set; }
        public string AddressLine { get; set; }

        public static List<Address> GetAllAddresses()
        {
            return new List<Address>()
            {
                new Address { ID = 1, AddressLine = "AddressLine1"},
                new Address { ID = 2, AddressLine = "AddressLine2"},
                new Address { ID = 3, AddressLine = "AddressLine3"},
                new Address { ID = 4, AddressLine = "AddressLine4"},
                new Address { ID = 5, AddressLine = "AddressLine5"},
                new Address { ID = 9, AddressLine = "AddressLine9"},
                new Address { ID = 10, AddressLine = "AddressLine10"},
                new Address { ID = 11, AddressLine = "AddressLine11"},
            };
        }
    }
}

Note: In Real-Time Applications, you must fetch data from a database. Here, we will not focus on how to fetch the data from a database. Rather, we will focus on how to perform the inner join. So, here, we created the required data sources (i.e., a list of employees and addresses) with the hard-coded data. In this article, I am showing how to join two data sources, and in our next article, I will explain how to join multiple data sources using the LINQ Join method.

In both data sources, the common property is the Address ID, i.e., the AddressId property of the Employee data source, and the ID property of the Address data source is the common property. As you can see, we have 11 records in the employee data source and 8 in the addresses data source. Further, if you notice, some of the data are present in both the data sources.

Using LINQ Join Method in C#:

Let us see how to use the LINQ Join Method in C#. Our requirement is to fetch the employee’s name and corresponding address in an anonymous type. But here, we need to fetch only the employees who have addresses. If one employee does not have the corresponding address, we don’t want that employee in our result set. So, basically, we need to perform an Inner Join between the Employee and Address Data Sources. In this case, you can take any data source as the Outer data source and any data source as the Inner Data Source.

Using LINQ Method Syntax to Perform Inner Join in C#:

The following code uses the Method Syntax to perform LINQ Inner Join in C#. Here, you can see we are using the Join Method. Employee.GetAllEmployees()  is our Outer Data Source and Address.GetAllAddresses() is our Inner Data Source. Here, we are accessing the employee’s information using the employee variable and the addresses using the address variable. Further, you can notice we are specifying the outer key selector as AddressId using Lambda Expression and specifying the Inner Key Selector as ID using the lambda expression. These Outer and Inner Key Selectors are nothing but the common properties in both data sources. Finally, we are projecting the result to an anonymous type and fetching the employee name to the EmployeeName property and AddressLine to the AddressLine property.

The complete example code is given below. Once the Join Operation is done, you can access the elements using a for each loop, which is shown in the example below. The following code is self-explained, so please go through the comment lines.

using System.Linq;
using System;
namespace LINQJoin
{
    class Program
    {
        static void Main(string[] args)
        {
            //Performing Inner Between Employees and Addresses Data Sources
            var JoinUsingMS = Employee.GetAllEmployees() //Outer Data Source
                           .Join( //Performing LINQ Inner Join
                           Address.GetAllAddresses(),  //Outer Data Source
                           employee => employee.AddressId, //Outer Key Selector
                           address => address.ID, //Inner Key selector
                           (employee, address) => new //Projecting the data into an Annonymous Type
                           {
                               EmployeeName = employee.Name,
                               AddressLine = address.AddressLine
                           }).ToList();

            //Accessing the Result using For Each Loop
            foreach (var employee in JoinUsingMS)
            {
                Console.WriteLine($"Name :{employee.EmployeeName}, Address : {employee.AddressLine}");
            }

            Console.ReadLine();
        }
    }
}

Output:

As you can see in the above output, it only fetches the matching records from both data sources. Instead of projecting the result to an anonymous type, can we project the result to a named type? Yes, it is possible. Let us see how we can do this. First, create a class file named EmployeeAddress.cs with the required properties you want in the result set. As per our requirement, we have created the class with two properties.

namespace LINQJoin
{
    class EmployeeAddress
    {
        public string EmployeeName { get; set; }
        public string AddressLine { get; set; }
    }
}

Next, modify the Main method of the Program class as follows. Here, you can see we are projecting the result to the above-created EmployeeAddress type. Further, we have changed the Inner and Outer Data Source order to ensure we get the expected output when performing the Inner Join Operations in LINQ. Here, we are making the Addresses the Outer Data Source and Employees as the Inner Data Source.

using System.Linq;
using System;
namespace LINQJoin
{
    class Program
    {
        static void Main(string[] args)
        {
            //Performing Inner Between Employees and Addresses Data Sources
            var JoinUsingMS = Address.GetAllAddresses()  //Outer Data Source
                           .Join( //Performing LINQ Inner Join
                           Employee.GetAllEmployees(),  //Outer Data Source
                           address => address.ID,   //Outer Key Selector
                           employee => employee.AddressId, //Inner Key selector
                           (address, employee) => new EmployeeAddress //Projecting the data to named type i.e. EmployeeAddress
                           {
                               EmployeeName = employee.Name,
                               AddressLine = address.AddressLine
                           }).ToList();

            //Accessing the Result using For Each Loop
            foreach (var employee in JoinUsingMS)
            {
                Console.WriteLine($"Name :{employee.EmployeeName}, Address : {employee.AddressLine}");
            }

            Console.ReadLine();
        }
    }
}

With the above changes in place, run the application, and you will also get the same output as the previous example, as shown in the below image.

Let’s rewrite the same example using LINQ Query Syntax.

Using LINQ Query Syntax to Perform Inner Join in C#:

LINQ provides the join operator to perform the join using Query syntax in C#. Performing the join operation using query syntax is similar to performing the join in SQL. I personally preferred to use Query Syntax instead of Method Syntax as it is syntactically similar to SQL Join.

To better understand how to perform the Join using LINQ Query Syntax, please look at the following code snippet. First, we need to fetch the data from the Outer Data Source, and then we need to use the “join” operator followed by the second data source. In the below code, we are accessing the first data source using variable emp and the second data source using the variable address. Like SQL Query, here we need to provide the joining condition using the “on” operator, and we need to provide the common properties using the “equals” operator. Finally, we need to project the result to either an anonymous or a named type. In the below code, we are projecting the result to an anonymous type.

The complete example code is given below. Once the Join Operation is done, you can access the elements using a for each loop, which is shown in the example below. The following code is self-explained, so please go through the comment lines.

using System.Linq;
using System;
namespace LINQJoin
{
    class Program
    {
        static void Main(string[] args)
        {
            //Performing Inner Join Between Employees and Addresses Collections
            var JoinUsingQS = (from emp in Employee.GetAllEmployees() //Outer Data Source
                               join address in Address.GetAllAddresses() //Joining with Inner Data Source
                               on emp.AddressId equals address.ID //Joining Condition
                               select new //Projecting the Result to an Anonymous Type
                               {
                                   EmployeeName = emp.Name,
                                   AddressLine = address.AddressLine
                               }).ToList();

            //Accessing the Elements using Foreach Loop
            foreach (var employee in JoinUsingQS)
            {
                Console.WriteLine($"Name :{employee.EmployeeName}, Address : {employee.AddressLine}");
            }

            Console.ReadLine();
        }
    }
}

Now, run the application, and you will get the following output.

Instead of projecting the Result to an Anonymous Type, we can use the Query Syntax to project the result to a named type. We have already created a type called EmployeeAddress. Let us project the result to the EmployeeAddress type. So, modify the Main method of the Program class as follows.

using System.Linq;
using System;
namespace LINQJoin
{
    class Program
    {
        static void Main(string[] args)
        {
            //Performing Inner Join Between Employees and Addresses Collections
            var JoinUsingQS = (from emp in Employee.GetAllEmployees() //Outer Data Source
                               join address in Address.GetAllAddresses() //Joining with Inner Data Source
                               on emp.AddressId equals address.ID //Joining Condition
                               select new EmployeeAddress//Projecting the Result to EmployeeAddress Type
                               {
                                   EmployeeName = emp.Name,
                                   AddressLine = address.AddressLine
                               }).ToList();

            //Accessing the Elements using Foreach Loop
            foreach (var employee in JoinUsingQS)
            {
                Console.WriteLine($"Name :{employee.EmployeeName}, Address : {employee.AddressLine}");
            }

            Console.ReadLine();
        }
    }
}

When to Use LINQ Inner Join in C#?

You should use LINQ inner joins in C# when combining and retrieving data from two or more collections based on a common key and only want to include the elements with matching keys in both collections. Here are some scenarios when you should use LINQ inner joins:

• Combining Related Data from Different Sources: Use an inner join when you have related data in different collections and you need to combine them. For example, joining a list of employees with a list of departments to get a list of employees with their department names.
• Data Analysis and Reporting: In data processing and reporting scenarios, inner joins combine data from different datasets. For instance, joining sales data with product information to report sales by product category.
• Database-like Queries in In-Memory Collections: When you’re working with in-memory collections (like arrays and lists) and need to perform operations similar to SQL joins.
• Filtering One Collection Based on Another: An inner join inherently filters data. It’s useful when you want elements from one collection that have corresponding elements in another collection. For example, getting all orders that have corresponding customer information.
• Optimizing Performance for Large Datasets: LINQ’s inner join can be more efficient than manual nested looping, especially for larger datasets, as it’s optimized for performance.

In the next article, I will discuss Joining three Data Sources using the LINQ Method and Query Syntax in C# with Examples. I explain LINQ Inner Join in C# using Query and Method syntax in this article. I hope you understand how to perform the LINQ Inner Join in C# using Method and Query Syntax with two Data Sources.