是否可以使用一个查询将LINQ集合分组为字典？

Given a table of order items (OrderItems) that relates to a table of orders (Orders), which in turn relates to a table of users (Users), is it possible to retrieve all the OrderItems and group them into a dictionary by OrderId with just one query? ie. without performing an iteration over either the OrderItems result set or performing a query for each order.

给定一个与订单表(订单)相关的订单商品(OrderItems)表,而这些订单商品又与用户表(用户)相关,是否可以检索所有OrderItem并通过OrderId将它们分组到字典中只有一个查询?即。不对OrderItems结果集执行迭代或对每个订单执行查询。

Desired controller pseudo-code

Dictionary<int,IEnumerable<OrderItem>> OrderItems = DataContext.OrderItems.ToDictionary(Key => oi.OrderId, Value => oi.ToList());

Desired usage:

IEnumerable<OrderItem> currentOrderItems = OrderItems[123]; // where 123 is OrderId

Current Method

In my Controller I presently retrieve a user's orders and order items to pass to the Orders view:

ViewData["Orders"] = (from o in orders
                      where o.UserId equals CurrentUserId
                      orderby o.DateCreated descending)
                     .ToList();

ViewData["OrderItems"] = (from oi in DataContext.OrderItems
                        join o in DataContext.Orders
                        on oi.OrderId equals o.OrderId
                        where o.UserId equals CurrentUserId
                        select oi)
                        .ToList();

Then in my view, I retrieve all order items:

IEnumerable<OrderItem> orderItems = ViewData["OrderItems"] as IEnumerable<OrderItem>;

and use LINQ to group and display each order's order items:

IEnumerable<OrderItem> currentOrderItems = orderItems.Where(
                                            i => i.OrderId == order.OrderId
                                           );

This is fairly efficient as only two queries are passed to the database and some processing is done in the view. But ideally, this should be done in the controller.

这是相当有效的,因为只有两个查询传递给数据库,一些处理在视图中完成。但理想情况下,这应该在控制器中完成。

2 个解决方案

#1

Solved it! With ToLookup(...)

ViewData["OrderItems"] = (from oi in DataContext.OrderItems
                          join o in DataContext.Orders
                          on oi.OrderId equals o.OrderId
                          where o.UserId == UserId
                          select oi).ToLookup(oi => oi.OrderId, oi => oi);

And in my view:

ILookup<int,OrderItem> orderItems = ViewData["OrderItems"] as ILookup<int,OrderItem>;

foreach (Order order in orders)
{
    DisplayOrder(order);

    // Now display this order's items:
    foreach(OrderItem item in orderItems[order.OrderId])
    {
        DisplayOrderItem(item);
    }
}

A trace shows only one query to create the lookup.

跟踪仅显示一个查询以创建查找。

Looks like I'm making a habit out of answering my own questions...

看起来我养成了回答自己问题的习惯......

#2

I think your best bet is to create a method that accepts a lambda for the key and a list to be inserted into the dictionary and then simply enumerates the list and adds to the dictionary using the key provided in the lambda. The method could be an Extension Method of IDictionary and let's call it say, AddRangeWithKeyType()

我认为最好的办法是创建一个方法,接受键的lambda和要插入字典的列表,然后简单地枚举列表并使用lambda中提供的键添加到字典中。该方法可以是IDictionary的扩展方法,让我们称之为AddRangeWithKeyType()