Joins in LINQ to SQL
Posted
by rajbk
on ASP.net Weblogs
See other posts from ASP.net Weblogs
or by rajbk
Published on Sat, 13 Mar 2010 04:28:50 GMT
Indexed on
2010/03/13
4:37 UTC
Read the original article
Hit count: 746
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)
© ASP.net Weblogs or respective owner
