How to group list and split every n records?

Question

I would like to know how to group item and split every N record by using LINQ

# |ITEM  |QUANTITY
==================
1 |ITEM01| 10
2 |ITEM01| 10
3 |ITEM01| 10
.     .     .
.     .     .
22|ITEM01| 10
23|ITEM02| 50
24|ITEM02| 50

Suppose there's a list with 23 ITEM001 and 2 ITEM002

How to get

ITEM  |QUANTITY
=================
ITEM001 | 100
ITEM001 | 100
ITEM001 | 20
ITEM002 | 100

Group by ITEM, if grouped > 10, go to next

Is there any way to achieve it? Thanks for you help!

Thanks for those nice guys help! Further question, now i would like to group the list like (grouped every 10 records, after grouping, if count does not reach 10, do not group), Sorry for my poor English :(

ITEM  |QUANTITY
=================
ITEM01 | 100
ITEM01 | 100
ITEM01 | 10
ITEM01 | 10
ITEM01 | 10
ITEM02 | 50
ITEM02 | 50

Thanks for your help again!

possible duplicate of [How to use Linq to group every N number of rows](http://stackoverflow.com/questions/860305/how-to-use-linq-to-group-every-n-number-of-rows) — Rawling, Jan 22 '14 at 12:57
Maybe third item should be `ITEM001 | 30` if you have 23 ITEM001? — Sergey Berezovskiy, Jan 22 '14 at 13:48
Yes. My typing mistake. further question, how to group list if count not >= 10? In result, ITEM01 : 100, ITEM01 : 100, ITEM01:10, ITEM01 : 10, ITEM01 : 10, ITEM02 : 50, ITEM02 : 50 — Wayne_Wall, Jan 23 '14 at 08:30

Sergey Berezovskiy · Accepted Answer · 2014-01-22T14:34:09.813

var query = 
    items.GroupBy(item => item.Name)
         .SelectMany(g => g.Select((item, index) => new { item, index })
                           .GroupBy(x => x.index / 10)
                           .Select(batch => new Item { 
                                 Name = batch.First().item.Name, 
                                 Quantity = batch.Sum(x => x.item.Quantity) 
                            })).OrderBy(item => item.Name);

Group all items by name. That will give you two groups for your sample data.
Split each group into batches by 10 items, and select new aggregated item from each batch (batch can contain less than 10 items, as thirds batch for ITEM001).
Order results by item name.

This query can be simplified if you will use MoreLINQ (available from NuGet) Batch extension or write your own one:

var query = 
    items.GroupBy(item => item.Name)
         .SelectMany(g => g.Batch(10)
                           .Select(batch => new Item { 
                                 Name = batch.First().Name, 
                                 Quantity = batch.Sum(item => item.Quantity) 
                            })).OrderBy(item => item.Name);

Assume you have item class like this

public class Item
{
    public string Name { get; set; }
    public int Quantity { get; set; }
}

Then this sample list of items (constructed with NBuilder):

var items = Builder<Item>.CreateListOfSize(32)
                         .TheFirst(23)
                             .With(i => i.Name = "ITEM01")
                             .And(i => i.Quantity = 10)
                         .TheNext(9)
                             .With(i => i.Name = "ITEM02")
                             .And(i => i.Quantity = 50)
                         .Build();

Will give result:

[
  { Name: "ITEM01", Quantity: 100 },  
  { Name: "ITEM01", Quantity: 100 },
  { Name: "ITEM01", Quantity: 30  },
  { Name: "ITEM02", Quantity: 450 }
]

NOTE: @Thejaka solution will give you five items in this case - there will be two ITEM02 items with quantity 350 and 100.

@KonradKokosa as I can see from sample, OP want items grouped by name in batches of max size 10 (i.e. it refers to items count instead of total quantity) — Sergey Berezovskiy, Jan 22 '14 at 14:49
It works! But may i ask further question? how to group list if count not >= 10? Expect result, ITEM01 : 100, ITEM01 : 100, ITEM01:10, ITEM01 : 10, ITEM01 : 10, ITEM02 : 50, ITEM02 : 50 — Wayne_Wall, Jan 23 '14 at 08:33

Konrad Kokosa · Answer 2 · 2014-01-22T16:51:20.080

Note: Below answer groups items by sum, not count. Nevertheless, I leave it here for future reference. Maybe someone will ever have similar problem.

You can achieve it with the following LINQ query:

List<Item> items = new List<Item>()
{
    new Item() { Name = "Item01", Quantity = 40 }, // 130
    new Item() { Name = "Item01", Quantity = 70 },
    new Item() { Name = "Item01", Quantity = 10 },
    new Item() { Name = "Item01", Quantity = 10 },
    new Item() { Name = "Item02", Quantity = 50 }, // 100
    new Item() { Name = "Item02", Quantity = 50 },
    new Item() { Name = "Item03", Quantity = 10 }  // 10
};

var result =
    // Group by Name, calculate total sum for each group
    items.GroupBy(i => i.Name, (k, v) => new Item()
    {
        Name = k,
        Quantity = v.Sum(i => i.Quantity)
    })
    // Split groups into 100 packages
    .SelectMany(i => Enumerable.Range(0, (int)Math.Ceiling(i.Quantity / 100.0))
                               .Select(n => new Item()
    {
        Name = i.Name,
        Quantity = i.Quantity > ((n + 1) * 100) ? 100 : i.Quantity - n * 100
    }))
    .ToList();

Then you have four elements in the list:

Item01 100
Item01 30
Item02 100
Item03 10

He wants it split by count (each grouping should only have 10 elements), not by sum. — Sven Grosen, Jan 22 '14 at 13:43

How to group list and split every n records?

2 Answers2