Is it possible to group a LINQ set into a dictionary with one query?

Question

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.

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.

Answer 1

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

Answer 2

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...