parsing Hierarchical self-join table by tree level?

Question

I have a self referential category table as you see:
alt text

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?

If you are using SQL 2008, you can change the self referencing table to use the new HierarchyID data type which is perfect for hierarchies like this. Otherwise you will need to use a CTE like CesarGon said...unless you want to do the parsing in C# code. — Dismissile, Oct 29 '10 at 18:16

Mark Byers · Answer 1 · 2010-10-29T19:59:52.310

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);

score 1 · Answer 2 · edited May 23 '17 at 12:30

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.

edited May 23 '17 at 12:30

Community

1
1

answered Oct 29 '10 at 18:14

CesarGon

15,099
6
57
85

score 0 · Answer 3 · answered Oct 29 '10 at 20:32

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://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

parsing Hierarchical self-join table by tree level?

3 Answers3