Thursday, November 17, 2011

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)