LINQ Group By and select collection

Question

I have this structure

Customer
 - has many Orders
  - has many OrderItems

I want to generate a list of CustomerItems via LINQ given a subset of OrderItems:

List of new { Customer, List<OrderItem> Items }

which is a grouping of all the items a Customer has ordered from the subset of items

How can i use LINQ to back track through the order and group by Customer to generate this object?

so far I'm on something like

items
 .GroupBy(i => i, i => i.Order.Customer, (i, customer) => new {customer, i})

But thats obviously not a List. I'm guessing I need a SelectMany in there somewhere, but could do with some pointers.

score 181 · Accepted Answer · edited Nov 05 '15 at 14:56

181

I think you want:

items.GroupBy(item => item.Order.Customer)
     .Select(group => new { Customer = group.Key, Items = group.ToList() })
     .ToList()

If you want to continue use the overload of GroupBy you are currently using, you can do:

items.GroupBy(item => item.Order.Customer, 
              (key, group) =>  new { Customer = key, Items = group.ToList() })
     .ToList()

...but I personally find that less clear.

edited Nov 05 '15 at 14:56

Adrian Thompson Phillips

6,893
6
38
69

answered May 17 '12 at 14:38

Ani

111,048
26
262
307

do you need the ToList() call on the group ? Would the last ToList() not be enough ? – Joanna Derks May 17 '12 at 15:50
2

@Joanna: According to the OP's requirements, the `Items` property of the anonymous type must be a list. – Ani May 18 '12 at 14:30
Can you please provider some assistance for this: https://stackoverflow.com/questions/44764687/how-to-use-linq-to-group-by-and-order-by-certain-column – Si8 Jun 28 '17 at 14:00
Seems like "group by" does not work anymore in EfCore5 even in the simplest scenarios (`from la in dbContext.LawArticles group la by new { la.TenantId, la.LawArticleId } into aAgg select new { TenantId = aAgg.Key.TenantId, LawArticleId = aAgg.Key.LawArticleId, ArticleTexts = aAgg.ToList() }`, complaining "The LINQ expression 'GroupByShaperExpression ... cannot be translated"... – Alexander Sep 23 '21 at 11:50
@Alexander I solved your problem.I did two separate operations: 1. var list = await _context.SomeTableName.AsNoTracking().ToListAsinc() 2. I use GroupBy and Select as for simple collection. Good luck! – Dmitry Petelko Aug 31 '22 at 09:52

score 11 · Answer 2 · answered Aug 08 '16 at 15:26

11

you may also like this

var Grp = Model.GroupBy(item => item.Order.Customer)
      .Select(group => new 
        { 
             Customer = Model.First().Customer, 
             CustomerId= group.Key,  
             Orders= group.ToList() 
       })
      .ToList();

answered Aug 08 '16 at 15:26

Caner

813
1
12
26

Why is it I get `item.Field<>` and not `.Order` for example? I am using a DataTable. – Si8 Jun 28 '17 at 13:41

score 7 · Answer 3 · answered Nov 21 '15 at 09:57

7

you can achive it with group join

var result = (from c in Customers
          join oi in OrderItems on c.Id equals oi.Order.Customer.Id into g
          Select new { customer = c, orderItems = g});

c is Customer and g is the customers order items.

answered Nov 21 '15 at 09:57

cimey

189
3
10

LINQ Group By and select collection

3 Answers3

Linked