I will be using the Northwind database in this example.
Say you get the following requirements:
Show me all the Customers which ordered more than 5 items of Tofu between ’1998-03-06′ and ’1998-06-06′, and the Supplier which supplied the product.
Decoding the above requirements we will see that we need data from quite a few statements. So let’s see how the SQL select statement would look like for this:
select distinct ct.CompanyName CustomerName, s.CompanyName SupplierName
from [Order Details] od
inner
join Orders o
on o.OrderID = od.OrderID
inner
join Products p
on od.ProductID = p.ProductID
inner
join Suppliers s
on p.SupplierID = s.SupplierID
inner
join Customers ct
on ct.CustomerID = o.CustomerID
where o.OrderDate between
’1998-03-06′
and
’1998-06-06′
and p.[ProductName] =
‘Tofu’
and od.Quantity > 5
Quite a long and lumpy join… Something called “lazy loading” in LINQ make this big lumpy select a little bit simpler. You have instant access to the data that is joined to a specific table. Let’s have a look at how we could do this in LINQ.
var q = (from od in nw.Order_Details
where od.Order.OrderDate > DateTime.Parse(“1998-03-06″)
&& od.Order.OrderDate < DateTime.Parse(“1998-06-06″)
&& od.Product.ProductName == “Tofu”
&& od.Quantity > 5
select
new
{
CustomerName = od.Order.Customer.CompanyName,
SupplierName = od.Product.Supplier.CompanyName
}).Distinct();
As you can see we aren’t joining Order_Details up with any other tables. Let’s have a closer look using SQL profiler to see the actual SQL created:
exec
sp_executesql
N’SELECT DISTINCT [t3].[CompanyName] AS [CustomerName], [t4].[CompanyName] AS [SupplierName]
FROM [dbo].[Order Details] AS [t0]
INNER JOIN [dbo].[Orders] AS [t1] ON [t1].[OrderID] = [t0].[OrderID]
INNER JOIN [dbo].[Products] AS [t2] ON [t2].[ProductID] = [t0].[ProductID]
LEFT OUTER JOIN [dbo].[Customers] AS [t3] ON [t3].[CustomerID] = [t1].[CustomerID]
LEFT OUTER JOIN [dbo].[Suppliers] AS [t4] ON [t4].[SupplierID] = [t2].[SupplierID]
WHERE ([t1].[OrderDate] > @p0) AND ([t1].[OrderDate] < @p1) AND ([t2].[ProductName] = @p2) AND ([t0].[Quantity] > @p3)’,N’@p0 datetime,@p1 datetime,@p2 nvarchar(4),@p3 int’,@p0=’1998-03-06 00:00:00′,@p1=’1998-06-06 00:00:00′,@p2=N’Tofu’,@p3=5
Mmmmm…. Two outer joins…. Not what we wanted. Lets us try to change our LINQ query that it doesn’t produce the outer joins. To get past the outer joins we will have to remove some of the “lazy loading”. So the LINQ query will look as follow:
var q = (from od in nw.Order_Details
where od.Quantity > 5
&& od.Order.OrderDate > DateTime.Parse(“1998-03-06″)
&& od.Order.OrderDate < DateTime.Parse(“1998-06-06″)
&& od.Product.ProductName == “Tofu”
join ct in nw.Customers
on od.Order.CustomerID equals ct.CustomerID
join sp in nw.Suppliers
on od.Product.SupplierID equals sp.SupplierID
select
new
{
CustomerName = ct.CompanyName,
SupplierName = sp.CompanyName
}).Distinct();
We still using some of the lazy loading but just joining to Customers and Suppliers to get rid of the outer joins. The SQL query now looks as follow:
exec
sp_executesql
N’SELECT DISTINCT [t3].[CompanyName] AS [CustomerName], [t4].[CompanyName] AS [SupplierName]
FROM [dbo].[Order Details] AS [t0]
INNER JOIN [dbo].[Orders] AS [t1] ON [t1].[OrderID] = [t0].[OrderID]
INNER JOIN [dbo].[Products] AS [t2] ON [t2].[ProductID] = [t0].[ProductID]
INNER JOIN [dbo].[Customers] AS [t3] ON [t1].[CustomerID] = [t3].[CustomerID]
INNER JOIN [dbo].[Suppliers] AS [t4] ON [t2].[SupplierID] = ([t4].[SupplierID])
WHERE ([t0].[Quantity] > @p0) AND ([t1].[OrderDate] > @p1) AND ([t1].[OrderDate] < @p2) AND ([t2].[ProductName] = @p3)’,N’@p0 int,@p1 datetime,@p2 datetime,@p3 nvarchar(4)’,@p0=5,@p1=’1998-03-06 00:00:00′,@p2=’1998-06-06 00:00:00′,@p3=N’Tofu’
Just what we want! I recommend that you always use SQL profiler to inspect your queries and make sure that your SQL select statement looks the way that it should!
