Left Join, Right Join Using LINQ

Hi Friends,

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 Department

class 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<Employee> ListOfEmployees = new List<Employee>();
ListOfEmployees.AddRange((new Employee[] { emp1, emp2, emp3, emp4, emp5, emp6, emp7,
emp8, emp9, emp10, emp11, emp12 }));

List<Department> ListOfDepartment = new List<Department>();
ListOfDepartment.AddRange( new Department[]{ Dept1,Dept2,Dept3,Dept4});

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 SQL
select Emp.Name, Dept.Name from Employee Emp left join Department Dept on
Emp.DeptID = Dept.ID

--Right Join In SQL
select Emp.Name, Dept.Name from Employee Emp right join Department Dept on
Emp.DeptID = Dept.ID

Using LINQ, Left Join can be acheived as follows

var LeftJoin = from emp in ListOfEmployees
join dept in ListOfDepartment
on 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 follows

var RightJoin = from dept in ListOfDepartment
join employee in ListOfEmployees
on dept.ID equals employee.DeptID into joinDeptEmp
from employee in joinDeptEmp.DefaultIfEmpty()
select new                            
{
EmployeeName = employee != null ? employee.Name : null,
DepartmentName = dept.Name
};

Do you believe it will work ??? lets check it by displaying,

Console.WriteLine(string.Join("n" , LeftJoin.Select(emp => " Employee Name = " +
emp.EmployeeName + ", Department Name = " + emp.DepartmentName).ToArray<string>()));

Console.WriteLine(string.Join("n", RightJoin.Select(emp => " Employee Name = " +
emp.EmployeeName + ", Department Name = " + emp.DepartmentName).ToArray<string>()));

Output of Left Join in LINQ

Output of Right Join in LINQ

Great, it has worked. So friends in this way you can handle Left and Right Joins in LINQ, hope you understood the sample.

Next -> Multiple List Left Join

Happy Learning

Join in LINQ

Hi Friends,

In this post lets us try to understand how Join is handled using LINQ. We will take a sample that would make us clear what we are trying to acheive.

Consider a department that handles a type of category of items, it can be active or not. The class of the department will look like

public class Dept    
{
public int ID { get; set; }
public int CategoryID { get; set; }
public bool IsActive { get; set; }
}

After creating the department class lets move to create the Items class

public class Item    
{
public int ID { get; set; }
public int DeptID { get; set; }
public int CategoryID { get; set; }
public bool IsActive { get; set; }
public int Quantity { get; set; }
}

First let us create a list of Department and Items

//Create Items            
Item itm1 = new Item() { ID = 1, DeptID = 1, CategoryID = 1, IsActive = true, Quantity = 30 };
Item itm2 = new Item() { ID = 2, DeptID = 1, CategoryID = 1, IsActive = false, Quantity = 5 };
Item itm3 = new Item() { ID = 3, DeptID = 1, CategoryID = 2, IsActive = true, Quantity = 2 };
Item itm4 = new Item() { ID = 4, DeptID = 1, CategoryID = 2, IsActive = false, Quantity = 0 };

Item itm5 = new Item() { ID = 5, DeptID = 2, CategoryID = 1, IsActive = true, Quantity = 0 };
Item itm6 = new Item() { ID = 6, DeptID = 2, CategoryID = 1, IsActive = false, Quantity = 0 };
Item itm7 = new Item() { ID = 7, DeptID = 2, CategoryID = 2, IsActive = true, Quantity = 20 };
Item itm8 = new Item() { ID = 8, DeptID = 2, CategoryID = 2, IsActive = false, Quantity = 5 };

Item[] ListOfItems = { itm1, itm2, itm3, itm4, itm5, itm6, itm7, itm8 };

//Create Departments            
Dept dept1 = new Dept() { ID = 1, CategoryID = 1, IsActive = true };
Dept dept2 = new Dept() { ID = 1, CategoryID = 1, IsActive = false};
Dept dept3 = new Dept() { ID = 1, CategoryID = 2, IsActive = true };
Dept dept4 = new Dept() { ID = 1, CategoryID = 2, IsActive = false };

Dept dept5 = new Dept() { ID = 2, CategoryID = 1, IsActive = true };
Dept dept6 = new Dept() { ID = 2, CategoryID = 1, IsActive = false };
Dept dept7 = new Dept() { ID = 2, CategoryID = 2, IsActive = true };
Dept dept8 = new Dept() { ID = 2, CategoryID = 2, IsActive = false };

Dept[] ListOfDept = { dept1, dept2, dept3, dept4,dept5,dept6,dept7,dept8 };

Lets move furthur to check how these two classes can be joined to derive the result

Join single column-> No of items in Department 1:
We will join the two classes on department id and get the the sum of items present in department 1.

//Single Join            
//List No of items in Dept 1            
int NoOfItems = (from dept in ListOfDept
join item in ListOfItems
on dept.ID equals item.DeptID 
where dept.ID == 1
select new { item.ID, item.Quantity }).Distinct().Sum(itm => itm.Quantity);

In the above code I am filtering the same records that are returned due to equijoin.

Join multiple columns -> Get the item * and dept * for the following criteria
Lets join the dept and item objects on the following criteria

Relation between Department and Items

and add a extra condition for Item.Quantity > 0 and retrive the values

First let us see how the above result are acheived without using join to make you clearly understand the difference between using and not using the join

var WithoutJoin = from dept in ListOfDept
from item in ListOfItems
where dept.ID == item.DeptID &&
dept.CategoryID == item.CategoryID &&
dept.IsActive == item.IsActive &&
item.IsActive == true &&
item.Quantity > 0
select new { item, dept };

Now lets retrive the same values using join

var WithJoin = from dept in ListOfDept
join item in ListOfItems
on new { deptid = dept.ID, catID = dept.CategoryID , isActive = dept.IsActive } equals
new { deptid = item.DeptID, catID = item.CategoryID, isActive = item.IsActive }
where item.IsActive == true && item.Quantity > 0
select new { item, dept };

Hope you have understood how to join single column and join multiple columns using LINQ. If you need any clarification please feel free.

Happy Learning