Back to: LINQ Tutorial For Beginners and Professionals
Full Outer Join in LINQ with Examples
In this article, I am going to discuss Full Outer Join in LINQ with Examples using both Method and Query Syntax. Please read our previous article where we discussed LINQ Left Outer Join in C# with Examples.
Full Outer Join in LINQ
The Full Join is used to retrieve all the matching records from both the data sources involved in the join as well as all the non-matching records from both data sources. The Unmatching data in such cases will take the default value i.e. NULL.
Like the Right Outer Join, LINQ also does not support Full Outer Join directly. We can implement the Full Outer Join in LINQ in a different mapper. The Full Outer Join in LINQ is the UNION of Left Outer Join and Right Outer Join. If this is not clear at the moment, then don’t worry we will try to understand this with some examples.
Examples to Understand Full Outer Join in LINQ:
Let us understand LINQ Full Join with some Examples using C# Language. To understand Full Outer Join, we are going to use the following Employee and Department Data Sources. So, first, create a class file with the name Employee.cs and then copy and paste the following code into it. The following Employee class has 3 properties i.e. Id, Name, and DepartmentId, and one method i.e. GetAllEmployees() which is going to return a collection of Employees which is going to be one of our data sources.
using System.Collections.Generic;
namespace LINQFullOuterJoin
{
public class Employee
{
public int ID { get; set; }
public string Name { get; set; }
public int DepartmentId { get; set; }
public static List<Employee> GetAllEmployees()
{
return new List<Employee>()
{
new Employee { ID = 1, Name = "Preety", DepartmentId = 10},
new Employee { ID = 2, Name = "Priyanka", DepartmentId =20},
new Employee { ID = 3, Name = "Anurag", DepartmentId = 0},
new Employee { ID = 4, Name = "Pranaya", DepartmentId = 0},
new Employee { ID = 5, Name = "Hina", DepartmentId = 10},
new Employee { ID = 6, Name = "Sambit", DepartmentId = 30},
new Employee { ID = 7, Name = "Mahesh", DepartmentId = 30}
};
}
}
}
Next, create another class file with the name Department.cs and then copy and paste the following code into it. This class is having 2 properties i.e. Id, Name, and one method which is going to return a collection of Departments which is going to be the second data source.
using System.Collections.Generic;
namespace LINQFullOuterJoin
{
public class Department
{
public int ID { get; set; }
public string Name { get; set; }
public static List<Department> GetAllDepartments()
{
return new List<Department>()
{
new Department { ID = 10, Name = "IT" },
new Department { ID = 20, Name = "HR" },
new Department { ID = 30, Name = "Payroll" },
new Department { ID = 40, Name = "Admin" },
new Department { ID = 40, Name = "Sales" }
};
}
}
}
LINQ Full Outer Join Example using C#:
In the below example, we are fetching both matchings as well as non-matching elements from both data sources using Full Join. As Full Outer Join is not directly supported in LINQ, we can still achieve the same by performing the UNION operation over the Left Outer Join and Right Outer Join which is shown in the below example.
using System.Linq;
using System;
namespace LINQFullOuterJoin
{
class Program
{
static void Main(string[] args)
{
//Full Outer Join = Left Outer Join UNION Right Outer Join
//Performinng Left Outer Join
var LeftOuterJoin = from emp in Employee.GetAllEmployees()
join dept in Department.GetAllDepartments()
on emp.DepartmentId equals dept.ID into EmployeeDepartmentGroup
from department in EmployeeDepartmentGroup.DefaultIfEmpty()
select new
{
//To Avoid Runtime Null Reference Exception, check NULL
EmployeeId = emp?.ID,
EmployeeName = emp?.Name,
DepartmentName = department?.Name
};
var RightOuterJoin = from dept in Department.GetAllDepartments()
join emp in Employee.GetAllEmployees()
on dept.ID equals emp.DepartmentId into EmployeeDepartmentGroup
from employee in EmployeeDepartmentGroup.DefaultIfEmpty()
select new
{
//To Avoid Runtime Null Reference Exception, check NULL
EmployeeId = employee?.ID,
EmployeeName = employee?.Name,
DepartmentName = dept?.Name
};
var FullOuterJoin = LeftOuterJoin.Union(RightOuterJoin);
foreach (var emp in FullOuterJoin)
{
Console.WriteLine($"EmployeeId: {emp.EmployeeId}, Name: {emp.EmployeeName}, Department: {emp.DepartmentName}");
}
Console.ReadLine();
}
}
}
Output:
Now, instead of printing the Empty value, if the value is not available, we want to store NA in the result set for the Name and Department string values. To do so, modify the Main method of the Program class as follows.
using System.Linq;
using System;
namespace LINQFullOuterJoin
{
class Program
{
static void Main(string[] args)
{
//Full Outer Join = Left Outer Join UNION Right Outer Join
//Performinng Left Outer Join
var LeftOuterJoin = from emp in Employee.GetAllEmployees()
join dept in Department.GetAllDepartments()
on emp.DepartmentId equals dept.ID into EmployeeDepartmentGroup
from department in EmployeeDepartmentGroup.DefaultIfEmpty()
select new
{
//To Avoid Runtime Null Reference Exception, check NULL
EmployeeId = emp?.ID,
EmployeeName = emp?.Name ?? "NA",
DepartmentName = department?.Name ?? "NA"
};
var RightOuterJoin = from dept in Department.GetAllDepartments()
join emp in Employee.GetAllEmployees()
on dept.ID equals emp.DepartmentId into EmployeeDepartmentGroup
from employee in EmployeeDepartmentGroup.DefaultIfEmpty()
select new
{
//To Avoid Runtime Null Reference Exception, check NULL
EmployeeId = employee?.ID,
EmployeeName = employee?.Name ?? "NA",
DepartmentName = dept?.Name ?? "NA"
};
var FullOuterJoin = LeftOuterJoin.Union(RightOuterJoin);
foreach (var emp in FullOuterJoin)
{
Console.WriteLine($"EmployeeId: {emp.EmployeeId}, Name: {emp.EmployeeName}, Department: {emp.DepartmentName}");
}
Console.ReadLine();
}
}
}
LINQ Full Join Example using Method Syntax:
In the below example, I am showing how to implement Full Join in LINQ using Method Syntax. The process is the same. First, Left Outer Join, then Right Outer Join, and finally perform the UNION operation between the Left Outer Join and Right Outer Join which is shown in the below example.
using System.Linq;
using System;
namespace LINQFullOuterJoin
{
class Program
{
static void Main(string[] args)
{
//Performing Left Outer Join using LINQ using Method Syntax
var MSLeftOuterJOIN = Employee.GetAllEmployees() //Left Data Source
//Performing Group join with Right Data Source
.GroupJoin(
Department.GetAllDepartments(), //Right Data Source
employee => employee.DepartmentId, //Outer Key Selector, i.e. Left Data Source Common Property
department => department.ID, //Inner Key Selector, i.e. Right Data Source Common Property
(employee, department) => new { employee, department } //Projecting the Result
)
.SelectMany(
x => x.department.DefaultIfEmpty(), //Performing Left Outer Join
//Final Result Set
(employee, department) => new
{
EmployeeId = employee?.employee?.ID,
EmployeeName = employee?.employee?.Name,
DepartmentName = department?.Name
}
);
//Performing Right Outer Join using LINQ using Method Syntax
var MSRightOuterJOIN = Department.GetAllDepartments() //Left Data Source
//Performing Group join with Right Data Source
.GroupJoin(
Employee.GetAllEmployees(), //Right Data Source
department => department.ID, //Outer Key Selector, i.e. Left Data Source Common Property
employee => employee.DepartmentId, //Inner Key Selector, i.e. Right Data Source Common Property
(department, employee) => new { department, employee } //Projecting the Result
)
.SelectMany(
x => x.employee.DefaultIfEmpty(), //Performing Left Outer Join
//Final Result Set
(department, employee) => new
{
EmployeeId = employee?.ID,
EmployeeName = employee?.Name,
DepartmentName = department?.department?.Name
}
);
var FullOuterJoin = MSLeftOuterJOIN.Union(MSRightOuterJOIN);
//Accessing the Elements using For Each Loop
foreach (var emp in FullOuterJoin)
{
Console.WriteLine($"EmployeeId: {emp.EmployeeId}, Name: {emp.EmployeeName}, Department: {emp.DepartmentName}");
}
Console.ReadLine();
}
}
}
Output:
In the next article, I am going to discuss the LINQ Cross Join in C# with Examples. In this article, I try to explain how to implement Full Outer Join in LINQ using Method syntax and Query Syntax using C# Language. I hope you enjoy this LINQ Full Join in C# with Examples article.