LINQ query output groups and subgroups in JSON

Question

I have a table like this (Groups):

ID       Name           ParentID
1        Group 1        null
2        Group 2        null
3        SubGr 1-1      1
4        SubGr 1-2      1
5        SubGr 2-1      2
6        Group 3        null
7        SubGr 1-2-1    4
..... and so on

I want to serialize this to JSON looking like this:

[{"id":1,
  "name":"Group 1",
  "children": [
    {
     "id":3,
     "name":"SubGr 1-1",
     "children":null
    },{
     "id":4,
     "name":"SubGr 1-2",
     "children": [
       {
        "id":7,
        "name":"SubGr 1-2-1", 
        "children": null
       }
      ]
    }
   ]
  },
 {"id":2,
  "name":"Group 2",
  "children": [
    {
     "id":5,
     "name":"SubGr 2-1",
     "children":null
    }
   ]
 },
 {"id":6,
  "name": "Group 3",
  "children": null
 }
]

As you can see, you can have indefinite subgroups.

How can I make such a query in LINQ and output it in JSON like the example above?

I have no problem outputting the JSON as seperated elements, with ParentID, but I need to have the structure as mentioned above.

This is the code that I am currently working with, after trying different things around, but with no luck still (this version only gives two levels):

    public ActionResult GetGroups()
    {
        var groupobjs = db.GroupObjs.ToList();

        var items = groupobjs.Where(p => p.ParentID == null).Select(p => new
        {
            id = p.ID,
            name = p.Name,
            children = groupobjs.Where(c => c.ParentID == p.ID).Select(c => new {
                id = c.ID,
                name = c.Name
            })
        });



        return Json(items, JsonRequestBehavior.AllowGet);
    }      

Answer 1

I was working on some code similar to what @Hunter-974 recommended.

public class Group
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int? ParentId { get; set; }
    public List<Group> Subgroups { get; set; }

    public Group()
    {
        this.Subgroups = new List<Group>();
    }
}

class Program
{
    static void Main()
    {
        Group[] groups = new Group[]
        {
            new Group { Id = 1, Name = "Group 1", ParentId = null },
            new Group { Id = 2, Name = "Group 2", ParentId = null },
            new Group { Id = 3, Name = "SubGr 1-1", ParentId = 1 },
            new Group { Id = 4, Name = "SubGr 1-2", ParentId = 1 },
            new Group { Id = 5, Name = "SubGr 2-1", ParentId = 2 },
            new Group { Id = 6, Name = "Group 3", ParentId = null },
            new Group { Id = 7, Name = "SubGr 1-2-1", ParentId = 4 }
        };

        foreach (Group g in groups)
            if (g.ParentId.HasValue)
                groups.Single(group => group.Id == g.ParentId.Value).Subgroups.Add(g);

        var rootgroups = groups.Where(g => g.ParentId == null);

        JavaScriptSerializer js = new JavaScriptSerializer();
        Console.WriteLine(js.Serialize(rootgroups));
    }
}

Answer 2

I think that you should use a recursive methode instead of LINQ to do this.

1) Define a class which represent a group, with a property ID (int), a property Name (string), a property Children (List), a property Parent (int?, not serialized) and a method Serialize() (who calls Serialize for each Group in Children)

2) Define a List that will contain the "root" groups, and a List that will contain all the groups.

3) For each row of your datatable, create an object Group. Define all its properties. obviously, the list of children will be empty.

4) For each Group, if the parent's ID is not null, add it to his parent. This way, you will fill the Children list, for all the Groups.

5) For each Group, if the parent's ID is null, add the Group to the Roots list.

6) For each Group in the Roots list, call the method Serialize.

I hope this will help you. Ask me if you want more explanation or code instead of them.