LINQ Left Outer Join

  Total Views : 224
  Comments : 0
 
  Keywords : Linq left outer join with defaultifempty in c# / vb.net, How to use linq left join or left outer join in c# / vb.net with example, How to perform linq left join in c# / vb.net with example

Here we will learn LINQ left outer join in c#, vb.net with example and how to use linq left outer join with defaultifempty to get all elements from left collection and matching elements from right collection in c#, vb.net with example.

LINQ Left Outer Join

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

 

In LINQ to achieve LEFT JOIN behavior, its mandatory to use "INTO" keyword and "DefaultIfEmpty()" method.

Syntax of LINQ Left Outer Join

Following is the syntax of using LINQ Left Join to get all the elements from left collection and matching elements from 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 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 above example we are getting elements from “objEmpt”, “objDept” collections by using left outer join in LINQ and here we specified condition to check if there is department for employee or not in case if department not mapped it will take “No Department” from the condition.

Output of LINQ Left Outer Join

Following is the result of LINQ Left Outer Join to get all elements from left collection and matching elements from 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 above example “Madhav Sai” does not have any department mapped so its displaying “No Deparatment” 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.

 

PREVIOUS

LINQ Inner Join
 

NEXT

LINQ Cross Join  
 

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User