All geek questions in one place

Hierarchical Queries in LINQ

Here is a simple LINQ query (based on NorthWind) that returns a list of clients. 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 great, and the generated SQL is fine too. Now I want to take it one step further. I want each Order object to contain an OrderDetails list. 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. Each client is issued a separate request. 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
         }
   )

Nested GroupJoins appear to be the cause of multiple SQL stataments. However, I tried various combinations without success. Any ideas?

EDIT: , , . , OrderDetail Order, , , Customer. , Order OrderDetail . . OrderDetails. , , .

+3
4

, :

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

SQL:

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]

, , , .

:

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)}
+1

:

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}
0

OrderDetails "" , :

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

Source: https://habr.com/ru/post/1716871/

More articles:


All Articles