parsing Hierarchical self-join table by tree level?!

Question

Hi friends, I have a self referential category table as you see:

I want to parse this table to find out tree level for each category. for example if root node level is 0 then CPU and Hard Drive and VGA and RAM are in level 1 and so on. how can I handle that?
I created a dictionary to put each category ID and its Level:

Dictionary<int, int> dic = new Dictionary<int, int>();

the Key is CategoryId and Value is Level. please help me how can I Fill the Dictionary?

Answer 1

I suggest you use a recursive Common Table Expression using the with keyword. Have a look at this article on MSDN and my own question here.

Answer 2

You can't do this easily in a single LINQ query. You should use recursion. Either write a recursive function in C# or use a recursive CTE in the database.

For the C# solution:

IEnumerable<KeyValuePair<int, int>> GetChildren(int id, int childLevel)
{
    foreach (var row in rows.Where(row => row.ParentID == id && row.ID != id))
    {
        yield return new KeyValuePair<int, int>(row.ID, childLevel);
        foreach (var x in GetChildren(row.ID, childLevel + 1))
        {
            yield return x;
        }
    }
}

Call as follows:

GetChildren(0, 0);

Answer 3

I agree with the previous answers; you can't do a magical query that will give you the tree level. Hierarchies like this are often better served with a nested set structure rather than a parent pointer:

http://en.wikipedia.org/wiki/Nested_set_model

This article shows you some common queries for working with nested set data:

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html