Join in LINQ Using C#.Net

In today's article we will check all the type of joins in LINQ using c#.net with examples. Join are important in relational data it represent the relation between the date. So, there are four types of joins are there in LINQ and they are as follows:

• Inner Join
• Group Join
• Left Outer Join
• Cross Join

Please check the diagram which represent the joins.

Please check the below table this table we will take as a reference for writing different examples of different joins in LINQ using c#.net.

Now for above table we will prepare the class and add the property. 

public class Employee
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int DepartmentId { get; set; }
    public string PhoneNo { get; set; }
}
public class Department
{
    public int Id { get; set; }
    public string DepartmentName { get; set; }
}

Inner Join

Inner join is the join where you will get date only in case if the applied condition gets satisfied. Here condition can be equal to, greater than or less then or not equal to. For example, is we are having two date collection first name as Employee and Second one is Department. Here date collection Employee table is having reference of departmentid. Now when we apply Inner Join, we will get all records which fulfill the condition. Now let's check the example. In this will take two list class and add some values.

List<Employee> employee = new List<Employee>();
employee.Add(new Employee { Id = 1, Name = "Rakesh", DepartmentId = 2, PhoneNo = "232323425" });
employee.Add(new Employee { Id = 2, Name = "Dhiru", DepartmentId = 3, PhoneNo = "234234234" });
employee.Add(new Employee { Id = 3, Name = "Suraj", DepartmentId = 5, PhoneNo = "433233423" });
employee.Add(new Employee { Id = 4, Name = "Rishant", DepartmentId = 1, PhoneNo = "334234322" });

List<Department> department = new List<Department>();
department.Add(new Department { Id = 1, DepartmentName = "Department 1" });
department.Add(new Department { Id = 2, DepartmentName = "Department 2" });
department.Add(new Department { Id = 3, DepartmentName = "Department 3" });
department.Add(new Department { Id = 4, DepartmentName = "Department 4" });

var empData = from e in employee
              join d in department on e.DepartmentId equals d.Id
              select new { Name = e.Name, DepartmentName = d.DepartmentName, PhoneNo = e.PhoneNo };
foreach (var item in empData)
{
    Console.WriteLine(string.Format("Name:{0}, Department:{1}, PhoneNo:{2}", item.Name, item.DepartmentName, item.PhoneNo));
}

Here in above code, I have created the object of the list of Employee class and added some values in it. Same as on other had created the object of Department class and added department detail into the list. Here one thing we need to check that the Employee table is having one column names as "DepartmentId", which is nothing but the reference of the Department table. Now the below piece of code 

join d in department on e.DepartmentId equals d.Id

In above code we have implemented the inner join. In this we have verified whether the department is present in the employee table or not, to compare equal have been used.  Now let's put a break point and run the code to check the output.

In above result we are getting three records as the departmentid in Employee table is not having one off the  departmentid.  Now press F5 to check the output.

Group Join

Group join is the join where we the first data collection defined in the query will return the data whether the second table is having the match or not. If no match found on that case, it will return null. But there will not be situation it will not return a value. In Group join we will get the result in the in a group item.

For Example: if there are two department D1 and D2, on that case query will return the result first D1 and collection of all employees and D2 with collection of all employees.  

List<Employee> employee = new List<Employee>();
employee.Add(new Employee { Id = 1, Name = "Rakesh", DepartmentId = 2, PhoneNo = "232323425" });
employee.Add(new Employee { Id = 2, Name = "Dhiru", DepartmentId = 3, PhoneNo = "234234234" });
employee.Add(new Employee { Id = 3, Name = "Suraj", DepartmentId = 5, PhoneNo = "433233423" });
employee.Add(new Employee { Id = 4, Name = "Rishant", DepartmentId = 1, PhoneNo = "334234322" });

List<Department> department = new List<Department>();
department.Add(new Department { Id = 1, DepartmentName = "Department 1" });
department.Add(new Department { Id = 2, DepartmentName = "Department 2" });
department.Add(new Department { Id = 3, DepartmentName = "Department 3" });
department.Add(new Department { Id = 4, DepartmentName = "Department 4" });

var empData = from d in department
              join e in employee on d.Id equals e.DepartmentId into emp
              select new { DepartmentName = d.DepartmentName, employeelist = emp };
foreach (var item in empData)
{
    Console.WriteLine("Department: " + item.DepartmentName);
    if (item.employeelist.Count() > 0)
    {
        foreach (var itememp in item.employeelist)
        {
            Console.WriteLine(string.Format("Name:{0}, PhoneNo:{1}", itememp.Name, itememp.PhoneNo));
        }
    }
    else
    {
        Console.WriteLine("Sorry no employee found.....");
    }
}

In above code i have taken two different list one for Employee and second for Department and added value in the list object list same as did in first example. After that check the LINQ query written for group join.

To display the result, I have used for each loop. First look for department and second for employee list as per department. 

var empData = from d in department
              join e in employee on d.Id equals e.DepartmentId into emp
              select new { DepartmentName = d.DepartmentName, employeelist = emp };

In above query we have stored the value of employee in the emp variable. In select statement instead of taken single value we have passed the variable emp. This will return the record in group as per department name. Now we have done let's run the code by putting break point and check the output.

In above result check the count for department 4, as for department 4 we are not having any employee in Employee table we are getting count "0". Here in the loop I have put a validation if no employee found under a department we display a user-friendly message. Now lets press F5 and check output.

Left Outer Join

Left outer join is the join where we definitely get the result for the first able whether the reference parameter match or not. If it does not match on that case, it will return null. In left outer join we use DefaultIfEmpty to group the result to apply the left outer join. Please check the below code. 

List<Employee> employee = new List<Employee>();
employee.Add(new Employee { Id = 1, Name = "Rakesh", DepartmentId = 2, PhoneNo = "232323425" });
employee.Add(new Employee { Id = 2, Name = "Dhiru", DepartmentId = 3, PhoneNo = "234234234" });
employee.Add(new Employee { Id = 3, Name = "Suraj", DepartmentId = 5, PhoneNo = "433233423" });
employee.Add(new Employee { Id = 4, Name = "Rishant", DepartmentId = 1, PhoneNo = "334234322" });

List<Department> department = new List<Department>();
department.Add(new Department { Id = 1, DepartmentName = "Department 1" });
department.Add(new Department { Id = 2, DepartmentName = "Department 2" });
department.Add(new Department { Id = 3, DepartmentName = "Department 3" });
department.Add(new Department { Id = 4, DepartmentName = "Department 4" });

var empData = from e in employee
              join d in department on e.DepartmentId equals d.Id into dep
              from depjoin in dep.DefaultIfEmpty()
              select new { Name = e.Name, DepartmentName = depjoin == null ? "" : depjoin.DepartmentName, PhoneNo = e.PhoneNo };
foreach (var item in empData)
{
    Console.WriteLine(string.Format("Name:{0}, Department:{1}, PhoneNo:{2}", item.Name, item.DepartmentName, item.PhoneNo));
}

In above cod i have taken two List one for Employee and second for Department. Here we are having reference of DepartmentId in employee list. Here i have used DefaultIfEmpty to perform the left join in LINQ using c#.net. Please check the below LINQ query. 

var empData = from e in employee
              join d in department on e.DepartmentId equals d.Id into dep
              from depjoin in dep.DefaultIfEmpty()
              select new { Name = e.Name, DepartmentName = depjoin == null ? "" : depjoin.DepartmentName, PhoneNo = e.PhoneNo };

In above query i have compared by equal to check the reference of department id in the employee table to get the department of the employee. After validating the reference value check the select the select item. In this I have used "DepartmentName = depjoin == null ? "" : depjoin.DepartmentName" in this validation of null I have used. In we don't user this check we will get the null reference error. If department is not available. 

Now let's run the code to check the output.

Cross Join

Cross join is the join which return all the possible combination of the records present table which we have used in the query. It will not make any different where the reference key value match or not.  So, to write Cross Join query in LINQ will use two from keywords. In cross join if we apply where clause on that case it will return the records for which the condition will qualify.

List<Employee> employee = new List<Employee>();
employee.Add(new Employee { Id = 1, Name = "Rakesh", DepartmentId = 2, PhoneNo = "232323425" });
employee.Add(new Employee { Id = 2, Name = "Dhiru", DepartmentId = 3, PhoneNo = "234234234" });
employee.Add(new Employee { Id = 3, Name = "Suraj", DepartmentId = 5, PhoneNo = "433233423" });
employee.Add(new Employee { Id = 4, Name = "Rishant", DepartmentId = 1, PhoneNo = "334234322" });

List<Department> department = new List<Department>();
department.Add(new Department { Id = 1, DepartmentName = "Department 1" });
department.Add(new Department { Id = 2, DepartmentName = "Department 2" });
department.Add(new Department { Id = 3, DepartmentName = "Department 3" });
department.Add(new Department { Id = 4, DepartmentName = "Department 4" });

var empData = from e in employee
              from d in department
              select new { Name = e.Name, DepartmentName = d.DepartmentName, PhoneNo = e.PhoneNo };
foreach (var item in empData)
{
    Console.WriteLine(string.Format("Name:{0}, Department:{1}, PhoneNo:{2}", item.Name, item.DepartmentName, item.PhoneNo));
}

In above code I have used employee and department list object to make the cross joins. Now let's put a break point and check the output.

Here in above code, we can see the combination of all the records we are getting not let press F5 to check the output.