To display matching records from the left table and the right table in SQL the left outer join is a type of join operation that is used. In LINQ, the left outer join can be performed using the GroupJoin() and SelectMany() methods, this article will extensively discuss performing left outer joins in LINQ using C#.
How to Perform Left Outer Joins in LINQ Using C#
To perform a left outer join in LINQ, you can use the GroupJoin() method to join two sequences based on a common key and then use the SelectMany() method to flatten the result, here is an example:
using System.Linq;
class Program
{
static void Main()
{
var left = new[] { 1, 2, 3 };
var right = new[] { 2, 3, 4 };
var result = left.GroupJoin(
right,
l => l,
r => r,
(l, r) => new { Left = l, Right = r.DefaultIfEmpty() })
.SelectMany(
lr => lr.Right.Select(
r => new { Left = lr.Left, Right = r }));
foreach (var item in result)
{
Console.WriteLine("{0} {1}", item.Left, item.Right);
}
}
}
This code performs a left outer join on two arrays left and right, and prints the result to the console. The GroupJoin() method performs the join, and the SelectMany() method is used to flatten the result. Finally, the result is printed to the console using a foreach loop and WriteLine() function:
Here is an another example that demonstrates the use of performing left out joins by displaying respective employee names and their relevant departments. Each employee is each department have been assigned a number which are then used to match the relevant department to the respective employee, here is the complete code for it:
using System.Collections.Generic;
using System.Linq;
namespace YourNamespace
{
class Program
{
static void Main(string[] args)
{
List<Employeename> employees = new List<Employeename>
{
new Employeename { Id = 1, Name = "Sam", DepartmentId = 1 },
new Employeename { Id = 2, Name = "Jhon", DepartmentId = 2 },
new Employeename { Id = 3, Name = "Kevin", DepartmentId = 2 },
new Employeename { Id = 4, Name = "Bob", DepartmentId = 3 }
};
List<Department> departments = new List<Department>
{
new Department { Id = 1, Name = "Content Writing" },
new Department { Id = 2, Name = "Marketing" },
new Department { Id = 3, Name = "Engineering" }
};
var query = from employeename in employees
join department in departments
on employeename.DepartmentId equals department.Id into departmentGroup
from department in departmentGroup.DefaultIfEmpty()
select new { EmployeenameName = employeename.Name, DepartmentName = department?.Name ?? "None" };
foreach (var result in query)
{
Console.WriteLine($"Employeename: {result.EmployeenameName}, Department: {result.DepartmentName}");
}
}
}
class Employeename
{
public int Id { get; set; }
public string Name { get; set; }
public int DepartmentId { get; set; }
}
class Department
{
public int Id { get; set; }
public string Name { get; set; }
}
}
First a sample data is given that contains employee name and the name of departments and afterwards a respective number is given to each. Next the join operation is performed by using the join command and after that the result is saved in a variable named query, next the for loop is used to print the names and departments of the respective employees and the output of the code will be like this:
Conclusion
Left outer join is a common operation in SQL, and can also be performed easily using LINQ in C#. Using the GroupJoin() and SelectMany() methods, you can perform left outer joins on two sequences based on a common key. Although the syntax for performing left outer joins in LINQ may be difficult to understand for beginners, it is a powerful and flexible language that allows for complex queries to be performed easily.
