How to group and merge/flatten a list of anonymous objects in LINQ

Question

I have a list of anonymous objects generated by a LINQ query that I do not have access to modify.

The objects have the following properties:

OrderId, RepId, FirstName, LastName, Address

Each "Rep" often places multiple orders, so there are a lot of rows where the only difference is the OrderId. There is a requirement that if the same Rep has placed multiple orders, to batch these together in groups of 6 with a new structure:

OrderId1, OrderId2, ..., OrderId6, RepId, FirstName, LastName, Address

But if the rep has placed say 8 orders, there would be a batch of 6 and a batch of 2. So the new objects don't always have the same number of properties.

I've started by grouping the initial result set by RepId, but I have no clue where to go next.

Is this possible using LINQ?

Possible duplicate of [Grouping lists into groups of X items per group](http://stackoverflow.com/questions/23921210/grouping-lists-into-groups-of-x-items-per-group) — bit, Aug 02 '16 at 08:00

Chasefornone · Accepted Answer · 2016-08-02T09:28:41.727

As your output have anonymous objects with different schema, that make the thing a little more complicate.

Ideally you should design your entity class to use list for orders instead of property like "OrderId1", "OrderId2"... That is not extensible and error prone. But for that specific question, we can combine LINQ and ExpandoObject to achieve this.

orders.GroupBy(order => order.RepId)
      .SelectMany(orderGroup => orderGroup.Select((order, i) => new  {
                           Order = order,
                           ReqId = orderGroup.Key,
                           SubGroupId = i / 6
                         }))
    .GroupBy(h => new {
      ReqId = h.ReqId,
      SubGroupId = h.SubGroupId,
      FirstName = h.Order.FirstName,
      LastName = h.Order.LastName,
      Address = h.Order.Address
    })
    .Select(orderWithRichInfo => {
       dynamic dynamicObject = new ExpandoObject();

       int i = 1;
       foreach(var o in orderWithRichInfo)
       {
         ((IDictionary<string, object>)dynamicObject).Add("OrderId" + i, o.Order.OrderId);
         i++;
       }

       ((IDictionary<string, object>)dynamicObject).Add("FirstName", orderWithRichInfo.Key.FirstName);
       ((IDictionary<string, object>)dynamicObject).Add("LastName", orderWithRichInfo.Key.LastName);
       ((IDictionary<string, object>)dynamicObject).Add("Address", orderWithRichInfo.Key.Address);
       return dynamicObject;
    });

Hope it helps.

This is perfect thanks. It's an awful requirement I realise, and very error prone, but it's being exported directly to an Excel file which is why I need the Id's flattened into separate columns — Stu Ratcliffe, Aug 02 '16 at 10:07

Adil Mammadov · Answer 2 · 2016-08-02T08:59:01.257

First option.

If you want to get 6 OrderId-s as a list, you can create

class OrderBundle
{
    public int RepId { get; set; }
    public List<int> OrderIds { get; set; }
}

Group your items:

var orderBundels = orderList
   .GroupBy(m => m.RepId)
   .Select(g => new OrderBundle
   {
       RepId = g.Key,
       OrderIds = g.Select(m => m.OrderId).ToList()
   });

And then split them into groups:

List<OrderBundle> dividedOrderBundels = new List<OrderBundle>();
foreach (OrderBundle orderBundle in orderBundels)
{
    int bundelCount = (int)Math.Ceiling(orderBundle.OrderIds.Count() / 6.0);
    for (int i = 0; i < bundelCount; i++)
    {
        OrderBundle divided = new OrderBundle
        {
            RepId = orderBundle.RepId,
            OrderIds = orderBundle.OrderIds.Skip(i * 6).Take(6).ToList()
        };
        dividedOrderBundels.Add(divided);
    }
}

Second option:

You can achieve the same result without creating model like below:

var result = orderList
    .GroupBy(m => m.RepId)
    .SelectMany(g => g.Select((m, i) => new
    {
        RepId = g.Key,
        FirstName = m.FirstName,
        LastName = m.LastName,
        Address = m.Address,
        OrderId = m.OrderId,
        BunleIndex = i / 6
    }))
    .GroupBy(m => m.BunleIndex)
    .Select(g => new
    {
        RepId = g.Select(m => m.RepId).First(),
        FirstName = g.Select(m => m.FirstName).First(),
        LastName = g.Select(m => m.LastName).First(),
        Address = g.Select(m => m.Address).First(),
        OrderIds = g.Select(m => m.OrderId).ToList()
    })
    .ToList()

How to group and merge/flatten a list of anonymous objects in LINQ

2 Answers2

First option.

Second option: