Trying to use linq to search based on table with hierarchical data.
- by Steph
Duplicate many times over (and also these)
I have a table called Types that has the following columns.
ID
Level
Name
ParentID (The ID of the parent row)
and a table called ParentObject which has the following columns.
ID
TypeID
there are 4 different level (possibly to be expanded to more).
So if I had
ID: 1
Level: 0
Name: Level0
ParentID: null
ID: 2
Level: 1
Name: Level1
ParentID: 1
ID: 3
Level: 2
Name: Level2
ParentID: 2
ID: 4
Level: 3
Name: Level3
ParentID: 3
ID: 5
Level: 4
Name: Level4
ParentID: 4
In the ParentObject table I store the tree by just storing the lowest level ID. So if the ID i have is 4, I know that the tree actually goes Level0 - Level1 - Level2 - Level3
Basically, I need to be able to search all objects for a certain Type, or Type Level 2 for example in a linq statement.
Say that the TypeID stored in the ParentObject table is 4, so Level3.
But I actually want to search for all ParentObjects where the Level 2 type has an ID of 3.
What would be the best way to do this since the id stored could be a level1 or a level2, etc.?
Preferably in one linq statement if possible.
