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
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!
![]() |
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
|
By Hamden On 25 Sep, 13 Viewed: 38,940 |
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
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
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
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
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