Hierarchical data in Linq - options and performance
- by Anthony
I have some hierarchical data - each entry has an id and a (nullable) parent entry id.
I want to retrieve all entries in the tree under a given entry. This is in a SQL Server 2005 database. I am querying it with LINQ to SQL in C# 3.5.
LINQ to SQL does not support Common Table Expressions directly. My choices are to assemble the data in code with several LINQ queries, or to make a view on the database that surfaces a CTE.
Which option (or another option) do you think will perform better when data volumes get large?
Is SQL Server 2008's HierarchyId type supported in Linq to SQL?