LINQ Inner Join in C#

Back to: LINQ Tutorial For Beginners and Professionals

LINQ Inner Join in C# with Examples

In this article, I am going to 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 learn what is LINQ Inner Join and when and how to use Inner Join in LINQ with Examples.

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 one time 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 is used to return 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, then only the matching elements which exist in both data sources are included in the result set. For a better understanding of Inner Join, please have a look at the following image which shows the pictorial representation of Inner Join.

Note: While performing the Inner Join, there should exist a common element or property in both data sources.

How to Implement Inner Join in LINQ?

To implement LINQ Inner Join we need to use the Join method 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 that contains data from both collections and it is the same as the SQL join. There are two overloaded versions of the Join method available in LINQ, their signature is as follows.

As you can see there are two overloaded versions of the Join Method available in LINQ to perform Inner Join Operations in C#. The one and only difference between the above two overloaded versions is that the second overloaded 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 first collection to be involved in the Join Operation.
2. Inner Data Source: This is the second data source or second collection to be 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 which will include 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 some examples using C# language. For this, we are going to use the following two model classes i.e. Employee and Address. So, create a class file with the name Employee.cs and then copy and paste the following code into it. This is a very simple class having 3 properties i.e. Id, Name, and AddressId. We have also created one method which is going to return a collection of Employees which is going to be our first data source going to be used in 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 with the name Address.cs and then copy and paste the following code into it. This is a very simple class having 2 properties i.e. Id, and AddressLine. We have also created one method which is going to return a collection of addresses which is going to be our second data source going to be used in 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 need to fetch the data from a database. Here we are not going to focus on how to fetch the data from a database rather we are going to focus on how to perform the inner join. So, here we created the required data sources (i.e. 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 am going to explain to you how to join multiple data sources using the LINQ Join method.

Here 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 records 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 which are having the address. If one employee does not have the corresponding address, then we don’t want that employee in our result set. So, basically, we need to perform Inner Join in between the Employee and Address Data Sources. In this case, you can take any data source as the Outer data source as well as any data source as the Inner Data Source.

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

The following code shows how to perform LINQ Inner Join in C# using the Method Syntax. 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 accessing 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, then you can access the elements using a for each loop which is shown in the below example. 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 with the name EmployeeAddress.cs with the required properties that 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 make sure we are going to get the output as expected 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 very much 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.

For a better understanding of how to perform the Join using LINQ Query Syntax, please have a look at the following code snippet. First, we need to fetch the data from Outer Data Source, 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 we are accessing 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 type or to 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, then you can access the elements using a for each loop which is shown in the below example. 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 also project the result to a named type using the Query Syntax. 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();
        }
    }
}

In the next article, I am going to discuss how to Join three Data Sources using both LINQ Method and Query Syntax in C# with Examples. In this article, I try to explain Linq Inner Join in C# using both Query and Method syntax. I hope you understood how to perform the LINQ Inner Join in C# with two Data Sources using both Method and Query Syntax.