Serialize C# object into JSON which contains a List

Question

What I'm trying to do is to get some data from my database (customers order with all items he ordered) convert it to JSON that contains a list of said items so I can send it with a POST request. I want to create a JSON object which would contain details of customers order, and a list of products contained in that order. This is how I want my JSON to look like:

{
  "account_code": "00000",       
  "advance_payment": false,       
  "date": [today's date],
  "order_items": 
   [
     {
       "item_id": 0, 
       "product_Price ": "105.00",  
       "order_Item_Rebate: "",     
       "product_code": "000001",   
       "quantity": 1
     },
     {
       "item_id": 1, 
       "product_Price ": "55.00",  
       "price": "",     
       "product_code": "000002",   
       "quantity": 5
     },
     {
       "item_id": 2, 
       "product_Price ": "15.00",  
       "price": "",     
       "product_code": "000003",   
       "quantity": 3
     }
   ]
}

But what I'm getting is this:

{
  "account_code": "00000",       
  "advance_payment": false,       
  "date": [today's date],
  "order_items": 
   [
     {
       "item_id": 0, 
       "roduct_Price ": "105.00",  
       "order_Item_Rebate: "",     
       "product_code": "000001",   
       "quantity": 1
     }
   ]
},
{
  "account_code": "00000",       
  "advance_payment": false,       
  "date": [today's date],
  "order_items": 
   [
     {
       "item_id": 1, 
       "roduct_Price ": "55.00",  
       "price": "",     
       "product_code": "000002",   
       "quantity": 5
     }
   ]
},
{
  "account_code": "00000",       
  "advance_payment": false,       
  "date": [today's date],
  "order_items": 
   [
     {
       "item_id": 2, 
       "product_Price ": "15.00",  
       "price": "",     
       "product_code": "000003",   
       "quantity": 3
     }
   ]
}

Instead of one JSON object with a list of items, I get multiple JSON object for the same customer(account_code).

I'm getting this data from database using ADO.NET and I read this data with SqlDataReader. Inside SqlDataReader I created a new object and filled it with details about my order. It all works, but problem is order_items which should be a list of all items from the order. I want one order with multiple items in it, not a different order for every item. Using code below I am able to get data from my database, but I don't know how to group order_items with the order they should be in.

List<OrderModel> orderList = new List<OrderModel>();
using (SqlDataReader rdr = await cmd.ExecuteReaderAsync())
                    {
                        while (rdr.Read())
                        {
                            OrderModel order = new OrderModel()
                            {
                                Account_code = rdr[0].ToString(),
                                Advance_payment = false,
                                Date = DateTime.Now.Date,
                                Order_Items = new List<Order_items>()
                                    {
                                     //Here is where I should do something instead of creating new
                                     //Order_items, but I don't know what
                                        new Order_items()
                                        {
                                            Order_Item_Rebate = rdr[1].ToString(),
                                            Product_Price = rdr[2].ToString(),
                                            Product_Code = rdr[3].ToString(),
                                            Quantity = rdr[4].ToString()
                                        }
                                    }

                            };
                            orderList.Add(order);
                        }
                        var jsonString = JsonConvert.SerializeObject(orderList);
                        return jsonString;
                    }

My OrderModel class looks like this:

public class OrderModel
    {
        public string Account_code { get; set; } 
        public bool Advance_payment { get; set; } 
        public DateTime Date { get; set; }
        public List<Order_items> Order_Items { get; set; } 
    }

    public class Order_items
    {
        public string Order_Item_Rebate { get; set; } 
        public string Product_Price { get; set; } 
        public string Product_Code { get; set; }
        public string Quantity { get; set; }
    }
  

And table from my database looks like this:

Account_code	Order_Item_Rebate	Product_Price	Product_Code	Quantity
00000	10	105	000001	1
00000	5	55	000002	5
00000	15	15	000002	3

I have seen similar questions and people were advising to use an array of dictionaries, but I'm not sure if I understand how would they help. I'm new with this, so I'm sorry if question is not really smart.

Answer 1

The SQL query will result in "flattened" data, so you need to group it yourself. Something along this lines:

var orderDict = new Dictionary<string, OrderModel>(); // mapping from account code to order object
while (rdr.Read())
{
   var accCode = rdr[0].ToString();
   
   // if no object previously added - add a new one 
   if(!orderDict.TryGetValue(accCode, out var order))
   {
       order = new OrderModel()
       {
           Account_code = accCode ,
           Advance_payment = false,
           Date = DateTime.Now.Date,
           Order_Items = new List<Order_items>()
       };
       orderDict[accCode] = order;
   }

   // add details from current result line to order 
   order.Order_Items.Add(new Order_items() {...});
}

var jsonString = JsonConvert.SerializeObject(orderDict.Values); // serialize only values, not the whole dictionary

Answer 2

So, the problem is that you're creating a new OrderModel in each iteration of the while (rdr.Read()) loop. The most straight forward way to approach this would be to simply check for an existing OrderModel in orderList based on the Account_code, and create a new instance of OrderModel only if you don't find one. For example...

List<OrderModel> orderList = new List<OrderModel>();
using (SqlDataReader rdr = await cmd.ExecuteReaderAsync())
{
    while (rdr.Read())
    {
        // Check to see if there is already an instance of OrderModel with the
        // account code in the list.
        OrderModel order = orderList.FirstOrDefault(om => om.Account_code == rdr[0].ToString());
        if (order == null)
        {
            // If there isn't create a new instance of OrderModel and add it to
            // the list.
            order = new OrderModel
            {
                Account_code = rdr[0].ToString(),
                Advance_payment = false,
                Date = DateTime.Now.Date,
                Order_Items = new List<Order_items>()
            };
            orderList.Add(order);
        }
        
        // Now, just add the new item to Order_Items.
        order.Order_Items.Add(new Order_item
        {
            Order_Item_Rebate = rdr[1].ToString(),
            Product_Price = rdr[2].ToString(),
            Product_Code = rdr[3].ToString(),
            Quantity = rdr[4].ToString()
        });
    }
    var jsonString = JsonConvert.SerializeObject(orderList);
    return jsonString;
}

The performance might be poor, especially as orderList grows larger, since you're iterating orderList once per iteration of the while loop. But, this basic approach should work. Instead of creating one OrderModel per iteration of the while loop, you need to make sure that you only create on per Account_code in your result set.

If you have control over the query itself, you can order the results by Account_code to address the performance.

List<OrderModel> orderList = new List<OrderModel>();
using (SqlDataReader rdr = await cmd.ExecuteReaderAsync())
{
    OrderModel order = null;
    while (rdr.Read())
    {
        if (order == null || order.Account_code != rdr[0].ToString())
        {
            // The order is null, meaning that this is the first order model to be processed.
            // Or, the order's account code is different than the current account code on the result set, meaning we have begun processing a new order.
            // In either case, you need to add a new order to the list so
            // you can start filling in its items.
            order = new OrderModel
            {
                Account_code = rdr[0].ToString(),
                Advance_payment = false,
                Date = DateTime.Now.Date,
                Order_Items = new List<Order_items>()
            };
            orderList.Add(order);
        }        
        
        // Now, just add the new item to Order_Items.
        order.Order_Items.Add(new Order_item
        {
            Order_Item_Rebate = rdr[1].ToString(),
            Product_Price = rdr[2].ToString(),
            Product_Code = rdr[3].ToString(),
            Quantity = rdr[4].ToString()
        });
    }
    var jsonString = JsonConvert.SerializeObject(orderList);
    return jsonString;
}

The above approach will only work if you can order the results of the query by account code.

Answer 3

1. Define your order variable outside the while loop.

var order = new OrderModel()
{
    Account_code = rdr[0].ToString(),
    Advance_payment = false,
    Date = DateTime.Now.Date,
    Order_Items = new List<Order_items>()
};        

2. Parse order items inside the while loop

using (SqlDataReader rdr = await cmd.ExecuteReaderAsync())
{
    while (rdr.Read())
    {
        var item = new Order_items()
        {
            Order_Item_Rebate = rdr[1].ToString(),
            Product_Price = rdr[2].ToString(),
            Product_Code = rdr[3].ToString(),
            Quantity = rdr[4].ToString()
        };
        order.Order_Items.Add(item);
    }
}

3. Serialize the only OrderModel after the loop

var jsonString = JsonConvert.SerializeObject(order);
return jsonString;