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