Left Outer Join in Linq

Left Outer Join in Linq using Method and Query Syntax

In this article, I am going to discuss Left Outer Join in Linq with some examples using both Method and Query Syntax. Please read the following three articles before proceeding to this article as they both are required to understand the Left Outer Join.

  1. Inner Join in Linq
  2. Group Join in Linq
  3. Select Many In Linq
What is Left Outer Join in Linq?

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. Please have a look at the following diagram which shows the graphical representation of Left Outer Join.

Left Outer Join in Linq

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.

In order to implement the Left Outer Join in Linq, it’s mandatory to use the “INTO” keyword along with the “DefaultIfEmpty()” method.

Model Classes and Data Sources:

We are going to use the following Employee and Address models in this demo. Please create a class file and then copy and paste the following code.

using System.Collections.Generic;
namespace LINQJoin
{
    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}
            };
        }
    }

    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"},
            };
        }
    }
}

As you can see, here we also created two methods which will be going to return the respective Employees and Addresses which are going to be our data source. Here we 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 that means these two employees do not have a matching address in the address data source.

Left Outer Join using Query Syntax:

In order to perform the left outer join using query syntax, you need to call the DefaultIfEmpty() method on the results of a group join. Let’s see the step by step procedure to implement the left outer join in Linq.

Step1:

The first step to implement a left outer join is to perform an inner join by using a group join. In the below example, the list of Employees is inner-joined to the list of Addresses based on the Address Id of Employee object that matches the ID of the Address object. The following code does the same.

Inner Join using Group Join

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 no matches 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 object is empty for any Employee object which will ensure that each Employee object is represented in the result collection. The following code exactly does the same thing.

DefaultIfEmplty in Outer Join

Note: The default value for a reference type is null. So, you need to check for the null reference before accessing each element of Address collection.

The complete code is given below.

using System.Linq;
using System;
namespace LINQJoin
{
    class Program
    {
        static void Main(string[] args)
        {
            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 {emp, address };
            
            foreach (var item in QSOuterJoin)
            {
                Console.WriteLine($"Name : {item.emp.Name}, Address : {item.address?.AddressLine} ");
            }

            Console.ReadLine();
        }
    }
}

Output:

Left Outer Join Output

Left Outer Join in Linq using Method Syntax:

In order to implement Left Outer Join in Linq using Method Syntax we need to use the GroupJoin() method along with SelectMany() and DefaultIfEmpty() methods. So, let us rewrite the previous example using Method Syntax as shown below.

using System.Linq;
using System;
namespace LINQJoin
{
    class Program
    {
        static void Main(string[] args)
        {
            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{ employee, address }
                               );

            foreach (var item in MSOuterJOIN)
            {
                Console.WriteLine($"Name : {item.employee.emp.Name}, Address : {item.address?.AddressLine} ");
            }

            Console.ReadLine();
        }
    }
}

It will give you the same output as the previous example. I feel it always better to use Query Syntax over Method Syntax to perform left outer join in Linq as it is simple and easy to understand.

Anonymous type with user-defined properties in the ResultSet:

Let us see how to return an anonymous type with user-defined properties.

using System.Linq;
using System;
namespace LINQJoin
{
    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();
        }
    }
}

Note: If you want to perform Right outer join then simply exchange the data source.

In the next article, I am going to discuss the Linq Cross Join with some examples. In this article, I try to explain how to implement Left Outer Join in Linq using Method syntax and Query Syntax.

Leave a Reply

Your email address will not be published. Required fields are marked *