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

Question

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.

Answer 1

In SQL (MS SQL Server 2005+) you could use a Common Table Expression to implement recursion. LINQ doesn't support CTE. A couple of work arounds would be one to create the stored procedure and map it as a method to your Data Context.

Another solution would be to write the SQL directly in your C# code and have LINQ execute it. See this.

Or you could write a bit of C# code that selects from your data until there isn't any more parent ID's. A rough example would be...

public partial class Form1 : Form
{
    public Form1()
    {
         IList<Data> data = new List<Data>();

        data.Add(new Data() { ID = 1, ParentID = 0, SomeData = "Example" });
        data.Add(new Data() { ID = 2, ParentID = 1, SomeData = "Another Example" });
        data.Add(new Data() { ID = 3, ParentID = 2, SomeData = "Example three" });
        data.Add(new Data() { ID = 4, ParentID = 3, SomeData = "Last example" });

        IList<Data> results = new List<Data>();

        Int32 parentID = 2;

        while (parentID > -1)
        {
            results.Add(
                data.Where(x => x.ParentID == parentID).Single()
            );

            parentID--;
        }
    }
}

public class Data
{
    public Int32 ID { get; set; }
    public Int32 ParentID { get; set; }
    public String SomeData { get; set; }
}

Hope this helps!