Joins In LINQ

This blog explains joins, using LINQ in C#.

In the blog, I am writing about Group Join. 

 

1. Group Join - Group Join produces hierarchical data structures. Each element from the first collection is paired       with a set of correlated elements from the second collection.

 

2. When we prefer Group Join with Extension method, the syntax used is GroupJoin() extension method.

 

3. Open Visual Studio and create a New Project console Application.

 

4. Add two Class files, name it as Department.cs and Employee.cs.

 

5. In the Department.cs, write the code, given below.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace ConsoleApplication64 {
 public class Department {
  public int DeptId {
   get;
   set;
  }
  public string DeptName {
   get;
   set;
  }
  public static List < Department > GetAllDepartments() {
   return new List < Department > () {
    new Department {
     DeptId = 1, DeptName = "IT"
    },
    new Department {
     DeptId = 2, DeptName = "Accounting"
    },
    new Department {
     DeptId = 3, DeptName = "HR"
    },
   };
  }
 }
}

6. It has two properties DeptId, DeptName and there is a Static Method GetAllDepartments(), which returns the list of Department object. It has three departments, which are IT, Accounting and HR.

 

7. The code written in Employee class is given below.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace ConsoleApplication64 {
 public class Employee {
  public int Id {
   get;
   set;
  }
  public string Name {
   get;
   set;
  }
  public int DepartmentId {
   get;
   set;
  }
  public static List < Employee > GetAllEmployeesByDepartment() {
   return new List < Employee > () {
    new Employee {
     Id = 1, Name = "Sachin", DepartmentId = 1
    },
    new Employee {
     Id = 2, Name = "Rahul", DepartmentId = 1
    },
    new Employee {
     Id = 3, Name = "Sourav", DepartmentId = 2
    },
    new Employee {
     Id = 4, Name = "Ricky", DepartmentId = 2
    },
    new Employee {
     Id = 5, Name = "Adam", DepartmentId = 2
    },
    new Employee {
     Id = 6, Name = "Brian", DepartmentId = 2
    },
    new Employee {
     Id = 7, Name = "Glenn", DepartmentId = 3
    },
    new Employee {
     Id = 8, Name = "Chris", DepartmentId = 1
    },
    new Employee {
     Id = 9, Name = "Anil", DepartmentId = 3
    },
    new Employee {
     Id = 10, Name = "Lionnel", DepartmentId = 3
    },
    new Employee {
     Id = 11, Name = "Christiano", DepartmentId = 3
    },
   };
  }
 }
}

8. Employee Class contains three properties, which are Id and Name of employees and DepartmentId. Here, DepartmentId is the Join Key. If we have to check which Department the Employee belongs to, we take the DepartmentId property from Employee and match it with the DeptId property of Department.

 

9. Thus, within these two classes DepartmentId from Employee and DeptId from Department, which are the Join keys. Also, in Employee class there is a method GetAllEmployeesByDepartment(), which returns the list of the employee object. 

 

10. We are going to retrieve Employees by Department, using a Group Join, and print the Department Name and Name of Employees belonging to that Department.

 

11. GetAllDepartments is going to give us the list of Department objects. Now, we want to group join the list of Department with the list of Employee, so we are going to the use the GroupJoin() Extension method.

 

Write the code, given below in Main Class.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace ConsoleApplication64 {
 class Program {
  static void Main(string[] args) {
   var employeebydept = Department.GetAllDepartments().GroupJoin(Employee.GetAllEmployeesByDepartment(),
    d => d.DeptId, e => e.DepartmentId, (department, employees) =>
    new {
     Departments = department,
      Employees = employees
    });

   foreach(var dept in employeebydept) {
    Console.WriteLine(dept.Departments.DeptName);
    foreach(var employee in dept.Employees) {
     Console.Write(" ");
     Console.WriteLine(employee.Name);
    }
   }
  }
 }
}

12. Group Join has 4 parameters, where first parameter is inner and its type is IEnumerable<TInner>.

 

13. In this, we want to join Employees with Department. We have the list of Departments, so the sequence is the list of employees. List of Department is the outer sequence, which is group joined with the inner sequence, which is the List of Employees.

 

14. Second Parameter is outerKeySelector, which is Join key property. Here, we provide the Join key for the outer sequence, which is Department. Thus, I have passed DeptId as a second parameter and next parameter is the innerKeySelector. Here, we provide Join key for the inner sequence, which is Employee here. DepartmentId from Employee is passed as third parameter. The final parameter is the resultSelector, which provides what we want as a result of Group Join. We need the list of Department and Employee.

 

15. Finally, we have got the list of Employees by Department in the variable employeebydept and we have to just print it, using foreach loop.

Console.Write(dept.Departments.DeptName);
This will print the Department Name and within each Department we are going to have Employee

foreach(var employee in dept.Employees) {
 Console.Write(" ");
 Console.WriteLine(employee.Name);
}

This is printing the employees belonging to a particular department.

 

Here, we have used an extension syntax, i.e. GroupJoin method.