Hierarchical queries in LINQ

Question

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.

Answer 1

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}

Answer 2

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
                       })
          };

Answer 3

It appears that linq2sql cannot handle more than one table span efficiently (by design?):

http://codebetter.com/blogs/david.hayden/archive/2007/08/06/linq-to-sql-query-tuning-appears-to-break-down-in-more-advanced-scenarios.aspx

http://social.msdn.microsoft.com/forums/en-US/adodotnetentityframework/thread/23bc2a58-ff1e-4857-81ae-507bb1a2754d/

Answer 4

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]

Bare 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)}