SQL Join in LINQ (LINQ to Entity / LINQ to SQL)

This article explains various types of joins in LINQ query. Learn Inner join, Outer Join, Left Outer join, Right Outer join, Full outer join, Cross Join, Group Join in LINQ query.

Introduction

 
SQL joins are used to get data from two or more tables, based on the logical relationships between the tables. There are mainly the following four types of joins available with SQL Server:
  1. Inner join
  2. Outer Join

    • Left Outer join
    • Right Outer join
    • Full outer join
       
  3. Cross Join
  4. Group Join
joins 
 
LINQ is a full-featured query language. LINQ also offers JOIN operations. In this article, I will explain how to do these joins in LINQ.
 
Example
 
I have two tables: EmployeeMaster and DepartmentMaster. The relation between these two tables and dummy data for the tables are as shown below.
 
two tables 
 
Department table
 
Department table 
 
Employee table
 
Employee table 
 

Inner Join in LINQ

 
An inner join returns only those records that exist in the tables. Using the "join" keyword we can do an inner join using a LINQ query.
 
C# syntax
  1. using (JoinEntities Context = new JoinEntities())  
  2. {  
  3.     var innerJoin = from e in Context.EmployeeMasters  
  4.     join d in Context.DepartmentMasters on e.DepartmentId equals d.DepartmentId  
  5.     select new  
  6.     {  
  7.         EmployeeCode = e.Code,  
  8.         EmployeeName = e.Name,  
  9.         DepartmentName = d.Name  
  10.     };  
  11.     Console.WriteLine("Employee Code\tEmployee Name\tDepartment Name");  
  12.     foreach (var data in innerJoin)  
  13.     {  
  14.        Console.WriteLine(data.EmployeeCode + "\t\t" + data.EmployeeName + "\t" + data.DepartmentName);  
  15.     }  
  16. }  
Output
 
Inner Join in LINQ 
 

Left outer join in LINQ

 
A Left Outer join returns all records from the left table and the matching record from the right table. If there are no matching records in the right table then it returns null. If we want to do a Left Outer join in LINQ then we must use the keyword "into" and method "DefaultIfEmpty".
 
C# syntax
  1. using (JoinEntities Context = new JoinEntities())  
  2. {  
  3.     var leftOuterJoin = from e in Context.EmployeeMasters  
  4.     join d in Context.DepartmentMasters on e.DepartmentId equals d.DepartmentId into dept  
  5.     from department in dept.DefaultIfEmpty()  
  6.     select new  
  7.     {  
  8.         EmployeeCode = e.Code,  
  9.         EmployeeName = e.Name,  
  10.         DepartmentName = department.Name  
  11.     };  
  12.     Console.WriteLine("Employee Code\tEmployee Name\tDepartment Name");  
  13.     foreach (var data in leftOuterJoin)  
  14.     {  
  15.         Console.WriteLine(data.EmployeeCode + "\t\t" + data.EmployeeName + "\t" + data.DepartmentName);  
  16.     }  
  17. }    
Output
 
Left outer join in LINQ 
 

Right outer join in LINQ

 
A right outer join is not possible with LINQ. LINQ only supports left outer joins. If we swap the tables and do a left outer join then we can get the behavior of a right outer join.
 
C# syntax
  1. using (JoinEntities Context = new JoinEntities())  
  2. {  
  3.     var rightOuterJoin = from d in Context.DepartmentMasters  
  4.     join e in Context.EmployeeMasters on d.DepartmentId equals e.DepartmentId into emp  
  5.     from employee in emp.DefaultIfEmpty()  
  6.     select new  
  7.     {  
  8.         EmployeeCode = employee.Code,  
  9.         EmployeeName = employee.Name,  
  10.         DepartmentName = d.Name  
  11.     };  
  12.     Console.WriteLine("Employee Code\tEmployee Name\tDepartment Name");  
  13.     foreach (var data in rightOuterJoin)  
  14.     {  
  15.         Console.WriteLine(data.EmployeeCode + "\t\t" + data.EmployeeName + "\t" + data.DepartmentName);  
  16.     }  

Output
 
Right outer join in LINQ 
 

Full outer join in LINQ

 
A full outer join is a logical union of a left outer join and a right outer join. LINQ does not support full outer joins directly, the same as right outer joins.
 
C# syntax
  1. using (JoinEntities Context = new JoinEntities())  
  2. {  
  3.     var leftOuterJoin = from e in Context.EmployeeMasters  
  4.     join d in Context.DepartmentMasters on e.DepartmentId equals d.DepartmentId into dept  
  5.     from department in dept.DefaultIfEmpty()  
  6.     select new  
  7.     {  
  8.         EmployeeCode = e.Code,  
  9.         EmployeeName = e.Name,  
  10.         DepartmentName = department.Name  
  11.     };  
  12.     var rightOuterJoin = from d in Context.DepartmentMasters  
  13.     join e in Context.EmployeeMasters on d.DepartmentId equals e.DepartmentId into emp  
  14.     from employee in emp.DefaultIfEmpty()  
  15.     select new  
  16.     {  
  17.         EmployeeCode = employee.Code,  
  18.         EmployeeName = employee.Name,  
  19.         DepartmentName = d.Name  
  20.     };  
  21.     leftOuterJoin = leftOuterJoin.Union(rightOuterJoin);  
  22.     Console.WriteLine("Employee Code\tEmployee Name\tDepartment Name");  
  23.     foreach (var data in leftOuterJoin)  
  24.     {  
  25.         if(!string.IsNullOrEmpty(data.EmployeeCode))  
  26.         Console.WriteLine(data.EmployeeCode + "\t\t" + data.EmployeeName + "\t" + data.DepartmentName);  
  27.         else  
  28.         Console.WriteLine(data.EmployeeCode + "\t\t" + data.EmployeeName + "\t\t" + data.DepartmentName);  
  29.     }  

Output
 
Full outer join in LINQ 
 

Cross Join in LINQ

 
A cross join is also known as a Cartesian Join. This join does not require any condition in the join but LINQ does not allow using the "join" keyword without any condition. Using two from clauses we can do a cross join.
 
C# syntax
  1. using (JoinEntities Context = new JoinEntities())  
  2. {  
  3.     var crossJoin = from e in Context.EmployeeMasters  
  4.     from d in Context.DepartmentMasters  
  5.     select new  
  6.     {  
  7.         EmployeeCode = e.Code,  
  8.         EmployeeName = e.Name,  
  9.         DepartmentName = d.Name  
  10.     };  
  11.     Console.WriteLine("Employee Code\tEmployee Name\tDepartment Name");  
  12.     foreach (var data in crossJoin)  
  13.     {  
  14.         Console.WriteLine(data.EmployeeCode + "\t\t" + data.EmployeeName + "\t" + data.DepartmentName);  
  15.     }  

Output
 
Cross Join in LINQ 
 

Group join in LINQ

 
Generally, in SQL, a group join can be done using a "Group by" clause. There are two ways to do a group join in LINQ.
 
1. Using INTO keyword
 
C# syntax
  1. using (JoinEntities Context = new JoinEntities())  
  2. {  
  3.     var groupJoin = from d in Context.DepartmentMasters  
  4.     join e in Context.EmployeeMasters on d.DepartmentId equals e.DepartmentId into emp  
  5.     select new  
  6.     {  
  7.         DeparmentCode = d.Code,  
  8.         DeparmentName = d.Name,  
  9.         Employee = emp  
  10.     };  
  11.     foreach (var data in groupJoin)  
  12.     {  
  13.         Console.WriteLine("Department:" + data.DeparmentCode + " - " + data.DeparmentName);  
  14.         if (data.Employee != null && data.Employee.Count() > 0)  
  15.         {  
  16.         Console.WriteLine("Employee Code\tEmployee Name");  
  17.         foreach (var empData in data.Employee)  
  18.         {  
  19.             Console.WriteLine(empData.Code + "\t\t" + empData.Name);  
  20.         }  
  21.     }  
  22.     else  
  23.     {  
  24.     Console.WriteLine("Department has no employee.");  
  25.     }  
  26.         Console.WriteLine("");  
  27.         Console.WriteLine("");  
  28.     }  

2. Using sub query
  1. using (JoinEntities Context = new JoinEntities())  
  2. {  
  3.     var groupJoin = from d in Context.DepartmentMasters  
  4.     select new  
  5.     {  
  6.         DeparmentCode = d.Code,  
  7.         DeparmentName = d.Name,  
  8.         Employee = (from e in Context.EmployeeMasters  
  9.         where e.DepartmentId == d.DepartmentId  
  10.         select e)  
  11.     };  
  12.     foreach (var data in groupJoin)  
  13.     {  
  14.         Console.WriteLine("Department:" + data.DeparmentCode + " - " + data.DeparmentName);  
  15.         var employees = data.Employee as IEnumerable<EmployeeMaster>;  
  16.         if (employees != null && employees.Count()> 0)  
  17.         {  
  18.             Console.WriteLine("Employee Code\tEmployee Name");  
  19.             foreach (var empData in employees)  
  20.             {  
  21.                Console.WriteLine(empData.Code + "\t\t" + empData.Name);  
  22.             }  
  23.        }  
  24.        else  
  25.        {  
  26.            Console.WriteLine("Department has no employee.");  
  27.        }  
  28.        Console.WriteLine("");  
  29.        Console.WriteLine("");  
  30.     }  
  31. }   
Output
 
Group join in LINQ 
 

Summary

 
This article may help you to implement a join in LINQ.