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 NameOrder DateProduct NameAmount
AAA9/25/2013 12:39:21 AMA 110.00
BBB9/25/2013 12:39:21 AMB 125.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 NameOrder DateProduct NameAmount
AAA9/25/2013 12:36:02 AMA 110
BBB9/25/2013 12:36:02 AMB 125
CCC9/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!

Hamden Process manager with a reputed organization, Fond of learning new features and technology related to C#, ASP.Net, SQL Server, MVC etc.I like to help others, if I can
  • linq
  • c#
By Hamden On 25 Sep, 13  Viewed: 38,940

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,570

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,736

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,005

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,899

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,500