In this post let us see how we can handle Left Join and Right Join when using LINQ. There are no keywords defined in C#, we have to use DefaultIfEmpty() function to get the desired result.
Let us see how we can achieve it.To make you understand better I use a Employee -> Department realation to explain.First we shall create two classes namely Employee and Departmentclass Employee {public string Name { get; set; }public int ID { get; set; }public int DeptID { get; set; }}class Department {public int ID { get; set; }public string Name { get; set; }}
Lets create some objects of both the classes and fill some dummy data in it.Employee emp1 = new Employee() { ID = 1, Name = “A”, DeptID = 1};Employee emp2 = new Employee() { ID = 2, Name = “B”, DeptID = 1};Employee emp3 = new Employee() { ID = 3, Name = “C”, DeptID = 1 };Employee emp4 = new Employee() { ID = 4, Name = “D”, DeptID = 2 };Employee emp5 = new Employee() { ID = 5, Name = “E”, DeptID = 2 };Employee emp6 = new Employee() { ID = 6, Name = “F”, DeptID = 2 };Employee emp7 = new Employee() { ID = 7, Name = “G”, DeptID = 6 };Employee emp8 = new Employee() { ID = 8, Name = “H”, DeptID = 3 };Employee emp9 = new Employee() { ID = 9, Name = “I”, DeptID = 3 };Employee emp10 = new Employee() { ID = 10, Name = “J”, DeptID = 7};Employee emp11 = new Employee() { ID = 11, Name = “K”, DeptID = 7};Employee emp12 = new Employee() { ID = 12, Name = “L”, DeptID = 5};Department Dept1 = new Department() { ID = 1, Name = “Development”};Department Dept2 = new Department() { ID = 2, Name = “Testing”};Department Dept3 = new Department() { ID = 3, Name = “Marketing”};Department Dept4 = new Department() { ID = 4, Name = “Support”};List
So we finish loading the objects into ListOfEmployees and ListOfDepartments, using this lists we shall see how we can join them to get the results.
First let us see what would be the query in SQL if we had the same structure in our tables.
For Left join and right join we would have used the query–Left Join in SQLselect Emp.Name, Dept.Name from Employee Emp left join Department Dept onEmp.DeptID = Dept.ID–Right Join In SQLselect Emp.Name, Dept.Name from Employee Emp right join Department Dept onEmp.DeptID = Dept.ID
Using LINQ, Left Join can be acheived as followsvar LeftJoin = from emp in ListOfEmployeesjoin dept in ListOfDepartmenton emp.DeptID equals dept.ID into JoinedEmpDept from dept in JoinedEmpDept.DefaultIfEmpty()select new {EmployeeName = emp.Name,DepartmentName = dept != null ? dept.Name : null };
And for Right Join there is no pretty difference, we just need to reverse the joining in first 2 lines. Here it followsvar RightJoin = from dept in ListOfDepartmentjoin employee in ListOfEmployeeson dept.ID equals employee.DeptID into joinDeptEmpfrom employee in joinDeptEmp.DefaultIfEmpty()select new {EmployeeName = employee != null ? employee.Name : null,DepartmentName = dept.Name};
