Converting flat data from sql to List<Item>

Question

First of all I'm sorry if you feel this question has been raised before, but I can't seem to wrap my mind around this one, although it's rly not the hardest thing to do..

Basically I have a query result from sql which holds several rows, existing out of :

id, parentid, name, description, level

level is the depth of the item viewed as a tree structure represented as a positive integer.

now I would love to parse/convert this flat data into a "List<Item> mySqlData" where Item consist like the following class definition

public class Item
    {
        public string Id { get; set; }
        public string ParentId { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
        public string List<Item> { get; set; }
    }

can anybody give me some example code, it's probably going to be something in the lines of recursive iteration trough the list while adding the items at their place..

thanks in advance

Answer 1

Assuming you want to build the tree, and don't get the data out of order, you should be able to maintain a lookup as you go, i.e.

var idLookup = new Dictionary<int, Item>();
var roots = new List<Item>();
foreach([row]) {
    Item newRow = [read basic row];
    int? parentId = [read parentid]
    Item parent;
    if(parentId != null && idLookup.TryGetValue(parentId.Value, out parent)) {
        parent.Items.Add(newRow);
    } else {
        roots.Add(newRow);
    }
    idLookup.Add(newRow.Id, newRow);
}