C# linq groupby including json

Question

Say I have a list of OrderItems like so:

public class OrderItem
{
    public long Id { get; set; }
    public long OrderNo { get; set; }
    public string OrderEvent { get; set; }
    public string OrderData { get; set; }
}

Each item is being added to a SQL database as an OrderEvent comes through with the OrderData being stored as JSON. The OrderNo is the unique identifier. Currently in is inserting 3 rows into the db and I would like to consolidate it into a single row based on the OrderNo and save the OrderEvent and OrderData in a single JSON field.

How can I rollup all the items into one item with the OrderData containing a JSON array with each OrderEvent and OrderData from each item?

So once rolled up, each OrderData will be stored as JSON like so:

[
   {
        orderEvent: "Start",
        orderData: {
          orderLine1: "line1",
          orderLine2: "line2",
        }
    },
    {
        orderEvent: "Collected",
        orderData: {
          orderCollectedLine1: "line1",
          orderCollectedLine2: "line2",
        }
    },
    {
        orderEvent: "End",
        orderData: {
          orderEndLine1: "line1",
          orderEndLine2: "line2",
        }
    }
]

I have tried using groupBy on the OrderNo but it just gives me all the items. Not sure if I should be using a Select with this.

var data = orders.GroupBy(x => x.OrderNo );

user_806202 · Answer

I'm not sure how much I can guess correctly here, but let's give it a try.

First, let's assume, we may have this input:

var listOfOrders = new List
{
    new OrderItem
    {
        Id = 1,
        OrderData = @"{
                        ""orderLine1"": ""line1"",
                        ""orderLine2"": ""line2"",
                    }",
        OrderEvent = "Start",
        OrderNo = 111
    },
    new OrderItem
    {
        Id         = 1,
        OrderData = @"{
                        ""orderLine1"": ""line1"",
                        ""orderLine2"": ""line2"",
                    }",
        OrderEvent = "Start",
        OrderNo    = 111
    },
    new OrderItem
    {
        Id         = 2,
        OrderData = @"{
                        ""orderLine1"": ""line1"",
                        ""orderLine2"": ""line2"",
                    }",
        OrderEvent = "Start",
        OrderNo    = 121
    }
};

Now, I'm assuming you may want to group these items by OrderNo and get a collection of items with the similar Order Number:

var collection = listOfOrders
    .GroupBy(l => l.OrderNo)
    .Select(group => new
    {
        Number = group.Key, // OrderNo
        OrderEvent = group.Select(elm => new
        {
            OrderEvent = elm.OrderEvent,
            OrderData = JObject.Parse(elm.OrderData) // assume we want to parse a
                                                     // JSON but this can be other logic
        })
    }).ToList();

C# linq groupby including json

1 Answers