Create desired json array of data from generic list

Question

I have two tables in my database Stores and Products with the following fields

TBL_Store

StoreID (Primary Key)
StoreName

TBL_PRODUCT

ProductID (Primary Key)
StoreID   (Foreign Key)
ProductName
INT_TYPE

I am using the following query to create JSON array

   var data = context.tbl_product.Where(x => x.INT_TYPE == 1).ToList();                    
            var json = JsonConvert.SerializeObject(data, Formatting.Indented,
                        new JsonSerializerSettings() {
                            ReferenceLoopHandling = ReferenceLoopHandling.Ignore
                        });

It gives me all relational tables data with Repeated Entries. I want my json to be something like this

[{
"Store": {
    "storeid": "1",
    "storename": "Nike",
    "Products": [{
        "ProdID": "1",
        "prodName": "NikeShoes1"
    }, {
        "ProdID": "2",
        "prodName": "NikeShoes2"
    }, {
        "ProdID": "3",
        "prodName": "NikeShoes3"
    }]
},
"Store": {
    "storeid": "2",
    "storename": "Biba",
    "Products": [{
        "ProdID": "1",
        "prodName": "Biba1"
    }, {
        "ProdID": "2",
        "prodName": "Biba2"
    }, {
        "ProdID": "3",
        "prodName": "Biba3"
    }]
}
  }]

Alex Art. · Accepted Answer · 2016-01-07 11:18:53Z

Assuming that you have a Store navigation property in your tbl_product

Create helper classes:

[JsonObject(MemberSerialization.OptIn)]
public class Product
{ 
    [JsonProperty("prodName")]
    public string ProductName {get;set;}

    [JsonProperty("ProdID")]
    public int ProductId {get;set;}
}

[JsonObject(MemberSerialization.OptIn)]
public class Store
{
     [JsonProperty("storeid")]
     public string StoreId { get; set; }

     [JsonProperty("storename")]
     public string StoreName { get; set; }

     [JsonProperty("Products")]
     public IList<Product> Products { get; set; }
}

Get the data in the required format (Notice Include method that will disable lazy loading to the store objects):

var data = context.tbl_product.Where(x => x.INT_TYPE == 1).Include(x=>x.Store).ToList();

var stores = from d in data
             group d by new {d.StoreID, d.StoreName} into stores
             select new Store{
                StoreId = stores.Key.StoreID,
                StoreName = stores.Key.StoreName,
                Products = stores.Select(s=>new Product{ ProductId = s.ProductID, ProductName = s.ProductName }).ToList()
             };

Then you can use your serialization on stores object:

 var json = JsonConvert.SerializeObject(stores , Formatting.Indented,
                        new JsonSerializerSettings() {
                            ReferenceLoopHandling = ReferenceLoopHandling.Ignore
                        });

Collectives™ on Stack Overflow

Create desired json array of data from generic list

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related