Inner Join in Linq

Inner Join in Linq with Examples

In this article, I am going to discuss the Inner Join in Linq with some 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.

What is Inner Join in Linq?

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 the 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 LINQ inner join, then only the matching elements which exist in both the data sources are included in the result set. Let’s have a look at the following diagram.

Inner Join in Linq

Note: While performing the Linq inner join then there should exist a common element or property in both the data sources.

What is Linq Join Method in C#?

The Linq Join Method in C# operates on two data sources or you can say two collections such as inner collection and outer collection. This operator returns a new collection which contains the data from both the collections and it is the same as the SQL join operator. Let us have a look at the signature of the Linq Join Methods.

Linq Join Method in C#

As you can see there are two overloaded versions available in Linq to perform inner join operations. The second overloaded version takes a comparer as an extra parameter.

So, while working with Linq Join you need to understand the following five things.

  1. Outer data source
  2. Inner data source
  3. Outer Key selector (common key in the outer data source)
  4. Inner Key selector (Common key in the inner data source)
  5. Result selector (project the data into a result set)

Let us understand the Linq Inner Join with some examples.

Creating the Model classes:

In this demo, we are going to use the following two model classes i.e. Employee and Address. So, create a class file and then copy and paste the following code in it.

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}
            };
        }
    }

    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.

Here in both the 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.

Example1:

Our requirement is to fetch the employee name and their address into an anonymous type. But here we need to fetch only the elements which are present in both the data sources.

Using Method Syntax:

Linq Inner Join Using Method Syntax

The Complete example is given below.

using System.Linq;
using System;

namespace LINQJoin
{
    class Program
    {
        static void Main(string[] args)
        {
            var JoinUsingMS = Employee.GetAllEmployees() //Outer Data Source
                           .Join(
                           Address.GetAllAddresses(),  //Inner Data Source
                           employee => employee.AddressId, //Inner Key Selector
                           address => address.ID, //Outer Key selector
                           (employee, address) => new //Projecting the data into a result set
                           {
                               EmployeeName = employee.Name,
                               AddressLine = address.AddressLine
                           }).ToList();

            foreach (var employee in JoinUsingMS)
            {
                Console.WriteLine($"Name :{employee.EmployeeName}, Address : {employee.AddressLine}");
            }

            Console.ReadLine();
        }
    }
}

Output:

Inner Join Output

As you can see in the above output, it only fetches the matching records from both the data sources. Let’s rewrite the same example using Linq Query Syntax.

Using Linq Query Syntax to Perform Inner Join:

The Linq provides the join operator to perform the joins using Query syntax. Performing the join using query syntax is very much similar to performing the join in SQL.

Using Linq Query Syntax to Perform Inner Join

The complete example is given below.

using System.Linq;
using System;

namespace LINQJoin
{
    class Program
    {
        static void Main(string[] args)
        {
            var JoinUsingQS = (from emp in Employee.GetAllEmployees()
                               join address in Address.GetAllAddresses()
                               on emp.AddressId equals address.ID
                               select new
                               {
                                   EmployeeName = emp.Name,
                                   AddressLine = address.AddressLine
                               }).ToList();

            foreach (var employee in JoinUsingQS)
            {
                Console.WriteLine($"Name :{employee.EmployeeName}, Address : {employee.AddressLine}");
            }

            Console.ReadLine();
        }
    }
}

It will give you the same result as the previous one. In the next article, I am going to discuss how to join three data sources using both Method and Query syntax in Linq. In this article, I try to explain Inner Join in Linq using both query and method syntax. I hope you understood how to perform the inner join in LINQ with two data sources using both Method and Query syntax.

Leave a Reply

Your email address will not be published. Required fields are marked *