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 );

What have you tried? what doesn't work? The code you presented is not the code that needs love. If you are asking how can you rollup the only answer is by rolling them up unfortunately — zaitsman
– zaitsman, Commented Jul 24, 2020 at 12:26
orderData:{orderCollectedLine1:"line1",orderCollectedLine2:"line2",} - Why would you want orderData to be an object and not array? Also why have orderCollectedLine1. It would be easier to set the output to {"OrderEvent":"Collected","OrderData":["line1","line2"]} — Greg
– Greg, Commented Jul 24, 2020 at 14:09
Hmmm... I think I will delete this question as I need to think about it some more. — user1685639
– user1685639, Commented Jul 24, 2020 at 14:15
The next step is to design how you want to structure your json, create a model class(es) to represent it and then create a method to convert OrderItem list to new data structure. — Greg
– Greg, Commented Jul 24, 2020 at 16:00

Arsen Khachaturyan · Accepted Answer · 2020-07-24 16:17:16Z

2

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<OrderItem>
{
    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();

edited Jul 24, 2020 at 16:17

answered Jul 24, 2020 at 15:59

Arsen Khachaturyan

8,4304 gold badges46 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Greg Over a year ago

Whilst this may works, it gets messy if you want to store and retrieve this object from a datasource. Why not create a model for the output, which can be serialized/ deserialized?

Greg Over a year ago

Also the output in your answer does not match the question. For example see orderCollectedLine1: "line1"

Arsen Khachaturyan Over a year ago

@Greg, you are absolutely correct here. The best practice is to use Models instead of anonymous objects. The reason for not doing that in above example is to make it feel simpler. The other reason is that this is just demonstration of the GroupBy in conjunction with the Select operators. Here, if the answer is what @user1685639 is looking for, I'm encouraging to use real Models in above example instead of anonymous ones.

Arsen Khachaturyan Over a year ago

@Greg for the second comment, I'm mostly sure it was a mistake in the question because the output of the array should be consistent in terms of Data.

user1685639 Over a year ago

@ArsenKhachaturyan This looks like something I can investigate, thanks! Forgive my ignorance but what is the Key in group.Key? I am still getting the hang of linq.

|

Collectives™ on Stack Overflow

C# linq groupby including json

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related