Create JSON arrays using LINQ

Question

I have a list of objects returning from the database that look like this when serialized using JSON.NET:

"[{"Percentage":0.78, "PartCode":"D40", "InspectionCode":"292", "Make":"TOYOTA"}]
{"Percentage":0.18, "PartCode":"6N", "InspectionCode":"292", "Make":"GM"},
{"Percentage":0.57, "PartCode":"6N", "InspectionCode":"F", "Make":"GM"},
{"Percentage":0.49, "PartCode":"D40", "InspectionCode":"F", "Make":"TOYOTA"},
{"Percentage":0.09, "PartCode":"785", "InspectionCode":"KB", "Make":"CHRYSLER"},
{"Percentage":0.09, "PartCode":"705", "InspectionCode":"KB", "Make":"FORD"},
{"Percentage":0.18, "PartCode":"D40", "InspectionCode":"KB", "Make":"TOYOTA"},
{"Percentage":0.61, "PartCode":"D40", "InspectionCode":"KB", "Make":"TOYOTA"},
{"Percentage":0.39, "PartCode":"705", "InspectionCode":"SB", "Make":"FORD"},
{"Percentage":0.31, "PartCode":"6N", "InspectionCode":"SB", "Make":"GM"},
{"Percentage":0.21, "PartCode":"AW7", "InspectionCode":"XE1", "Make":"CHRYSLER"},
{"Percentage":0.27, "PartCode":"705", "InspectionCode":"XE1", "Make":"FORD"},
{"Percentage":0.28, "PartCode":"UX", "InspectionCode":"XE1", "Make":"FORD"},
{"Percentage":0.56, "PartCode":"D40", "InspectionCode":"XE1", "Make":"TOYOTA"}]"

I need to create two JSON arrays in this format to pass to HighCharts:

var categories = [
{name: "Toyota", categories: ['D40']},
{name: "GM", categories: ['6N']},
{name: "FORD", categories: ['705', 'UX']},
{name: "CHRYSLER", categories: ['AW7','785']}];

var series = [
{name: "292", data = [0.78, 0.18]}
{name: "F", data = [0.57, 0.49]},
{name: "KB", data = [0.09, 0.09, 0.18, 0.61]},
{name: "SB", data = [0.39, 0.31]},
{name: "XE1", data = [0.21, 0.27, 0.28, 0.56]}];

So far, I am doing the nested grouping of data, since Make and PartCode are hierarchical data.

var query = from d in sortedData
            group d by d.Make into newgroup1
            from newgroup2 in
                (from e in newgroup1
                 group e by e.PartCode)
            group newgroup2 by newgroup1.Key;

I am able to see data in a hierarchical format using:

foreach (var outergroup in query)
{
    System.Diagnostics.Debug.WriteLine(outergroup.Key);
    foreach (var innergroup in outergroup)
    {
        System.Diagnostics.Debug.WriteLine(innergroup.Key);
        foreach (var innerGroupElement in innergroup)
        {
            System.Diagnostics.Debug.WriteLine("\t\t{0} {1}", innerGroupElement.InspectionCode, innerGroupElement.Percentage);
        }
    }
}

But, I am having a hard time understanding what to do further to get to the desired JSON arrays. What steps do I have to take further inorder to achieve this?

rclement · Accepted Answer · 2015-03-03 20:36:35Z

This will get you what you want. Once you group by the Make or InspectionCode, then all the items in that sub-list will contain the data you are looking for.

var categories = sortedData.GroupBy(d => d.Make)
                           .Select(g => new 
                           { 
                               name = g.Key, 
                               categories = g.Select(x => x.PartCode).ToArray() 
                           });

var series = sortedData.GroupBy(d => d.InspectionCode)
                       .Select(g => new 
                       { 
                           name = g.Key, 
                           data = g.Select(x => x.Percentage).ToArray() 
                       });

var categoriesAsJson = Newtonsoft.Json.JsonConvert.SerializeObject(categories);
var seriesAsJson = Newtonsoft.Json.JsonConvert.SerializeObject(series);

If you have the data already in memory, converting it to Json is as easy as the last two lines, using Json.NET. If you are sending this out over the wire via a WebAPI endpoint, then you can just have your endpoint return a list which would be the categories or series list objects without having converted them to JSON.

[HttpGet]
public HttpResponseMessage GetCategories()
{
    var categories = GetCategoriesUsingAboveCode();
    return Request.CreateResponse(HttpStatusCode.OK, categories);
}

galets · Accepted Answer · 2015-03-03 20:24:54Z

0

Something like that?

            var categories = sortedData
                .GroupBy(i => i.Make)
                .Select(g => new
                {
                    name = g.Key,
                    categories = sortedData
                        .Where(i2 => i2.Make == g.Key)
                        .Select(i2 => i2.InspectionCode)
                });

answered Mar 3, 2015 at 20:24

galets

18.7k19 gold badges76 silver badges103 bronze badges

Collectives™ on Stack Overflow

Create JSON arrays using LINQ

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related