c# - 从JOIN中选择IEnumerable会导致复杂的Linq-to-SQL查询

我有一个查询，看起来像这样：

var orderLines = from Order order in _orders
                 join OrderItem orderItem in dbc.OrderItems
                     on order.OrderId equals orderItem.OrderId
                 join Product product in dbc.Products
                     on orderItem.ProductId equals product.ProductId
                 join OrderItemQuantity oiq in dbc.OrderItemQuantities
                     on orderItem.OrderItemId equals oiq.OrderItemId
                     into orderItemQuantities
                 join ActivityQuantity aq in dbc.ActivityQuantities
                     on orderItem.OrderItemId equals aq.OrderItemId
                     into activityQuantities
                 orderby
                     order.OrderId ascending,
                     orderItem.OrderLineNumber ascending
                 select new {
                     Order = order,
                     Item = orderItem,
                     Product = product,
                     // I'd like to get these child items as IEnumerables or similar
                     ItemQuantities = orderItemQuantities,
                     ActivityQuantities = activityQuantities
                 };

这样可以很好地编译，但是导致查询中缺少orderItemQuantities和activityQuantities部分，因此我为订单/商品/产品获得了一个单独的select / join / join，并对每个条目在OIQ / AQ上进行了单独选择：

SELECT (...) FROM [ORDERS] AS t0
INNER JOIN [ORDER_ITEMS] AS t1 ON t0.ORDER_ID = t1.ORDER_ID
INNER JOIN [PRODUCTS] AS t2 ON t1.PRODUCT_ID = t2.PRODUCT_ID
ORDER BY (...)

然后，对于这些行中的每一行，执行以下查询：

SELECT (...) FROM [ACTIVITY_QUANTITY] as t0
WHERE t0.ORDER_ITEM_ID = @p0

SELECT (...) FROM [ORDER_ITEM_QUANTITY] as t0
WHERE t0.ORDER_ITEM_ID = @p0

由于我有成千上万的行，因此导致查询数量荒谬。

有没有一种方法可以将OrderItemQuantity的ActivityQuantity和OrderItem条目合并为IEnumerable（或类似名称），从而使它们可以轻松地从选择中使用的匿名类型进行访问？

最佳答案

不知道这是否适用于LINQ to SQL，但是您可以尝试

             select new {
                 Order = order,
                 Item = orderItem,
                 Product = product,
                 // I'd like to get these child items as IEnumerables or similar
                 ItemQuantities = orderItemQuantities.ToList(),
                 ActivityQuantities = activityQuantities.ToList()
             };

我相当确定可以在Entity Framework中使用。

关于c# - 从JOIN中选择IEnumerable会导致复杂的Linq-to-SQL查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/12894015/