Post
Ask Question

Joins In LINQ

A LINQ JOIN keyword is used to combine rows from two or more tables, based on a common field between them. Like SQL Joins, the Linq is also provided some keywords to achieve Inner Join and Outer Join. As we know from SQL outer join is divided into 2 groups that is Left Outer Join and Right Outer Join. Here, in this tip, we learn how to achieve these joins in LINQ. Let us discuss what is inner join.
  • INNER JOIN: Inner Join only return matched records between two or more tables based on a common field between these tables.

  • LEFT OUTER JOIN: Return all rows from the left table, and the matched rows from the right table.

  • RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table.

  • FULL JOIN: Return all rows when there is a match in ONE of the tables.
Here in this console application, I have two lists (EmployeeList and DepartmentList). Considering these 2 lists as 2 different tables, I am joining these 2 tables on the basis of a common column, i.e., ID.
 
INNER JOIN
 
  
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Text;  
  5. using System.Threading.Tasks;  
  6.   
  7. namespace Joins  
  8. {  
  9.     public class Employee  
  10.     {  
  11.         public int Id { get; set; }  
  12.         public int age { get; set; }  
  13.         public string name { get; set; }  
  14.         public string gender { get; set; }  
  15.     }  
  16.     public class Department  
  17.     {  
  18.         public int id { get; set; }  
  19.         public string Departments { get; set; }  
  20.         public string Location { get; set; }  
  21.     }  
  22.     class Demo  
  23.     {      
  24.         static void Main(string[] args)  
  25.         {  
  26.             List<Employee> li = new List<Employee>();  
  27.             li.Add(new Employee   
  28.             { Id = 1, age = 19, name = "Ritesh", gender = "M" });  
  29.             li.Add(new Employee   
  30.             { Id = 2, age = 20, name = "sujit", gender = "M" });  
  31.             li.Add(new Employee   
  32.             { Id = 3, age = 23, name = "Kabir", gender = "F" });  
  33.             li.Add(new Employee   
  34.             { Id = 4, age = 3, name = "mantu", gender = "F" });  
  35.             li.Add(new Employee   
  36.             { Id = 5, age = 24, name = "Kamlesh", gender = "M" });  
  37.             li.Add(new Employee   
  38.             { Id = 6, age = 28, name = "Manoj", gender = "M" });  
  39.   
  40.   
  41.             List<Department> Deli = new List<Department>();  
  42.             Deli.Add(new Department   
  43.             { id = 2, Departments = "IT", Location = "Bangalore" });  
  44.             Deli.Add(new Department   
  45.             { id = 8, Departments = "IT", Location = "Bangalore" });  
  46.             Deli.Add(new Department   
  47.             { id = 3, Departments = "HR", Location = "Bangalore" });  
  48.             Deli.Add(new Department   
  49.             { id = 7, Departments = "HR", Location = "Bangalore" });  
  50.             Deli.Add(new Department   
  51.             { id = 6, Departments = "Account", Location = "Bangalore" });  
  52.             var result = from emp in li  
  53.                          join  
  54.                           de in Deli  
  55.                          on emp.Id equals de.id  
  56.                          select new  
  57.                              {  
  58.                                  EmployeeId = emp.Id,  
  59.                                  EmployeeName = emp.name,  
  60.                                  Department = de.Departments,  
  61.                                  Location = de.Location  
  62.                              };  
  63.   
  64.             Console.WriteLine(" ID\t\tName\t\t DepartmentName \t\tLocation");  
  65.               
  66.             foreach (var obj in result)  
  67.             {  
  68.                 Console.WriteLine(obj.EmployeeId + "\t\t" + obj.EmployeeName +   
  69.                 "\t\t" + obj.Department + "\t\t\t\t" + obj.Location);  
  70.             }            
  71.   
  72.             Console.ReadLine();  
  73.         }  
  74.     }  
  75. }  
Now here is the result produced.
 
 
LEFT OUTER JOIN
 
 
 
Here is the program for Left Outer join. 
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Text;  
  5. using System.Threading.Tasks;  
  6.   
  7. namespace Joins  
  8. {  
  9.     public class Employee  
  10.     {  
  11.         public int Id { get; set; }  
  12.         public int age { get; set; }  
  13.         public string name { get; set; }  
  14.         public string gender { get; set; }  
  15.     }  
  16.     public class Department  
  17.     {  
  18.         public int id { get; set; }  
  19.         public string Departments { get; set; }  
  20.         public string Location { get; set; }  
  21.     }  
  22.     class Demo  
  23.     {  
  24.         static void Main(string[] args)  
  25.         {  
  26.             List<Employee> li = new List<Employee>();  
  27.             li.Add(new Employee { Id = 1, age = 19, name = "Ritesh", gender = "M" });  
  28.             li.Add(new Employee { Id = 2, age = 20, name = "sujit", gender = "M" });  
  29.             li.Add(new Employee { Id = 3, age = 23, name = "Kabir", gender = "F" });  
  30.             li.Add(new Employee { Id = 4, age = 3, name = "mantu", gender = "F" });  
  31.             li.Add(new Employee { Id = 5, age = 24, name = "Kamlesh", gender = "M" });  
  32.             li.Add(new Employee { Id = 6, age = 28, name = "Manoj", gender = "M" });  
  33.   
  34.             List<Department> Deli = new List<Department>();  
  35.             Deli.Add(new Department { id = 2, Departments = "IT", Location = "Bangalore" });  
  36.             Deli.Add(new Department { id = 8, Departments = "IT", Location = "Ranchi" });  
  37.             Deli.Add(new Department { id = 3, Departments = "HR", Location = "bihar" });  
  38.             Deli.Add(new Department { id = 7, Departments = "HR", Location = "bhubaneshwar" });  
  39.             Deli.Add(new Department { id = 6, Departments = "Account", Location = "keonjhar" });  
  40.   
  41.             var result = from emp in li  
  42.                          join  
  43.                          de in Deli  
  44.                          on emp.Id equals de.id into tempstorage  
  45.                          from dx in tempstorage.DefaultIfEmpty()  
  46.                          select new  
  47.                          {  
  48.                              EmployeeId = emp.Id,  
  49.                              EmployeeName = emp.name,  
  50.   
  51.                              Department = (dx != null) ? dx.Departments : "NULL",  
  52.                              Location = (dx != null) ? dx.Location : "NULL"  
  53.                          };  
  54.   
  55.             Console.WriteLine("ID\t\tName\t\t DepartmentName \t\tLocation");  
  56.   
  57.             foreach (var obj in result)  
  58.             {  
  59.                 Console.WriteLine(obj.EmployeeId + "\t\t" +  
  60.                 obj.EmployeeName + "\t\t" + obj.Department + "\t\t\t\t" + obj.Location);  
  61.             }  
  62.   
  63.             Console.ReadLine();  
  64.         }  
  65.     }  
  66. }  
The output produced can be seen below:
 
 
RIGHT OUTER JOIN
 
 
 
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Text;  
  5. using System.Threading.Tasks;  
  6.   
  7. namespace Joins  
  8. {  
  9.     public class Employee  
  10.     {  
  11.         public int Id { get; set; }  
  12.         public int age { get; set; }  
  13.         public string name { get; set; }  
  14.         public string gender { get; set; }  
  15.     }  
  16.     public class Department  
  17.     {  
  18.         public int id { get; set; }  
  19.         public string Departments { get; set; }  
  20.         public string Location { get; set; }  
  21.     }  
  22.     class Demo  
  23.     {    
  24.         static void Main(string[] args)  
  25.         {  
  26.             List<Employee> li = new List<Employee>();  
  27.             li.Add(new Employee   
  28.             { Id = 1, age = 19, name = "Ritesh", gender = "M" });  
  29.             li.Add(new Employee   
  30.             { Id = 2, age = 20, name = "sujit", gender = "M" });  
  31.             li.Add(new Employee   
  32.             { Id = 3, age = 23, name = "Kabir", gender = "F" });  
  33.             li.Add(new Employee   
  34.             { Id = 4, age = 3, name = "mantu", gender = "F" });  
  35.             li.Add(new Employee   
  36.             { Id = 5, age = 24, name = "Kamlesh", gender = "M" });  
  37.             li.Add(new Employee   
  38.             { Id = 6, age = 28, name = "Manoj", gender = "M" });  
  39.   
  40.             List<Department> Deli = new List<Department>();  
  41.             Deli.Add(new Department   
  42.             { id = 2, Departments = "IT", Location = "Bangalore" });  
  43.             Deli.Add(new Department   
  44.             { id = 8, Departments = "IT", Location = "Bangalore" });  
  45.             Deli.Add(new Department   
  46.             { id = 3, Departments = "HR", Location = "Bangalore" });  
  47.             Deli.Add(new Department   
  48.             { id = 7, Departments = "HR", Location = "Bangalore" });  
  49.             Deli.Add(new Department   
  50.             { id = 6, Departments = "Account", Location = "Bangalore" });  
  51.             
  52.             var result = from  
  53.                              de in Deli  
  54.                              join  
  55.                          emp in li                           
  56.                            
  57.                          on de.id equals emp.Id into tempstorage  
  58.                          from dx in tempstorage.DefaultIfEmpty()  
  59.                          select new  
  60.                          {  
  61.                              EmployeeId = (dx != null) ? dx.Id :0,  
  62.                              EmployeeName = (dx != null) ? dx.name : "NULL",  
  63.                                
  64.                              Department = de.Departments,  
  65.                              Location = de.Location  
  66.                          };  
  67.   
  68.             Console.WriteLine("ID\t\tName\t\t DepartmentName \t\tLocation");  
  69.               
  70.             foreach (var obj in result)  
  71.             {  
  72.                 Console.WriteLine(obj.EmployeeId + "\t\t" + obj.EmployeeName +   
  73.                 "\t\t" + obj.Department + "\t\t\t\t" + obj.Location);  
  74.             }  
  75.   
  76.             Console.ReadLine();  
  77.         }  
  78.     }  
  79. }  
 
 
So in this, we can see 2 different tables in different ways in LINQ. 


Similar Articles