C# return a single list into nested JSON API

Question

I have a simple list which is returned from my DB to my MVC API, the sample query is

select school, class, pupil from area

With that I am adding to a list

foreach (DataRow row in table.Rows)
{
    var area = new Area();
    area.school = row.ItemArray[0].ToString();
    area.class = row.ItemArray[1].ToString();
    area.pupil = row.ItemArray[2].ToString();
    returnedlist.Add(area);
}

at the moment the API just returns the list

{
  "School": "",
  "Class": "",
  "Pupil": ""
},
{
  "School": "",
  "Class": "",
  "Pupil": ""
},

However, I would ideally like it returned nested like the following

[
    {
        School: "Name",
        Class: [
            ClassName: '',
            Pupil: [
                    PupilName: ''},
                    PupilName: ''},
                    PupilName: '']

    },
    {
        School: "Name",
        Class: [
            ClassName: '',
            Pupil: [
                    PupilName: ''},
                    PupilName: ''},
                    PupilName: '']

    },

I may have butchered that but you get the general idea.

The class for the data again is very simple

public class Area
{
    public string School { get; set; }
    public string Class { get; set; }
    public string Pupil { get; set; }
  
}

So far I have tried returning a list in a list etc, but without any luck.

Any help greatly appreciated.

can you share the area class and row?, you could change the structure of area class. — Mohammed Sajid
– Mohammed Sajid, Commented Jul 22, 2020 at 9:09
Ive added the class as it is, happy to change the structure, I have attempted but unsuccessfully — Michael Cole
– Michael Cole, Commented Jul 22, 2020 at 9:32
maybe you could change the sql query or returnedlist. group by school and by class to build new list of expected object. — Mohammed Sajid
– Mohammed Sajid, Commented Jul 22, 2020 at 10:14

Mohammed Sajid · Accepted Answer · 2020-07-22 12:37:20Z

2

To get the expected result, you could create class with this structure :

1 - Classes

public class NewArea
{
    public string School { get; set; }
    public List<Class> Classes { get; set; }
}

public class Class
{
    public string Name { get; set; }
    public List<string> Pupils { get; set; }
}

2 - First, group by school and for each grouped item, group by class:

List<NewArea> newAreas = returnedlist
    .GroupBy(x => x.School)
    .Select(x => new NewArea
    {
        School = x.Key,
        Classes = x.GroupBy(y => y.Class).Select(z => new Class
        {
            Name = z.Key,
            Pupils = z.Select(w => w.Pupil).ToList()
        }).ToList()
    }).ToList();

3 - Example for test :

List<Area> returnedlist = new List<Area>
{
    new Area{School = "s1", Class="c1",Pupil="p1"},
    new Area{School = "s1", Class="c1",Pupil="p2"},
    new Area{School = "s1", Class="c2",Pupil="p1"},
    new Area{School = "s1", Class="c2",Pupil="p2"},
    new Area{School = "s2", Class="c1",Pupil="p1"},
};

Result

[
   {
      "School":"s1",
      "Classes":[
         {
            "Name":"c1",
            "Pupils":[
               "p1",
               "p2"
            ]
         },
         {
            "Name":"c2",
            "Pupils":[
               "p1",
               "p2"
            ]
         }
      ]
   },
   {
      "School":"s2",
      "Classes":[
         {
            "Name":"c1",
            "Pupils":[
               "p1"
            ]
         }
      ]
   }
]

Namespaces :

using System.Linq;
using System.Collections.Generic;

edited Jul 22, 2020 at 12:37

answered Jul 22, 2020 at 11:01

Mohammed Sajid

4,9132 gold badges18 silver badges22 bronze badges

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

2 Comments

Michael Cole Over a year ago

Its throwing up the following error. 'NewArea' does not contain a definition for 'Class' and no accessible extension method 'Class' accepting a first argument of type 'NewArea' could be found (are you missing a using directive or an assembly reference?)

Mohammed Sajid Over a year ago

@MichaelCole i don't how you have used my code, but it's work fine for me, check this dotnetfiddle.net/Bel5i3. have you add new classes NewArea and Class to your project?.

Collectives™ on Stack Overflow

C# return a single list into nested JSON API

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related