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 Articles