Selecting, Grouping on child list -> Single statement requested

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

source : http://stackoverflow.com/questions/1310940/selecting-grouping-on-child-list-single-statement-requested

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s