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

i have table called groups. structure of table :

groupid     1------- groupname          | parentid    *------- 

now want find children , grand-children specific parent.

i have tried below code. gives me children upto 2nd level :

list<string> groups = new list<string>();   var parent = (from g in db.groups               g.groupid == 1               select g).firstordefault();  var children = (from x in db.groups                 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                          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()); 

you can try doing recursively so:

ienumerable<group> getallchildren(group parent)  {    var result = (from x in db.groups             x.parentid == parent.groupid             select x);    foreach (group child in result)    {       result.addrange(getallchildren(child));    }    return res; } 

it's example , not checking circular references.