Linq中的分层数据 - 选项和性能

我有一些分层数据 - 每个条目都有一个id和一个(可空)父条目id.我想检索给定条目下树中的所有条目.这是在SQL Server 2005数据库中.我在C#3.5中使用LINQ to SQL查询它.

LINQ to SQL不直接支持公用表表达式.我的选择是使用几个LINQ查询在代码中汇编数据,或者在面向CTE的数据库上进行查看.

当数据量变大时,您认为哪个选项(或其他选项)会表现更好？Linq to SQL是否支持SQL Server 2008的HierarchyId类型？

此选项也可能有用:

LINQ AsHierarchy()扩展方法
http://www.scip.be/index.php?Page=ArticlesNET18

我很惊讶没有人提到过替代数据库设计 - 当层次结构需要从多个级别展平并以高性能检索(不是考虑存储空间)时,最好使用另一个实体2实体表来跟踪层次结构而不是parent_id做法.

它不仅允许单亲关系,还允许多父关系,水平指示和不同类型的关系:

CREATE TABLE Person (
  Id INTEGER,
  Name TEXT
);

CREATE TABLE PersonInPerson (
  PersonId INTEGER NOT NULL,
  InPersonId INTEGER NOT NULL,
  Level INTEGER,
  RelationKind VARCHAR(1)
);

我将基于CTE设置视图和相关的基于表的函数.我的理由是,虽然您可以在应用程序端实现逻辑,但这将涉及通过线路发送中间数据以便在应用程序中进行计算.使用DBML设计器,视图转换为Table实体.然后,您可以将该函数与Table实体相关联,并调用在DataContext上创建的方法,以派生由该视图定义的类型的对象.使用基于表的函数允许查询引擎在构造结果集时考虑您的参数,而不是在事实之后对视图定义的结果集应用条件.

CREATE TABLE [dbo].[hierarchical_table](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [parent_id] [int] NULL,
    [data] [varchar](255) NOT NULL,
 CONSTRAINT [PK_hierarchical_table] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE VIEW [dbo].[vw_recursive_view]
AS
WITH hierarchy_cte(id, parent_id, data, lvl) AS
(SELECT     id, parent_id, data, 0 AS lvl
      FROM         dbo.hierarchical_table
      WHERE     (parent_id IS NULL)
      UNION ALL
      SELECT     t1.id, t1.parent_id, t1.data, h.lvl + 1 AS lvl
      FROM         dbo.hierarchical_table AS t1 INNER JOIN
                            hierarchy_cte AS h ON t1.parent_id = h.id)
SELECT     id, parent_id, data, lvl
FROM         hierarchy_cte AS result


CREATE FUNCTION [dbo].[fn_tree_for_parent] 
(
    @parent int
)
RETURNS 
@result TABLE 
(
    id int not null,
    parent_id int,
    data varchar(255) not null,
    lvl int not null
)
AS
BEGIN
    WITH hierarchy_cte(id, parent_id, data, lvl) AS
   (SELECT     id, parent_id, data, 0 AS lvl
        FROM         dbo.hierarchical_table
        WHERE     (id = @parent OR (parent_id IS NULL AND @parent IS NULL))
        UNION ALL
        SELECT     t1.id, t1.parent_id, t1.data, h.lvl + 1 AS lvl
        FROM         dbo.hierarchical_table AS t1 INNER JOIN
            hierarchy_cte AS h ON t1.parent_id = h.id)
    INSERT INTO @result
    SELECT     id, parent_id, data, lvl
    FROM         hierarchy_cte AS result
RETURN 
END

ALTER TABLE [dbo].[hierarchical_table]  WITH CHECK ADD  CONSTRAINT [FK_hierarchical_table_hierarchical_table] FOREIGN KEY([parent_id])
REFERENCES [dbo].[hierarchical_table] ([id])

ALTER TABLE [dbo].[hierarchical_table] CHECK CONSTRAINT [FK_hierarchical_table_hierarchical_table]

要使用它,你会做一些事情 - 假设一些合理的命名方案:

using (DataContext dc = new HierarchicalDataContext())
{
    HierarchicalTableEntity h = (from e in dc.HierarchicalTableEntities
                                 select e).First();
    var query = dc.FnTreeForParent( h.ID );
    foreach (HierarchicalTableViewEntity entity in query) {
        ...process the tree node...
    }
}