Inner Join, Left Join, Right Join in LINQ Query C#

Entity Framework is a popular ORM framework in which developers don't have to write SQL queries and focus more on writing business logic in the C# end. Instead, to read or write data in a database we use LINQ Query or Lambda Expression in our data layer of the project.

LINQ queries are very similar to SQL queries and are converted to SQL queries by .Net Runtime when we run our application.

LINQ queries generally have three parts like below

from ...
where ...
select ...

Using the JOIN operator is very common in SQL Queries as they help us to get data from two or more tables in a database. In this article, we will see how to join two or more tables in LINQ Query to get the final data.

In this example, we are using Sales.Customer and Sales.SalesOrderHeader table in AdventureWorks2014 database.

INNER JOIN in LINQ Query

Suppose we want to fetch all the customers from Sales.Customer table which has the corresponding record in Sales.SalesOrderHeader table which has total due greater than 10000 then we have to go for INNER JOIN Query or simply JOIN in our LINQ Query.

/// <summary>
/// Gets all the customer which has corresponding Order in SalesOrderHeader table
/// </summary>
/// <returns></returns>
public IEnumerable<CustomerOrder> GetAllCustomerWithOrder()
{


    var customerOrders =
                                from customer in _dbCoreContext.Customers
                                join salesorderheader in _dbCoreContext.SalesOrderHeaders
                                    on customer.CustomerId equals salesorderheader.CustomerId
                                where salesorderheader.TotalDue > 10000
                                select new CustomerOrder
                                {
                                    CustomerId = customer.CustomerId,
                                    AccountNumber = customer.AccountNumber,
                                    OrderDate = salesorderheader.OrderDate,
                                    SalesOrderNumber = salesorderheader.SalesOrderNumber,
                                    TotalDue = salesorderheader.TotalDue
                                };


    var result = customerOrders.ToList().AsEnumerable();
    return result;
}

In the above example, we can see we are joining between Customer and SalesOrderHeaders table and returning list of a new entity named CustomerOrder which has CustomerId, AccountNumber from Customer table and OrderDate, SalesOrderNumber, and TotalDue from SalesOrderHeader table.

LEFT JOIN in LINQ Query

Suppose we want to retrieve all the Customers irrespective of whether they have any order or not then we can write LEFT JOIN in LINQ Query to retrieve the same.

/// <summary>
/// Get all the customers with or without Order
/// </summary>
/// <returns></returns>
public IEnumerable<CustomerOrder> GetAllCustomer()
{


    var customerOrders =
                                from customer in _dbCoreContext.Customers
                                join salesorderheader in _dbCoreContext.SalesOrderHeaders
                                    on customer.CustomerId equals salesorderheader.CustomerId into nc
                                from subSalesOrderHeader in nc.DefaultIfEmpty()
                                where subSalesOrderHeader.TotalDue > 10000
                                select new CustomerOrder
                                {
                                    CustomerId = customer.CustomerId,
                                    AccountNumber = customer.AccountNumber,
                                    OrderDate = subSalesOrderHeader.OrderDate,
                                    SalesOrderNumber = subSalesOrderHeader.SalesOrderNumber,
                                    TotalDue = subSalesOrderHeader.TotalDue
                                };


    var result = customerOrders.ToList().AsEnumerable();
    return result;
}

RIGHT JOIN in LINQ

Suppose we want to get all the Orders from SalesOrderHeader table whether they have Customer associated with or without it then we can go for RIGHT JOIN in LINQ. RIGHT JOIN in LINQ is reverse of LEFT JOIN so we just swap the position of Customer and SalesOrderHeader table.

/// <summary>
/// Get all the orders with or without Customer associated with it.
/// </summary>
/// <returns></returns>
public IEnumerable<CustomerOrder> GetAllOrderWithCustomer()
{


    var customerOrders =
                                from salesorderheader in _dbCoreContext.SalesOrderHeaders
                                join customer in _dbCoreContext.Customers
                                    on salesorderheader.CustomerId equals customer.CustomerId into nc
                                from subCustomer in nc.DefaultIfEmpty()
                                where salesorderheader.TotalDue > 10000
                                select new CustomerOrder
                                {
                                    CustomerId = subCustomer.CustomerId,
                                    AccountNumber = subCustomer.AccountNumber,
                                    OrderDate = salesorderheader.OrderDate,
                                    SalesOrderNumber = salesorderheader.SalesOrderNumber,
                                    TotalDue = salesorderheader.TotalDue
                                };


    var result = customerOrders.ToList().AsEnumerable();
    return result;
}

Hope you have understood how to use JOIN in LINQ Query.

Inner Join, Left Join, Right Join in LINQ Query C#

INNER JOIN in LINQ Query

LEFT JOIN in LINQ Query

RIGHT JOIN in LINQ

Share This Post

Support Me