LINQ Left Join in C#

Back to: LINQ Tutorial For Beginners and Professionals

LINQ Left Outer Join in C# with Examples

In this article, I will discuss LINQ Left Outer Join in C# with Examples using Method and Query Syntax. The Left Join is also called the 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.

1. Inner Join in LINQ
2. Group Join in LINQ
3. Select Many In LINQ

What is Left Join?

The Left Join or Left Outer Join is a Join in which each data from the first data source will be returned irrespective of whether it has any correlated data present in the second data source. That means the LEFT OUTER JOIN retrieves all the matching rows from both the data sources involved in the join and 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 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 will return all the matching data from both the data sources and all the non-matching data from the left data source. In such cases, the non-matching data will take null values for the second data source. 

Left Outer Join in LINQ

In LINQ, you can perform a left join operation between two collections (or tables in a database) using the join keyword followed by the into keyword to create a temporary grouping of results. You can then use the DefaultIfEmpty method to specify the left join behavior. So, to implement the LINQ Left Join, it’s mandatory to use the into keyword along with the DefaultIfEmpty() method. 

Examples to Understand Left Outer Join in LINQ:

Let us Understand Left Outer Join in LINQ with Examples using C# Language. We will use the following Employee and Address Data Sources to understand LINQ Left Outer Join. So, first, create a class file with the name Employee.cs and then copy and paste the following code into it. This class has 3 properties, i.e., Id, Name, and AddressId. We have also created one method, i.e., GetAllEmployees(), which will return a collection of Employees, 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 copy and paste the following code. This class has two properties, i.e., ID and AddressLine. We have also created one method, i.e., GetAddress(), which will return a collection of addresses, which will 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"},
            };
        }
    }
}

In our example, 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 you will get the data from a database in real-time. 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 see an example to Understand LINQ Left Outer Join using Query Syntax using C#. 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 available. We need to use the LINQ Inner Join and 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 using the “into” operator. In the code snippet below, 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 store 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 in the second (i.e., right) data source. To do this, we need to call the DefaultIfEmpty() method on each sequence of matching elements from the group join.

In our example, we must call the DefaultIfEmpty() method on each sequence of matching Address objects. The DefaultIfEmpty() method returns a collection containing a default value if the sequence of matching Address objects is empty for any Employee object, ensuring that each Employee object is represented in the result collection. That means if the corresponding value is empty, 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. In the below example, we are implementing the LINQ Left Outer Join using Query Syntax. In the below example, we are projecting the result to an anonymous 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
                              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, they are still 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 named EmployeeResult.cs with the required properties 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, 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#:

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 the 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, which will give you the same output as the previous example, as shown in the image below. 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.

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 example below, we display the value NA if the corresponding address is unavailable.

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 unavailable for an employee.

LINQ Right Outer Join

The RIGHT OUTER JOIN retrieves all the matching rows from both the data sources involved in the join and 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.

To perform the Right Outer Join, exchange the data sources. In our previous examples, we have used Employees as the Left Data Source and Addresses 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();
        }
    }
}

Real-Time Example to Understand LINQ Left Join:

Suppose you have a list of orders and a list of customers, and you want to generate a report of all orders along with the corresponding customer names (if available) or a default value if there’s no matching customer. A left join would be appropriate in this case because you want to include all orders in the report. For a better understanding, please have a look at the below example:

using System;
using System.Collections.Generic;
using System.Linq;
namespace ConsoleApp12
{
    public class Order
    {
        public int OrderId { get; set; }
        public int CustomerId { get; set; }
        public DateTime OrderDate { get; set; }
    }

    public class Customer
    {
        public int CustomerId { get; set; }
        public string Name { get; set; }
    }
    class Program
    {
        static void Main(string[] args)
        {
            List<Order> orders = new List<Order>
            {
                new Order { OrderId = 101, CustomerId = 10101, OrderDate = DateTime.Parse("2022-05-10") },
                new Order { OrderId = 102, CustomerId = 10102, OrderDate = DateTime.Parse("2022-05-15") },
                new Order { OrderId = 103, CustomerId = 10103, OrderDate = DateTime.Parse("2022-05-20") },
            };

            List<Customer> customers = new List<Customer>
            {
                new Customer { CustomerId = 10101, Name = "Pranaya" },
                new Customer { CustomerId = 10103, Name = "Rout" },
            };

            var leftJoinQuery =
                from order in orders
                join customer in customers
                on order.CustomerId equals customer.CustomerId into customerGroup
                from customerInfo in customerGroup.DefaultIfEmpty()
                select new
                {
                    OrderId = order.OrderId,
                    OrderDate = order.OrderDate,
                    CustomerName = customerInfo?.Name ?? "No Customer"
                };

            foreach (var result in leftJoinQuery)
            {
                Console.WriteLine($"OrderID: {result.OrderId}, OrderDate: {result.OrderDate}, CustomerName: {result.CustomerName}");
            }

            Console.ReadKey();
        }
    }
}

In the above example, we first define two collections: orders and customers. We then use a LINQ query to perform a left join between these collections based on the CustomerId field. The into keyword is used to create a temporary grouping of results for each order, and the DefaultIfEmpty method is used to specify the left join behavior.

The result is a sequence of anonymous objects that contain order information along with customer names (if available) or “No Customer” if there is no matching customer for an order.

When to use LINQ Left Join in C#?

Here are some common scenarios where you might use a LINQ left join:

• Combining Data with Missing Records: When you have two collections, and you want to combine them while preserving all elements from one collection and including data from the other collection when a match is found, but not excluding any elements from the first collection.
• Optional Relationships: In database scenarios, if you have a parent-child relationship between two tables where some child records may not have a corresponding parent record, a left join ensures that you get all child records along with their parent data when available.
• Reporting and Analysis: When working with data for reporting or analysis, you may want to include all data from one source while enriching it with additional information from another source, and you don’t want to lose any records from the first source.
• Handling Default Values: When combining data, you can use left joins to provide default values or placeholders for missing data from the second collection.

In the next article, I will discuss the LINQ Full Outer Join in C# with Examples. In this article, I 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.