Get children and grand-children at all levels for specified parent

Question

I have a table called Groups. The structure of that table is :

GroupID     1-------
GroupName          |
ParentID    *-------

Now I want to find all the children and grand-children for a specific parent.

I have tried the below code. But it gives me children upto 2nd level only :

List<string> groups = new List<string>();


var parent = (from g in db.Groups
              where g.GroupID == 1
              select g).FirstOrDefault();

var Children = (from x in db.Groups
                where x.ParentID == parent.GroupID
                select x);

groups.AddRange(Children.Select(x => x.GroupName));

foreach (Group children in Children)
{
    var grandChildren = (from x in db.Groups
                         where x.ParentID == children.GroupID
                         select x.GroupName);
        groups.AddRange(grandChildren);
}  


StringBuilder builder = new StringBuilder();

foreach (string str in groups)
{
    builder.Append(str.ToString()).AppendLine();
}

MessageBox.Show(builder.ToString());

Grand-children **are** the 2nd level. Do you need all levels of them? In that case, recusion might do. — AgentFire, Mar 20 '14 at 09:41
I don't know how to call it recursively. Can you provide a simple demo? — Vishal, Mar 20 '14 at 09:42
I'd suggest doing it in the database http://stackoverflow.com/questions/1686340/sql-server-how-to-get-all-child-records-given-a-parent-id-in-a-self-referencing — Jodrell, Mar 20 '14 at 10:08

Michał Krzemiński · Accepted Answer · 2014-03-20T10:04:04.193

3

You can try doing it recursively like so:

IEnumerable<Group> GetAllChildren(Group parent) 
{
   var result = (from x in db.Groups
            where x.ParentID == parent.GroupID
            select x);
   foreach (Group child in result)
   {
      result.AddRange(GetAllChildren(child));
   }
   return res;
}

It's only an example and is not checking for circular references.

edited Mar 20 '14 at 10:04

answered Mar 20 '14 at 09:58

Michał Krzemiński

1,261
8
10

score 1 · Answer 2 · edited May 23 '17 at 12:05

If you don't want to do it in the database, which would likely be more efficient, you could do something like this.

Note, I make a dictionary to prevent hitting the database for every generation. If you are only getting a small subset of all groups or if the number of generations is small this is probably the wrong approach.

If this process is repeated and the group dictionary can be cached, this will be efficient and prevent many trips to the database. This function doesn't allocate extra memory to build the result set, it just lazily walks the tree.

IDictionary<int, Group> groups;
using(var db = new Context())
{
    groups = db.Groups.ToDictionary(g => g.GroupID, g => g)
}

var all = FlattenGroups(groups, new[] { groups[1] });

private IEnumerable<Group> FlattenGroups(
        IDictionary<int, Group> groups,
        IEnumerable<group> topSet)
{
    return topSet.SelectMany(group =>
        FlattenGroups(
                groups,
                groups[group.ParentID])
        .Concat(topSet));
}

Get children and grand-children at all levels for specified parent

2 Answers2