Reputation: 6738
Here is a simple LINQ query (based on NorthWind) that returns a list of Customers. Each customer contains a list of Orders.
from c in Customers
join o in Orders on c.CustomerID equals o.CustomerID into CO
select new {c, CO}
This works fine and the SQL generated is also fine. Now I want to go one step further. I want each Order object to contain a list of OrderDetails. I am using the following query:
from c in Customers
join od in (
from o in Orders
join od in OrderDetails on o.OrderID equals od.OrderID into OD
select new { o.CustomerID, o, OD }
)
on c.CustomerID equals od.CustomerID into COD
select new { c, COD }
This query works but generates horrible SQL. A separate query is issued for each Customer. When you look at the lambda code we have:
Customers
.GroupJoin (
Orders
.GroupJoin (
OrderDetails,
o => o.OrderID,
od => od.OrderID,
(o, OD) =>
new
{
CustomerID = o.CustomerID,
o = o,
OD = OD
}
),
c => c.CustomerID,
od => od.CustomerID,
(c, COD) =>
new
{
c = c,
COD = COD
}
)
The nested GroupJoins seem to be the cause of the multiple SQL stataments. However, I have tried various combinations without success. Any ideas?
EDIT: I may have been a little unclear about what I was trying to achieve. I want the OrderDetail object to be a property of the Order object, which is in turn a property of the Customer object. I do not want Order & OrderDetail to be properties of Customer. I am trying to get list of unique customers. For each customer I expect to a list of Orders and for each Order I want a list of OrderDetails. I want the hierarchy to go one level deeper than my original query.
Upvotes: 3
Views: 4081
Reputation: 62990
If you want to force a single query, then you can perform the grouping on client side not on serverside:
from a in (from c in Customers
join o in Orders on c.CustomerID equals o.CustomerID
join od in OrderDetails on o.OrderID equals od.OrderID
select new {c, o, od}).AsEnumerable()
group a by a.c into g
select new { Customer = g.Key, Orders = g.Select(o => o.o) , OrderDetails = g.Select(od => od.od)}
The Generated SQL is:
SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax], [t1].[OrderID], [t1].[CustomerID] AS [CustomerID2], [t1].[EmployeeID], [t1].[OrderDate], [t1].[RequiredDate], [t1].[ShippedDate], [t1].[ShipVia], [t1].[Freight], [t1].[ShipName], [t1].[ShipAddress], [t1].[ShipCity], [t1].[ShipRegion], [t1].[ShipPostalCode], [t1].[ShipCountry], [t2].[OrderID] AS [OrderID2], [t2].[ProductID], [t2].[UnitPrice], [t2].[Quantity], [t2].[Discount]
FROM [Customers] AS [t0]
INNER JOIN [Orders] AS [t1] ON [t0].[CustomerID] = [t1].[CustomerID]
INNER JOIN [Order Details] AS [t2] ON [t1].[OrderID] = [t2].[OrderID]
Bear in mind that this is not faster than multiple queries and at the same time it increases the network and server load.
My recommendation would be to use a query like the following:
DataLoadOptions opt = new DataLoadOptions();
opt.LoadWith<Orders>(o => o.OrderDetails);
this.LoadOptions = opt;
from c in Customers
select new {c, Orders = c.Orders, OrderDetails = c.Orders.SelectMany( o=> o.OrderDetails)}
Upvotes: 1
Reputation: 12621
It appears that linq2sql cannot handle more than one table span efficiently (by design?):
Upvotes: 0
Reputation: 77620
You might try to join down to the OrderDetails on the server and then "regroup" on the client to get your full hierarchical data structure:
var q = from c in Customers
join o in Orders on c.CustomerID equals o.CustomerID
join od in OrderDetails on o.OrderID equals od.OrderID into OD
select new { c, o, OD };
var res = from x in q.AsEnumerable()
group x by x.c.CustomerID into g
select new
{
Customer = g.First().c,
Orders = g.Select(y => new
{
Order = y.o,
OrderDetails = y.OD
})
};
Upvotes: 0
Reputation: 95223
Why not just use multiple joins:
from c in Customers
join o in Orders on c.CustomerID equals o.CustomerID
join od in OrderDetails on o.OrderID equals od.OrderID
select new {c, o, od}
Upvotes: 0