How do I write a LINQ query which will generate a JSON string with the following format?

Question

I have 2 related tables with schemas like so:

CREATE TABLE [dbo].[tbl_People] (
[People_Id] int NOT NULL IDENTITY(1,1) ,
[ActivityDate] date NULL,
[Colour_Id] int NOT NULL
)

and

CREATE TABLE [dbo].[tbl_Colours] (
[Colour_Id] int NOT NULL IDENTITY(1,1) ,
[Colour_Name] nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
)

I am trying to arrive at a JSON string formatted like so:

[
    {
        "Month": "June",
        "Red": 3,
        "Blue": 1,
        "Green": 7
    },
    {
        "Month": "July",
        "Red": 6,
        "Blue": 11,
        "Green": 4
    },
    {
        "Month": "August",
        "Red": 1,
        "Blue": 5,
        "Green": 9
    }

]

Currently, the best I have been able to get to with LINQ is the following:

var query = from a in context.tbl_Colours
            join t in context.tbl_People
            on a.Colour_Id equals t.People_Id
            group a by new { a.Colour_Name, t.Colour_Id, t.ActivityDate.Value.Month } into result
            select new
            {

                Month = result.Key.Month,
                Colour = result.Key.Colour_Name,
                Frequency = result.Count()
            };

string jsonData = JsonConvert.SerializeObject(query);

which returns:

[
    {
        "Month": 6,
        "Colour": "\r\nRed\r",
        "Frequency": 1
    },
    {
        "Month": 7,
        "Colour": "\r\nGreen\r",
        "Frequency": 1
    },
    {
        "Month": 8,
        "Colour": "\r\nRed\r",
        "Frequency": 1
    }

]

which is appears close to what is desired but I have not been able to tweak the code to produce anything closer to what is expected. Thanks for your help.

Answer 1

In case your colors are fixed, you could simply do:

var results = (from t in context.tbl_Peoples
            join a in context.tbl_Colours
                on t.Colour_Id equals a.Colour_Id
            group a by t.ActivityDate.Value.Month
            into result
            select new
            {
                Month = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(result.Key), 
                Blue = result.Count(r => r.Colour_Name.Trim() == "Blue"),
                Green = result.Count(r => r.Colour_Name.Trim() == "Green"),
                Red = result.Count(r => r.Colour_Name.Trim() == "Red"),
            });

string jsonData = JsonConvert.SerializeObject(results);

If your colors are not fixed and you want to build dynamic property names (e.g. Red, Green, Blue, Yellow etc.) a viable approach would be to use JSON.net objects like JArray, JObject and JProperty to generate dynamic properties and build the correct JSON output that you want.

This code should produce the JSON you expect:

var results = (from t in context.tbl_Peoples
                join a in context.tbl_Colours
                on t.Colour_Id equals a.Colour_Id
                group a by new { a.Colour_Name, t.Colour_Id, t.ActivityDate.Value.Month } into result
                select new
                {
                    result.Key.Month,
                    Colour = result.Key.Colour_Name.Trim(),
                    Frequency = result.Count()
                }).ToList();

var distinctMonths = results.Select(r => r.Month).Distinct().ToList();
var distinctColors = results.Select(r => r.Colour).Distinct().ToList();

var arr = new JArray();

foreach (var month in distinctMonths)
{
    var monthObject = new JObject
    {
        { "Month", CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(month) }
    };

    foreach (var color in distinctColors)
    {
        monthObject.Add(color, 0);
    }

    var colorsData = results.Where(d => d.Month == month).Select(a => new {a.Colour, a.Frequency});

    foreach (var color in colorsData)
    {
        monthObject[color.Colour] = color.Frequency;
    }

    arr.Add(monthObject);
}

string jsonData = arr.ToString();

The output JSON for some sample data would be:

[
  {
    "Month": "May",
    "Blue": 2,
    "Green": 1,
    "Red": 1
  },
  {
    "Month": "November",
    "Blue": 0,
    "Green": 1,
    "Red": 0
  },
  {
    "Month": "January",
    "Blue": 0,
    "Green": 0,
    "Red": 2
  }
]

Make sure to include some error handling to handle edge cases.