Selecting, Grouping on child list -> Single statement requested.

Question

Example: I have an in-memory list of customers. Every customer has a list of orders. Every order has a list of items. Every item has an item-code.

I need to get a list of items grouped by the itemcode, with beneath it the customers who have ordered this item. If a customer ordered an item twice or more, he should still be shown as a single person.

It's a query that I can do but not in a single LINQ command. Can it be done in a single statement? I don't care that it'd be 20 lines, as long as it's a single query.

This is a challenge! Don't suggest other solutions. ;-)

Answer 1

This gets you a list of objects, each containing one item and a distinct list of customers who bought it:

var items =
   customers
   .SelectMany(c => c.Orders.SelectMany(
      o => o.Items.Select(i => new { item = i, customer = c })
   ))
   .GroupBy(o => o.item.ItemCode)
   .Select(g => new {
      item = g.First().item,
      customers = g.Select(o => o.customer).Distinct()
   });

Test data:

Customer[] customers = {
   new Customer("John Doe", new Order("A", "B")),
   new Customer("Jane Doe", new Order("B", "C", "D"), new Order("B", "D")),
   new Customer("Ford Prefect", new Order("D"), new Order("A", "E"))
};

Result:

A: John Doe, Ford Prefect
B: John Doe, Jane Doe
C: Jane Doe
D: Jane Doe, Ford Prefect
E: Ford Prefect

Answer 2

var query = customers
    .SelectMany
    (
        c => c.Orders
            .SelectMany(o => o.Items)
            .Select(i => new { Customer = c, i.ItemCode })
            .Distinct()
    )
    .GroupBy(x => x.ItemCode, x => x.Customer);

// and to quickly display the results...
foreach (var result in query)
{
    Console.WriteLine("Item code: " + result.Key);

    Console.Write("Bought by: ");
    foreach (var customer in result)
    {
        Console.Write(customer.Name + ", ");
    }

    Console.WriteLine();
    Console.WriteLine("----------");
}