Back to: LINQ Tutorial For Beginners and Professionals
LINQ Left Outer Join in C# with Examples
In this article, I am going to discuss LINQ Left Outer Join in C# with Examples using both Method and Query Syntax. The Left Join is also called Left Outer Join. Please read the following three articles before proceeding to this article as they all are required to understand the Left Outer Join.
What is Left Join?
The Left Join or Left Outer Join is a Join in which each data from the first data source is going to be returned irrespective of whether it has any correlated data present in the second data source or not. That means the LEFT OUTER JOIN is used to retrieve all the matching rows from both the data sources involved in the join as well as non-matching rows from the left side data source. In this case, the un-matching data will take a null value. For a better understanding, please have a look at the following diagram which shows the graphical representation of the Left Outer Join.
So, in simple words, we can say that the Left Outer Join is going to return all the matching data from both the data sources as well as all the non-matching data from the left data source. In such cases, for the non-matching data, it will take null values for the second data source.
Note: In order to implement the LINQ Left Join in C#, it’s mandatory to use the “into” keyword along with the “DefaultIfEmpty()” method. If this is not clear at the moment, don’t worry, we will understand this with multiple examples.
Examples to Understand Left Outer Join in LINQ:
Let us Understand Left Outer Join in LINQ with some Examples using C# Language. To Understand LINQ Left Outer Join, we are going to use the following Employee and Address Data Sources. So, first, create a class file with the name Employee.cs and then copy and paste the following code into it. This is a very simple class having 3 properties i.e. Id, Name, and AddressId. We have also created 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 LINQLeftOuterJoin
{
public class Employee
{
public int ID { get; set; }
public string Name { get; set; }
public int AddressId { get; set; }
public static List<Employee> GetAllEmployees()
{
return new List<Employee>()
{
new Employee { ID = 1, Name = "Preety", AddressId = 1},
new Employee { ID = 2, Name = "Priyanka", AddressId =2},
new Employee { ID = 3, Name = "Anurag", AddressId = 0},
new Employee { ID = 4, Name = "Pranaya", AddressId = 0},
new Employee { ID = 5, Name = "Hina", AddressId = 5},
new Employee { ID = 6, Name = "Sambit", AddressId = 6}
};
}
}
} Next, create another class file with the name Address.cs and then copy and paste the following code into it. This is a very simple class having 2 properties i.e. ID, and AddressLine. We have also created one method i.e. GetAddress() which is going to return a collection of addresses which is going to be our second data source.
using System.Collections.Generic;
namespace LINQLeftOuterJoin
{
public class Address
{
public int ID { get; set; }
public string AddressLine { get; set; }
public static List<Address> GetAddress()
{
return new List<Address>()
{
new Address { ID = 1, AddressLine = "AddressLine1"},
new Address { ID = 2, AddressLine = "AddressLine2"},
new Address { ID = 5, AddressLine = "AddressLine5"},
new Address { ID = 6, AddressLine = "AddressLine6"},
};
}
}
} Here we created two methods that will be going to return the respective Employees and Addresses which are going to be our data sources. We have hard-coded the data sources but in real-time you will get the data from a database. If you further notice we have two employees with address id 0 which means these two employees do not have a matching address in the address data source.
Examples to Understand LINQ Left Outer Join using Query Syntax in C#:
Let us an example to Understand LINQ Left Outer Join using Query Syntax using C#. In order to perform the left outer join using query syntax in LINQ, we need to call the DefaultIfEmpty() method on the results of a Group Join. In LINQ Query Syntax there is no such Group Join operator is available. Here we need to use the LINQ Inner Join along with the “into” operator as discussed in the previous article. Let’s see the step-by-step procedure to implement the LINQ Left Outer Join in C#.
Step1:
The first step to implement the LINQ Left Outer Join is to perform Group Join and store the result into a variable by using the “into” operator. In the below code snippet, as you can see, the list of Employees is inner-joined with the list of Addresses based on the AddressId Property of the Employee object that matches the ID property of the Address object. And then we are storing the result in the EmployeeAddressGroup using the “into” operator. The following code exactly does the same thing.
Step2:
In the second step, we need to include each element of the first (i.e. left) data source in the result set irrespective of whether that element has any matching element found or not in the second (i.e. right) data source. In order to do this, we need to call the DefaultIfEmpty() method on each sequence of matching elements from the group join.
In our example, we need to call the DefaultIfEmpty() method on each sequence of matching Address objects. The DefaultIfEmpty() method returns a collection that contains a single, default value if the sequence of matching Address objects is empty for any Employee object which will ensure that each Employee object is represented in the result collection. That means if the corresponding value is empty, then it will take the default value based on the data type. The following code exactly does the same thing.
The default value for a reference type is null. So, you need to check for the null reference before accessing each element of the Address collection. The complete example code is given below. The following example code is self-explained, so please go through the comment lines for a better understanding. In the below example, we are implementing the LINQ Left Outer Join using Query Syntax.
using System.Linq;
using System;
namespace LINQLeftOuterJoin
{
class Program
{
static void Main(string[] args)
{
//Performing Left Outer Join using LINQ using Query Syntax
//Left Data Source: Employees
//Right Data Source: Addresses
//Note: Left and Right Data Source Matters
var QSOuterJoin = from emp in Employee.GetAllEmployees() //Left Data Source
join add in Address.GetAddress() //Right Data Source
on emp.AddressId equals add.ID //Inner Join Condition
into EmployeeAddressGroup //Performing LINQ Group Join
from address in EmployeeAddressGroup.DefaultIfEmpty() //Performing Left Outer Join
select new { emp, address }; //Projecting the Result to Anonymous Type
//Accessing the Elements using For Each Loop
foreach (var item in QSOuterJoin)
{
//Before Accessing the AddressLine, please check null else you will get Null Reference Exception
Console.WriteLine($"Name : {item.emp.Name}, Address : {item.address?.AddressLine} ");
}
Console.ReadLine();
}
}
}
Now run the application and you will get the following output. As you can see, even though Anurag and Pranaya do not have the corresponding address, still they are returned as part of the result set. In that case, the Address will be NULL.
Projecting the Result to a Named Type:
Instead of projecting the result to an anonymous type, we can also project the result to a named type. Let us see how we can do this. First, create a class file with the name EmployeeResult.cs with the required properties that you want in the result set. As per our requirement, we have created the class with two properties.
namespace LINQLeftOuterJoin
{
class EmployeeResult
{
public Employee Employee { get; set; }
public Address Address { get; set; }
}
} Next, modify the Main method of the Program class as follows. Here, you can see, we are projecting the result to the above-created EmployeeResult type.
using System.Linq;
using System;
namespace LINQLeftOuterJoin
{
class Program
{
static void Main(string[] args)
{
//Performing Left Outer Join using LINQ using Query Syntax
//Left Data Source: Employees
//Right Data Source: Addresses
//Note: Left and Right Data Source Matters
var QSOuterJoin = from emp in Employee.GetAllEmployees() //Left Data Source
join add in Address.GetAddress() //Right Data Source
on emp.AddressId equals add.ID //Inner Join Condition
into EmployeeAddressGroup //Performing LINQ Group Join
from address in EmployeeAddressGroup.DefaultIfEmpty() //Performing Left Outer Join
//Projecting the Result to Named Type
select new EmployeeResult
{
Employee = emp,
Address = address
};
//Accessing the Elements using For Each Loop
foreach (var item in QSOuterJoin)
{
//Before Accessing the AddressLine, please check null else you will get Null Reference Exception
Console.WriteLine($"Name : {item.Employee.Name}, Address : {item.Address?.AddressLine} ");
}
Console.ReadLine();
}
}
} With the above changes in place, run the application and you will also get the same output as the previous example as shown in the below image.
Examples to Understand LINQ Left Outer Join using Method Syntax in C#:
In order to implement Left Outer Join in Linq using Method Syntax we need to use the GroupJoin() method along with the SelectMany() and DefaultIfEmpty() methods. So, let us rewrite the previous example using LINQ Method Syntax. For a better understanding, please have a look at the following example. The following example code is self-explained, so please go through the comment lines.
using System.Linq;
using System;
namespace LINQLeftOuterJoin
{
class Program
{
static void Main(string[] args)
{
//Performing Left Outer Join using LINQ using Method Syntax
//Left Data Source: Employees
//Right Data Source: Addresses
//Note: Left and Right Data Source Matters
var MSOuterJOIN = Employee.GetAllEmployees() //Left Data Source
//Performing Group join with Right Data Source
.GroupJoin(
Address.GetAddress(), //Right Data Source
employee => employee.AddressId, //Outer Key Selector, i.e. Left Data Source Common Property
address => address.ID, //Inner Key Selector, i.e. Right Data Source Common Property
(employee, address) => new { employee, address } //Projecting the Result
)
.SelectMany(
x => x.address.DefaultIfEmpty(), //Performing Left Outer Join
(employee, address) => new { employee, address } //Final Result Set
);
//Accessing the Elements using For Each Loop
foreach (var item in MSOuterJOIN)
{
Console.WriteLine($"Name : {item.employee.employee.Name}, Address : {item.address?.AddressLine} ");
}
Console.ReadLine();
}
}
} Now, run the application and it will give you the same output as the previous example as shown in the below image. I feel it is always better to use Query Syntax over Method Syntax to perform left outer join in Linq as it is simple and easy to understand.
Instead of projecting the result to an anonymous type, we can also project the result to a named type with LINQ Method Syntax. We have already created a type called EmployeeResult. Let us project the result to this EmployeeResult type. So, modify the Main method of the Program class as follows to project the result to the EmployeeResult type. The following example code is self-explained, so please go through the comment lines for a better understanding.
using System.Linq;
using System;
namespace LINQLeftOuterJoin
{
class Program
{
static void Main(string[] args)
{
//Performing Left Outer Join using LINQ using Method Syntax
//Left Data Source: Employees
//Right Data Source: Addresses
//Note: Left and Right Data Source Matters
var MSOuterJOIN = Employee.GetAllEmployees() //Left Data Source
//Performing Group join with Right Data Source
.GroupJoin(
Address.GetAddress(), //Right Data Source
employee => employee.AddressId, //Outer Key Selector, i.e. Left Data Source Common Property
address => address.ID, //Inner Key Selector, i.e. Right Data Source Common Property
(employee, address) => new { employee, address } //Projecting the Result
)
.SelectMany(
x => x.address.DefaultIfEmpty(), //Performing Left Outer Join
//Final Result Set
(employee, address) => new EmployeeResult {
Employee = employee.employee,
Address = address
}
);
//Accessing the Elements using For Each Loop
foreach (var item in MSOuterJOIN)
{
Console.WriteLine($"Name : {item.Employee.Name}, Address : {item.Address?.AddressLine} ");
}
Console.ReadLine();
}
}
} Run the application and you will get the same output as the previous example.
Anonymous Type with User-Defined Properties in the ResultSet:
Let us see how to return an anonymous type with user-defined properties using LINQ Left Join in C#. Please have a look at the following example for a better understanding. In the below example, if the corresponding address is not available, then we are displaying the value NA.
using System.Linq;
using System;
namespace LINQLeftOuterJoin
{
class Program
{
static void Main(string[] args)
{
//Using Method Syntax
var MSOuterJOIN = Employee.GetAllEmployees()
.GroupJoin(
Address.GetAddress(),
emp => emp.AddressId,
add => add.ID,
(emp, add) => new { emp, add }
)
.SelectMany(
x => x.add.DefaultIfEmpty(),
(employee, address) => new
{
EmployeeName = employee.emp.Name,
AddressLine = address == null ? "NA" : address.AddressLine
}
);
//Using Query Syntax
var QSOuterJoin = from emp in Employee.GetAllEmployees()
join add in Address.GetAddress()
on emp.AddressId equals add.ID
into EmployeeAddressGroup
from address in EmployeeAddressGroup.DefaultIfEmpty()
select new
{
EmployeeName = emp.Name,
AddressLine = address == null ? "NA" : address.AddressLine
};
foreach (var item in MSOuterJOIN)
{
Console.WriteLine($"Name : {item.EmployeeName}, Address : {item.AddressLine} ");
}
Console.ReadLine();
}
}
} Now, run the application and you will get the following output. Now, we are getting NA in the address if the corresponding address is not available for an employee.
LINQ Right Outer Join
The RIGHT OUTER JOIN is used to retrieve all the matching rows from both the data sources involved in the join as well as non-matching rows from the right-side data source. In this case, the un-matching data will take the default values. But, Right Outer Join is not supported with LINQ. LINQ only supports left outer joins.
If you want to perform the Right Outer Join then simply exchange the data sources. In our previous examples, we have used Employees as the Left Data Source and Address as the Right Data Source. In the below example, we are just changing the Data Sources. Now, we are making Address as the Left Data Source and Employees the Right Data Source.
using System.Linq;
using System;
namespace LINQLeftOuterJoin
{
class Program
{
static void Main(string[] args)
{
//Performing Right Outer Join using LINQ using Query Syntax
//Changing the Data Sources
//Left Data Source: Addresses
//Right Data Source: Employees
//Note: Left and Right Data Source Matters
var QSRightJoin = from add in Address.GetAddress() //Left Data Source
join emp in Employee.GetAllEmployees() //Right Data Source
on add.ID equals emp.AddressId //Inner Join Condition
into EmployeeAddressGroup //Performing LINQ Group Join
from employee in EmployeeAddressGroup.DefaultIfEmpty() //Performing Left Outer Join
select new { add, employee }; //Projecting the Result to Anonymous Type
//Accessing the Elements using For Each Loop
foreach (var item in QSRightJoin)
{
//Before Accessing the AddressLine, please check null else you will get Null Reference Exception
Console.WriteLine($"Name : {item.employee?.Name}, Address : {item.add?.AddressLine} ");
}
Console.ReadLine();
}
}
} In the next article, I am going to discuss the LINQ Full Outer Join in C# with Examples. In this article, I try to explain how to implement Left Join in Linq using Method syntax and Query Syntax using C# Language. I hope you enjoy this LINQ Left Outer Join in C# with Examples article.
Great post !!
Thank you