Joins in LINQ to SQL - Raj Kaimal

Joins in LINQ to SQL

The following post shows how to write different types of joins in LINQ to SQL. I am using the Northwind database and LINQ to SQL for these examples.

NorthwindDataContext dataContext = new NorthwindDataContext();

Inner Join
var q1 = from c in dataContext.Customers
         join o in dataContext.Orders on c.CustomerID equals o.CustomerID
         select new
         {
             c.CustomerID,
             c.ContactName,
             o.OrderID,
             o.OrderDate
         };
SELECT [t0].[CustomerID], [t0].[ContactName], [t1].[OrderID], [t1].[OrderDate]
FROM [dbo].[Customers] AS [t0]
INNER JOIN [dbo].[Orders] AS [t1] ON [t0].[CustomerID] = [t1].[CustomerID]

Left Join
var q2 = from c in dataContext.Customers
         join o in dataContext.Orders on c.CustomerID equals o.CustomerID into g
         from a in g.DefaultIfEmpty()
         select new
         {
             c.CustomerID,
             c.ContactName,
             a.OrderID,
             a.OrderDate
         };
SELECT [t0].[CustomerID], [t0].[ContactName], [t1].[OrderID] AS [OrderID], [t1].[OrderDate] AS [OrderDate]
FROM [dbo].[Customers] AS [t0]
LEFT OUTER JOIN [dbo].[Orders] AS [t1] ON [t0].[CustomerID] = [t1].[CustomerID]


Inner Join on multiple
//We mark our anonymous type properties as a and b otherwise
//we get the compiler error "Type inferencce failed in the call to 'Join’
 
 
var q3 = from c in dataContext.Customers
         join o in dataContext.Orders on new { a = c.CustomerID, b = c.Country } equals new { a = o.CustomerID, b = "USA" }
         select new
         {
             c.CustomerID,
             c.ContactName,
             o.OrderID,
             o.OrderDate
         };
SELECT [t0].[CustomerID], [t0].[ContactName], [t1].[OrderID], [t1].[OrderDate]
FROM [dbo].[Customers] AS [t0]
INNER JOIN [dbo].[Orders] AS [t1] ON ([t0].[CustomerID] = [t1].[CustomerID]) AND ([t0].[Country] = @p0)

Inner Join on multiple with ‘OR’ clause
var q4 = from c in dataContext.Customers
         from o in dataContext.Orders.Where(a => a.CustomerID == c.CustomerID || c.Country == "USA")
         select new
         {
             c.CustomerID,
             c.ContactName,
             o.OrderID,
             o.OrderDate
         };
SELECT [t0].[CustomerID], [t0].[ContactName], [t1].[OrderID], [t1].[OrderDate]
FROM [dbo].[Customers] AS [t0], [dbo].[Orders] AS [t1]
WHERE ([t1].[CustomerID] = [t0].[CustomerID]) OR ([t0].[Country] = @p0)


Left Join on multiple with ‘OR’ clause
var q5 = from c in dataContext.Customers
         from o in dataContext.Orders.Where(a => a.CustomerID == c.CustomerID || c.Country == "USA").DefaultIfEmpty()
         select new
         {
             c.CustomerID,
             c.ContactName,
             o.OrderID,
             o.OrderDate
         };
SELECT [t0].[CustomerID], [t0].[ContactName], [t1].[OrderID] AS [OrderID], [t1].[OrderDate] AS [OrderDate]
FROM [dbo].[Customers] AS [t0]
LEFT OUTER JOIN [dbo].[Orders] AS [t1] ON ([t1].[CustomerID] = [t0].[CustomerID]) OR ([t0].[Country] = @p0)

Published Friday, March 12, 2010 10:28 PM by rajbk
Filed under: , , ,

Comments

# re: Joins in LINQ to SQL

I still like write pure sql to do that ;)

Monday, March 15, 2010 5:02 AM by jack.niu

# re: Joins in LINQ to SQL

Thanks for this post. I have been looking for articles on Linq-to-sql joins.

Monday, March 15, 2010 11:32 AM by Phil