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

问题描述

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

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 不直接支持公用表表达式.我的选择是使用多个 LINQ 查询在代码中组合数据，或者在显示 CTE 的数据库上创建视图.

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.

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

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?

推荐答案

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

I would set up a view and an associated table-based function based on the CTE. My reasoning for this is that, while you could implement the logic on the application side, this would involve sending the intermediate data over the wire for computation in the application. Using the DBML designer, the view translates into a Table entity. You can then associate the function with the Table entity and invoke the method created on the DataContext to derive objects of the type defined by the view. Using the table-based function allows the query engine to take your parameters into account while constructing the result set rather than applying a condition on the result set defined by the view after the fact.

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]

要使用它，你会做一些类似的事情——假设一些合理的命名方案:

To use it you would do something like -- assuming some reasonable naming scheme:

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...
    }
}

这篇关于Linq 中的分层数据 - 选项和性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！