Trying to use linq to search based on table with hierarchical data.

Posted by Steph on Stack Overflow See other posts from Stack Overflow or by Steph
Published on 2009-10-12T15:38:30Z Indexed on 2010/03/30 10:03 UTC
Read the original article Hit count: 475

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.

© Stack Overflow or respective owner

Related posts about linq-to-entities

Related posts about c#