score:2

Accepted answer

you could use a recursive method, starting with items without a parent id:

var categories = new list<category>();

getcategories(ref categories);

void getcategories(ref list<category> categories, int? parentid = null)
{
    string query = string.empty; 

    if (parentid.hasvalue)
    {
        query = "select * from categories where parentid=@parentid";         
    }
    else
    {
        query = "select * from categories where parentid is null";
    }

    using (var conn = new sqlconnection(connstr))
    {
        using (var command = new sqlcommand(query, conn))
        {
            conn.open();

            if (parentid.hasvalue)
            {
                command.parameters.addwithvalue("@parentid", parentid.value);
            }

            using (var reader = command.executereader())
            {
                while (reader.read())
                {
                   var c = new category();
                   c.text = reader["text"];
                   //etc..
                   categories.add(c);
                   c.children = new list<category>();
                   getcategories(ref c.children, c.id);
                }
            }
        }
   }
}

score:0

try this

    var allcategories = new list<category>();

after fetching data...

    var children = allcategories.tolookup(cat => cat.parentid);
    foreach (var category in allcategories)
    {
        category.children = children[category.parentid].tolist();
    }

score:0

flat list to hierarchy

public class treeobject
  {
     public int id { get; set; }
     public int parentid { get; set; }
     public string name { get; set; }
     public list<treeobject> children { get; set; } = new list<treeobject>();
  }

send flat list to this method and get hierarchy (parent/child) object list

public list<treeobject> flattohierarchy(list<treeobject> list)
        {
           
            var lookup = new dictionary<int, treeobject>();
            var nested = new list<treeobject>();

            foreach (treeobject item in list)
            {
                if (lookup.containskey(item.parentid))
                {
                    // add to the parent's child list 
                    lookup[item.parentid].children.add(item);
                }
                else
                {
                    // no parent added yet (or this is the first time)
                    nested.add(item);
                }
                lookup.add(item.id, item);
            }

            return nested;
        }

Related Query

More Query from same tag