Linq Join with Multiple Data Sources

Back to: LINQ Tutorial For Beginners and Professionals

Linq Join with Multiple Data Sources in C#

In this article, I am going to discuss how to perform Linq Join with Multiple data sources in C# with some examples. I strongly recommended you to read our previous article before proceeding to this article where we discussed how to perform Linq Join with two data sources in C# with some examples.

Data Sources used in this Demo:

We are going to use the following three data sources in this demo.

Here the AddressId property of the Employee Data Source points to the ID property of the Address Data Source. Similarly, the DepartmentId property of the Employee Data Source refers to the ID property of the Department Data Source.

Now we need to fetch the following data from the above three data sources. Notice, we only required to fetch the data which exist in all the three data sources.

As you can see, we need to fetch only the employees with employee IDs 1, 2, 3, 9, 10, and 11. This is because these 6 employees are present in all the data sources and while we are performing the Linq Inner join, then it only fetches the records which exist in both the data sources.

Creating Data Sources:

Create a class file and then copy and paste the following code which will create the required models with the required data.

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 int DepartmentId { get; set; }

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

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

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

        public static List<Department> GetAllDepartments()
        {
            return new List<Department>()
                {
                    new Department { ID = 10, Name = "IT"       },
                    new Department { ID = 20, Name = "HR"       },
                    new Department { ID = 30, Name = "Payroll"  },
                };
        }
    }
}

Joining three Data Sources using Query Syntax in Linq:

The following diagram shows how to join three data sources using Linq Query Syntax:

If you want to join the fourth data source then you need to write another join within the query.

The complete example is given below.

using System.Linq;
using System;

namespace LINQJoin
{
    class Program
    {
        static void Main(string[] args)
        {
            var JoinMultipleDSUsingQS = (//Data Source1
                                         from emp in Employee.GetAllEmployees()

                                         //Joining with Address Data Source (Data Source2)
                                         join adrs in Address.GetAllAddresses()
                                         on emp.AddressId equals adrs.ID

                                         //Joining with Department Data Source (Data Source3)
                                         join dept in Department.GetAllDepartments()
                                         on emp.DepartmentId equals dept.ID

                                         //Projecting the result set
                                         select new
                                         {
                                             ID = emp.ID,
                                             EmployeeName = emp.Name,
                                             DepartmentName = dept.Name,
                                             AddressLine = adrs.AddressLine
                                         }).ToList();

            foreach (var employee in JoinMultipleDSUsingQS)
            {
                Console.WriteLine($"ID = {employee.ID}, Name = {employee.EmployeeName}, Department = {employee.DepartmentName}, Addres = {employee.AddressLine}");
            }
            Console.ReadLine();
        }
    }
}

Output:

Using Method syntax to perform Join using Multiple Data Sources:

Let see how to rewrite the same example using Method syntax. Using method syntax it is a little difficult to write the join query using more than two data sources. The following image shows how to write the query to fetch data from three data sources using Method syntax.

The complete code is given below.

using System.Linq;
using System;

namespace LINQJoin
{
    class Program
    {
        static void Main(string[] args)
        {
            var JoinMultipleDSUsingMS =
                            //Employee data Source (i.e. Data Source 1)
                            Employee.GetAllEmployees()

                            //Joining with Address data Source (i.e. Data Source 2)
                            .Join(
                                    Address.GetAllAddresses(), //Inner Data Source 1
                                    empLevel1 => empLevel1.AddressId, //Outer Key selector
                                    addLevel1 => addLevel1.ID, //Inner Key selector

                                    //Result set
                                    (empLevel1, addLevel1) => new { empLevel1, addLevel1 }
                                )

                            // Joinging with Department data Source (i.e. data Source 3)
                            .Join(
                                    Department.GetAllDepartments(), //Inner Data Source 2

                                    //You cannot access the outer key selector directly
                                    //You can only access with the result set created in previous step
                                    //i.e. using empLevel1 and addLevel1
                                    empLevel2 => empLevel2.empLevel1.ID, //Outer Key selector
                                    addLevel1 => addLevel1.ID, //Inner Key selector

                                    //Result set
                                    (empLevel2, addLevel1) => new { empLevel2, addLevel1 }
                            )

                            //Creating the actual result set
                            .Select(e => new
                            {
                                ID = e.empLevel2.empLevel1.ID,
                                EmployeeName = e.empLevel2.empLevel1.Name,
                                AddressLine = e.empLevel2.addLevel1.AddressLine,
                                DepartmentName = e.addLevel1.Name
                            }).ToList();

            foreach (var employee in JoinMultipleDSUsingMS)
            {
                Console.WriteLine($"ID = {employee.ID}, Name = {employee.EmployeeName}, Department = {employee.DepartmentName}, Addres = {employee.AddressLine}");
            }
            Console.ReadLine();
        }
    }
}

It will give you the same output as the previous example.

Note: If you want to fetch the data from two data sources then you can use either the Method syntax or Query Syntax. But if you want to fetch the data from more than two data sources then it is easier to use the Query syntax rather than the Method syntax.

In the next article, I am going to discuss the Group Join in Linq with some examples. In this article, I try to explain how to perform Linq Joins with Multiple data sources in C#. I hope you understood how to join multiple data sources using Linq in C#.