LINQ Left Outer Join

In LINQ, LEFT JOIN or LEFT OUTER JOIN is used to return all the records or elements from the left side collection and match elements from the right side collection.

 

In LINQ, to achieve LEFT JOIN behavior, it's mandatory to use the "INTO" keyword and the "DefaultIfEmpty()" method.

Syntax of LINQ Left Outer Join

Following is the syntax of using LINQ Left Join to get all the elements from the left collection and matching elements from the right collection.

 

C# Code

 

var result = from e in objEmp
             join d in objDept
             on e.DeptId equals d.DepId into empDept
             from ed in empDept.DefaultIfEmpty()
             select new
             {
               EmployeeName = e.Name,
               DepartmentName = ed == null ? "No Department" : ed.DepName
             }

VB.NET Code

 

Dim result = From e In objEmp Group Join d In objDept On e.DeptId Equals d.DepId Into empDept = Group From ed In empDept.DefaultIfEmpty()
Select New With {.EmployeeName = e.Name, .DepartmentName = If(ed Is Nothing, "No Department", ed.DepName)}

If you observe the above syntax, we used Into and DefaultIfEmpty() methods to implement left outer join to get elements from “objEmp”, “objDept” collections.

Example of LINQ Left Outer Join

Following is the example of using LINQ Left Outer Join to get elements from collections based on specified conditions.

 

C# Code

 

using System;
using System.Collections.Generic;
using System.Linq;

namespace Linqtutorials
{
  class Program
  {
    static void Main(string[] args)
    {
      List<Department> objDept = new List<Department>(){
       new Department{DepId=1,DepName="Software"},
       new Department{DepId=2,DepName="Finance"},
       new Department{DepId=3,DepName="Health"}
      }
      List<Employee> objEmp = new List<Employee>()
      {
        new Employee { EmpId=1,Name = "Suresh Dasari", DeptId=1 },
        new Employee { EmpId=2,Name = "Rohini Alavala", DeptId=1 },
        new Employee { EmpId=3,Name = "Praveen Alavala", DeptId=2 },
        new Employee { EmpId=4,Name = "Sateesh Alavala", DeptId =2},
        new Employee { EmpId=5,Name = "Madhav Sai"}
      }
      var result = from e in objEmp
                   join d in objDept
                   on e.DeptId equals d.DepId into empDept
                   from ed in empDept.DefaultIfEmpty()
                   select new
                   {
                     EmployeeName = e.Name,
                     DepartmentName = ed == null ? "No Department" : ed.DepName
                   }
      foreach (var item in result)
      {
         Console.WriteLine(item.EmployeeName + "\t | " + item.DepartmentName);
      }
      Console.ReadLine();
    }
  }
  class Department
  {
    public int DepId { get; set; }
    public string DepName { get; set; }
  }
  class Employee
  {
    public int EmpId { get; set; }
    public string Name { get; set; }
    public int DeptId { get; set; }
  }
}

VB.NET Code

 

Module Module1
Sub Main()
Dim objDept As New List(Of Department)() From {
New Department With {.DepId = 1, .DepName = "Software"},
New Department With {.DepId = 2, .DepName = "Finance"},
New Department With {.DepId = 3, .DepName = "Health"}
}
Dim objEmp As New List(Of Employee)() From {
New Employee With {.EmpId = 1, .Name = "Suresh Dasari", .DeptId = 1},
New Employee With {.EmpId = 2, .Name = "Rohini Alavala", .DeptId = 1},
New Employee With {.EmpId = 3, .Name = "Praveen Alavala", .DeptId = 2},
New Employee With {.EmpId = 4, .Name = "Sateesh Alavala", .DeptId = 2},
New Employee With {.EmpId = 5, .Name = "Madhav Sai"}
}
Dim result = From e In objEmp Group Join d In objDept On e.DeptId Equals d.DepId Into empDept = Group From ed In empDept.DefaultIfEmpty()
Select New With {.EmployeeName = e.Name, .DepartmentName = If(ed Is Nothing, "No Department", ed.DepName)}
For Each item In result
Console.WriteLine(item.EmployeeName + vbTab & " | " + item.DepartmentName)
Next
Console.ReadLine()
End Sub
Class Department
Public Property DepId() As Int32
Get
Return m_DepId
End Get
Set(ByVal value As Int32)
m_DepId = value
End Set
End Property
Private m_DepId As Int32
Public Property DepName() As String
Get
Return m_DepName
End Get
Set(ByVal value As String)
m_DepName = value
End Set
End Property
Private m_DepName As String
End Class

Class Employee
Public Property EmpId() As Int32
Get
Return m_EmpId
End Get
Set(ByVal value As Int32)
m_EmpId = value
End Set
End Property
Private m_EmpId As Int32
Public Property Name() As String
Get
Return m_Name
End Get
Set(ByVal value As String)
m_Name = value
End Set
End Property
Private m_Name As String
Public Property DeptId() As String
Get
Return m_DeptId
End Get
Set(ByVal value As String)
m_DeptId = value
End Set
End Property
Private m_DeptId As String
End Class
End Module

If you observe the above example, we are getting the elements from “objEmpt” and “objDept” collections using left outer join in LINQ. Here we specified conditions to check if there is a department for an employee or not; if the department is not mapped, it will take “No Department” from the condition.

Result of LINQ Left Outer Join

Following is the result of LINQ Left Outer Join to get all elements from the left collection and match elements from the right collection based on specified conditions.

 

Suresh Dasari    | Software
Rohini Alavala   | Software
Praveen Alavala  | Finance
Sateesh Alavala  | Finance
Madhav Sai       | No Department

If you observe the above example, “Madhav Sai” does not have any department mapped, so it's displaying “No Department” in the result set.

 

This is how we can use LINQ Left Outer Join in c#, vb.net to get elements from collections based on requirements.