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:
- Inner join
- Outer Join
- Left Outer join
- Right Outer join
- Full outer join
- Cross Join
- Group Join
![SQL 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
using (JoinEntities Context = new JoinEntities())
{
var innerJoin = from e in Context.EmployeeMasters
join d in Context.DepartmentMasters on e.DepartmentId equals d.DepartmentId
select new
{
EmployeeCode = e.Code,
EmployeeName = e.Name,
DepartmentName = d.Name
};
Console.WriteLine("Employee Code\tEmployee Name\tDepartment Name");
foreach (var data in innerJoin)
{
Console.WriteLine(data.EmployeeCode + "\t\t" + data.EmployeeName + "\t" + data.DepartmentName);
}
}
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
using (JoinEntities Context = new JoinEntities())
{
var leftOuterJoin = from e in Context.EmployeeMasters
join d in Context.DepartmentMasters on e.DepartmentId equals d.DepartmentId into dept
from department in dept.DefaultIfEmpty()
select new
{
EmployeeCode = e.Code,
EmployeeName = e.Name,
DepartmentName = department.Name
};
Console.WriteLine("Employee Code\tEmployee Name\tDepartment Name");
foreach (var data in leftOuterJoin)
{
Console.WriteLine(data.EmployeeCode + "\t\t" + data.EmployeeName + "\t" + data.DepartmentName);
}
}
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
using (JoinEntities Context = new JoinEntities())
{
var rightOuterJoin = from d in Context.DepartmentMasters
join e in Context.EmployeeMasters on d.DepartmentId equals e.DepartmentId into emp
from employee in emp.DefaultIfEmpty()
select new
{
EmployeeCode = employee.Code,
EmployeeName = employee.Name,
DepartmentName = d.Name
};
Console.WriteLine("Employee Code\tEmployee Name\tDepartment Name");
foreach (var data in rightOuterJoin)
{
Console.WriteLine(data.EmployeeCode + "\t\t" + data.EmployeeName + "\t" + data.DepartmentName);
}
}
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
using (JoinEntities Context = new JoinEntities())
{
var leftOuterJoin = from e in Context.EmployeeMasters
join d in Context.DepartmentMasters on e.DepartmentId equals d.DepartmentId into dept
from department in dept.DefaultIfEmpty()
select new
{
EmployeeCode = e.Code,
EmployeeName = e.Name,
DepartmentName = department.Name
};
var rightOuterJoin = from d in Context.DepartmentMasters
join e in Context.EmployeeMasters on d.DepartmentId equals e.DepartmentId into emp
from employee in emp.DefaultIfEmpty()
select new
{
EmployeeCode = employee.Code,
EmployeeName = employee.Name,
DepartmentName = d.Name
};
leftOuterJoin = leftOuterJoin.Union(rightOuterJoin);
Console.WriteLine("Employee Code\tEmployee Name\tDepartment Name");
foreach (var data in leftOuterJoin)
{
if(!string.IsNullOrEmpty(data.EmployeeCode))
Console.WriteLine(data.EmployeeCode + "\t\t" + data.EmployeeName + "\t" + data.DepartmentName);
else
Console.WriteLine(data.EmployeeCode + "\t\t" + data.EmployeeName + "\t\t" + data.DepartmentName);
}
}
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
using (JoinEntities Context = new JoinEntities())
{
var crossJoin = from e in Context.EmployeeMasters
from d in Context.DepartmentMasters
select new
{
EmployeeCode = e.Code,
EmployeeName = e.Name,
DepartmentName = d.Name
};
Console.WriteLine("Employee Code\tEmployee Name\tDepartment Name");
foreach (var data in crossJoin)
{
Console.WriteLine(data.EmployeeCode + "\t\t" + data.EmployeeName + "\t" + data.DepartmentName);
}
}
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
using (JoinEntities Context = new JoinEntities())
{
var groupJoin = from d in Context.DepartmentMasters
join e in Context.EmployeeMasters on d.DepartmentId equals e.DepartmentId into emp
select new
{
DeparmentCode = d.Code,
DeparmentName = d.Name,
Employee = emp
};
foreach (var data in groupJoin)
{
Console.WriteLine("Department:" + data.DeparmentCode + " - " + data.DeparmentName);
if (data.Employee != null && data.Employee.Count() > 0)
{
Console.WriteLine("Employee Code\tEmployee Name");
foreach (var empData in data.Employee)
{
Console.WriteLine(empData.Code + "\t\t" + empData.Name);
}
}
else
{
Console.WriteLine("Department has no employee.");
}
Console.WriteLine("");
Console.WriteLine("");
}
}
2. Using sub query
using (JoinEntities Context = new JoinEntities())
{
var groupJoin = from d in Context.DepartmentMasters
select new
{
DeparmentCode = d.Code,
DeparmentName = d.Name,
Employee = (from e in Context.EmployeeMasters
where e.DepartmentId == d.DepartmentId
select e)
};
foreach (var data in groupJoin)
{
Console.WriteLine("Department:" + data.DeparmentCode + " - " + data.DeparmentName);
var employees = data.Employee as IEnumerable<EmployeeMaster>;
if (employees != null && employees.Count()> 0)
{
Console.WriteLine("Employee Code\tEmployee Name");
foreach (var empData in employees)
{
Console.WriteLine(empData.Code + "\t\t" + empData.Name);
}
}
else
{
Console.WriteLine("Department has no employee.");
}
Console.WriteLine("");
Console.WriteLine("");
}
}
Output
![Group join in LINQ]()
Summary
This article may help you to implement a join in LINQ.