Vishal
Vishal

Reputation: 6368

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

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());

Upvotes: 1

Views: 222

Answers (2)

Jodrell
Jodrell

Reputation: 35696

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));
}

Upvotes: 1

Michał Krzemiński
Michał Krzemiński

Reputation: 1251

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.

Upvotes: 3

Related Questions