c# - LINQ 中的分层查询

Question

这是一个返回客户列表的简单 LINQ 查询(基于 NorthWind)。每个客户都包含一个订单列表。

from c in Customers
join o in Orders on c.CustomerID equals o.CustomerID into CO
select new {c, CO}

这很好用，生成的 SQL 也很好。现在我想更进一步。我希望每个 Order 对象都包含一个 OrderDetails 列表。我正在使用以下查询:

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 }

此查询有效但会生成可怕的 SQL。为每个客户发出单独的查询。当您查看我们的 lambda 代码时:

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

嵌套的 GroupJoins 似乎是多个 SQL 语句的原因。但是，我尝试了各种组合都没有成功。有什么想法吗？

编辑:我可能对我想要达到的目标有点不清楚。我希望 OrderDetail 对象成为 Order 对象的一个​​属性，而 Order 对象又是 Customer 对象的一个​​属性。我不希望 Order & OrderDetail 成为客户的属性。我正在尝试获取唯一客户的列表。对于每个客户，我希望有一个订单列表，对于每个订单，我都需要一个 OrderDetails 列表。我希望层次结构比我的原始查询更深一层。

Answer 1

如果你想强制执行单个查询，那么你可以在客户端而不是服务器端执行分组:

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