Linq inner join, left outer join on two lists in C# with example

Joining two generic lists in linq is tricky, in this article we will see how we can join two lists by using inner join and then by using outer join. Using inner join is quite similar to sql inner join but outer join is a little different, so we will see it with example code as well as records (output).

So let's check the data, two generic lists which we will use in our example

List1: Orders

OrderID	Customer Name	Order Date	Total Amount
101	AAA	11 Sep 2013	10.00
102	BBB	15 Sep 2013	25.00
103	CCC	25 Sep 2013	80.00

List2: OrderDetail

DetaillD	Product	Qty	Price	Amount	OrderID
1	A 1	2	5	10.00	101
1	B 1	5	5	25.00	102

It is not real data, so we don't have any record in detail table for order id 103.

We want following items in our joined list

• Customer Name
• Order Date
• Product Name
• Product Total

If we will do the inner join, we will only get two records for order 101 and order 102 because order 103 don't have any product detail in detail table.

So let's write our INNER JOIN code

var joinedList = (from ord in orders
     join detail in orderDetails on ord.OrderID equals detail.OrderID
    select new { ord.CustomerName, ord.OrderDate, detail.ProductName, detail.Amount });

And here is the output:

Customer Name	Order Date	Product Name	Amount
AAA	9/25/2013 12:39:21 AM	A 1	10.00
BBB	9/25/2013 12:39:21 AM	B 1	25.00

As we can see it is very similar to sql inner join, difference is only in compare statement, we use "equals" to compare two IDs in place of "=".

Let's write LEFT JOIN code:

var joinedList = (from ord in orders
           join detail in orderDetails on ord.OrderID equals detail.OrderID into temp
           from detail in temp.DefaultIfEmpty()
           select new
           {
             ord.CustomerName
             , ord.OrderDate 
             , ProductName = detail == null ? String.Empty : detail.ProductName
             , Amount = detail == null ? null : detail.Amount
           });

And here is the output:

Customer Name	Order Date	Product Name	Amount
AAA	9/25/2013 12:36:02 AM	A 1	10
BBB	9/25/2013 12:36:02 AM	B 1	25
CCC	9/25/2013 12:36:02 AM

If you want to check it copy the following code and check it yourself:

Create two classes or structure to create the list of Orders and OrderDetails

public class Orders
{
    public Int32 OrderID { get; set; }
    public String CustomerName { get; set; }
    public DateTime OrderDate { get; set; }
    public Decimal TotalAmount { get; set; }
}

public class OrderDetail
{
    public Int32 DetailID { get; set; }
    public String ProductName { get; set; }
    public Int32 Qty { get; set; }
    public Decimal Price { get; set; }
    public Decimal? Amount { get; set; }
    public Int32 OrderID { get; set; }
}

Now add record in these structure

List<Orders> orders = new List<Orders>();
orders.Add(new Orders 
{ 
    OrderID = 101
  , CustomerName = "AAA"
  , OrderDate = DateTime.Now
  , TotalAmount = 10 
});
orders.Add(new Orders 
{
    OrderID = 102
  , CustomerName = "BBB"
  , OrderDate = DateTime.Now
  , TotalAmount = 25 
});
orders.Add(new Orders 
{ 
    OrderID = 103
  , CustomerName = "CCC"
  , OrderDate = DateTime.Now
  , TotalAmount = 80 
});

 List<OrderDetail> orderDetails = new List<OrderDetail>();
orderDetails.Add(new OrderDetail  
{  DetailID = 1
 , ProductName = "A 1"
 , Qty = 2
 , Price = 5
 , Amount = 10
 , OrderID = 101 
});
orderDetails.Add(new OrderDetail 
{   DetailID = 2
  , ProductName = "B 1"
  , Qty = 5
  , Price = 5
  , Amount = 25
  , OrderID = 102 
});
// Inner Join
var joinedList = (from ord in orders
     join detail in orderDetails on ord.OrderID equals detail.OrderID
    select new { ord.CustomerName, ord.OrderDate, detail.ProductName, detail.Amount });

//Left Outer Join
var joinedList = (from ord in orders
           join detail in orderDetails on ord.OrderID equals detail.OrderID into temp
           from detail in temp.DefaultIfEmpty()
           select new
           {
             ord.CustomerName
             , ord.OrderDate 
             , ProductName = detail == null ? String.Empty : detail.ProductName
             , Amount = detail == null ? null : detail.Amount
           });

Hope it will help you!

Other blogs you may like

Linq dynamic order by a list with example

Sort a list dynamically is easy, in this article we will create a class to sort the source dynamically by passing source, column and sort order. Let’s write our class and then we will see it with example, create a new class DynamicSort public static class DynamicSort { ... By Hamden   On 07 Sep 2013  Viewed: 5,658

Linq to Sql - Sort record on multiple columns in ascending or descending order

In SQL Server it's easy and we are familiar to sort orders on different columns but when it comes to Linq to SQL or Lamda expression we stuck how to sort the records, it is because we are experienced much. So let's discuss some daily use ordering of records with examples. **1**. Suppose we have... By Mike .Net   On 30 Jan 2013  Viewed: 4,979

Linq to SQL connection String to read from web.config file automatically

When we use Linq to SQL it creates a connection string in Settings files. When we need to create the DLL of the DataAccessLayer (DAL) for production server or any server which is not our current development server then we need to change connection string manually in Settings file, is not it would... By Montana Neyra   On 17 Jun 2012  Viewed: 5,111

How to use linq query on a datatable in C#

Is it possible to perform a linq query on datatable, answer is no because datatable’s row collection, DataRowCollection does not implement IEnumerable<T> so we cannot perform linq query on it. But if there is any way to convert our datatable data row collection into IEnumerable, then surely we can.... By Charles Fuller   On 25 Jun 2013  Viewed: 3,975

MVC paging with linq to sql and html helper class

To use paging in MVC you need to first create structure to pass values form page to HTML Helper class so first create a class to keep and pass the page information public class PagingInfo { public int TotalItems { get; set; } public int ItemsPerPage { get; set; } ... By Ali Adravi   On 03 Jan 2013  Viewed: 3,521