Linq Inner Join

Linq Inner Join

Linq Inner Join Example.

Here is very simple inner join example using Linq query expression. We will Inner join Order Table and Order Details table.

North wind database contains Orders and Order Details table. Order ID is primary key of Orders table and it's a foreign key of Order Details table. Let's join these two tables on Order ID.

  1. Linq Chapter 1 – Split string to list of strings.
  2. Linq Chapter 2 – Remove duplicates from list.
  3. Linq Chapter 3 – Linq Where Clause.
  4. Linq Chapter 4 – Linq Group by multiple clause.
  5. Linq Chapter 5 – Linq Inner Join query.
  6. Linq Chapter 6 – Linq Distinct list.
  7. Linq Chapter 7 – Linq OfType Method.

Linq inner join c#.net example.

from orderList in Orders

join orderDetailsList in OrderDetails

on orderList.OrderID equals orderDetailsList.OrderID

select new 
{ 

City = orderList.ShipCity
, ProductID = orderDetailsList.ProductID
, QTY = orderDetailsList.Quantity  

}

VB.net Linq Inner join.
From orderList In OrdersJoin orderDetailsList In OrderDetails On orderList.OrderID = orderDetailsList.OrderIDNew With { _
	Key .City = orderList.ShipCity, _
	Key .ProductID = orderDetailsList.ProductID, _
	Key .QTY = orderDetailsList.Quantity _
}
Inner join Extension.
Orders
   .Join (
      OrderDetails, 
      orderList => orderList.OrderID, 
      orderDetailsList => orderDetailsList.OrderID, 
      (orderList, orderDetailsList) => 
         new  
         {
            City = orderList.ShipCity, 
            ProductID = orderDetailsList.ProductID, 
            QTY = orderDetailsList.Quantity
         }
   )
Inner join example output. Linq innerJoine example.

Linq Inner Query.

The equivalent SQL query generated by Linq is.

SELECT [t0].[ShipCity] AS [City], [t1].[ProductID], [t1].[Quantity] AS [QTY]
FROM [Orders] AS [t0]
INNER JOIN [Order Details] AS [t1] ON [t0].[OrderID] = [t1].[OrderID]

Linq inner select.

In Select statement you can add multiple columns and Give each column anonymous name.
select new 
{ 

City = orderList.ShipCity
, ProductID = orderDetailsList.ProductID
, QTY = orderDetailsList.Quantity  

}

Linq Inner Join on Multiple Tables.

The example above joins only 2 table which is not sufficient to get complete order details. Let’s join multiple tables to get complete order details. We will join Order Details, Orders, Customer and Products table of north wind database.

from orderList in Orders

join orderDetailsList in OrderDetails
on orderList.OrderID equals orderDetailsList.OrderID

join productList in Products
on orderDetailsList.ProductID equals productList.ProductID

join customerList in Customers 
on orderList.CustomerID equals customerList.CustomerID

select new 
{ 
 CustomerName = customerList.CompanyName
,City = orderList.ShipCity
, ProductID = productList.ProductName
, QTY = orderDetailsList.Quantity  

}

VB.net Linq inner join on multiple tables example.
From orderList In OrdersJoin orderDetailsList In OrderDetails On orderList.OrderID = orderDetailsList.OrderIDJoin productList In Products On orderDetailsList.ProductID = productList.ProductIDJoin customerList In Customers On orderList.CustomerID = customerList.CustomerIDNew With { _
	Key .CustomerName = customerList.CompanyName, _
	Key .City = orderList.ShipCity, _
	Key .ProductID = productList.ProductName, _
	Key .QTY = orderDetailsList.Quantity _
}

Output.

Linq Inner Join On Multiple Table.

Extension method join multiple tables.

Orders
   .Join (
      OrderDetails, 
      orderList => orderList.OrderID, 
      orderDetailsList => orderDetailsList.OrderID, 
      (orderList, orderDetailsList) => 
         new  
         {
            orderList = orderList, 
            orderDetailsList = orderDetailsList
         }
   )
   .Join (
      Products, 
      temp0 => temp0.orderDetailsList.ProductID, 
      productList => productList.ProductID, 
      (temp0, productList) => 
         new  
         {
            temp0 = temp0, 
            productList = productList
         }
   )
   .Join (
      Customers, 
      temp1 => temp1.temp0.orderList.CustomerID, 
      customerList => customerList.CustomerID, 
      (temp1, customerList) => 
         new  
         {
            CustomerName = customerList.CompanyName, 
            City = temp1.temp0.orderList.ShipCity, 
            ProductID = temp1.productList.ProductName, 
            QTY = temp1.temp0.orderDetailsList.Quantity
         }
   )
Linq to SQL query Join on multiple tables.
SELECT [t3].[CompanyName] AS [CustomerName], [t0].[ShipCity] AS [City], [t2].[ProductName] AS [ProductID], [t1].[Quantity] AS [QTY]
FROM [Orders] AS [t0]
INNER JOIN [Order Details] AS [t1] ON [t0].[OrderID] = [t1].[OrderID]
INNER JOIN [Products] AS [t2] ON [t1].[ProductID] = [t2].[ProductID]
INNER JOIN [Customers] AS [t3] ON [t0].[CustomerID] = [t3].[CustomerID]

Tags:

Linq Group By Multiple ColumnsLinq Split String To ListLearn Linq Step By StepHow to select multiple columns in Linq- C#.net VB.net codeLinq Distinct ListLinq Get Duplicate Count Of Words From StringLinq Union and intersect ExampleC# ArrayList Search using Linq Extension OfType MethodLinq ToDictionary ExtensionLinq to SqlLinq Array Order-By

Author

My name is Satalaj, but people call me Sat. Here is my homepage: . I live in Pune, PN and work as a Software Engineer. I'm former MVP in ASP.net year 2010.
Disclaimer: Views or opinion expressed here are my personal research and it has nothing to do with my employer. You are free to use the code, ideas/hints in your projects. However, you should not copy and paste my original content to other web sites. Feel free to copy or extend the code.
If you want to fight with me, this website is not for you.
 

I'm Satalaj.