我有一些分层数据 - 每个条目都有一个ID和一个（可空的）父条目ID。
我想检索给定条目下树中的所有条目。这是在SQL Server 2005数据库中完成的。我使用C# 3.5中的LINQ to SQL进行查询。
LINQ to SQL不直接支持公共表达式。我的选择是使用几个LINQ查询在代码中组装数据，或者在数据库上创建一个视图来显示CTE。
当数据量变大时，您认为哪种选项（或其他选项）会执行得更好？ Linq to SQL是否支持SQL Server 2008的HierarchyId类型？
9个回答
16
这个选项也许会很有用：
LINQ AsHierarchy() 扩展方法
http://www.scip.be/index.php?Page=ArticlesNET18
- Kyle
1
1我正在使用这个，它非常好用，特别是底部提到的更新版本。 - Chris Ridenour
8
我很惊讶没有人提到另一种数据库设计方式-当需要将多层次的层级关系展平并以高性能（不考虑存储空间）检索时，最好使用另一个实体-实体表来跟踪层次结构，而不是使用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)
);
- too
6
我会基于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...
}
}
- tvanfosson
2
1我已经尝试了这样一个函数，它似乎是可行的方法。它可以从LINQ中调用，并附加到数据上下文。另外，为什么需要视图和函数？它们似乎是重复的。 - Anthony
1该函数的模式与表不匹配。它包括级别。如果没有添加列，您可以直接将其映射到表上。我假设层次结构中的级别很重要。 - tvanfosson
3
我有两种方法来实现这个：
- 根据用户输入驱动检索每个层级的树。想象一个树形视图控件，它填充了根节点、根节点的子节点和孙子节点。只有根节点和子节点是展开的（孙子节点被折叠隐藏）。当用户展开一个子节点时，根节点的孙子节点就会显示出来（之前已经检索并隐藏），并启动检索所有曾孙节点。重复这个模式直到N层深度。这种模式非常适用于大型树（深度或宽度）因为它仅检索所需部分的树。
- 使用带有LINQ的存储过程。在服务器上使用类似通用表达式的东西来构建平面表格的结果，或者在T-SQL中构建XML树。Scott Guthrie在他的博客文章中写到了关于如何在LINQ中使用存储过程的方法。当以平面格式返回结果时，从结果中构建树，或者使用XML树。
- Jason Jackson
1
1当我陷入寻找解决方案的困境时，你的答案让我意识到我不需要拉取整个树，只需在需要时拉取子节点即可。 - ProfK
3
这个扩展方法有可能被修改为使用IQueryable。我过去曾经成功地在对象集合上使用它。它可能适用于您的场景。
public static IEnumerable<T> ByHierarchy<T>(
this IEnumerable<T> source, Func<T, bool> startWith, Func<T, T, bool> connectBy)
{
if (source == null)
throw new ArgumentNullException("source");
if (startWith == null)
throw new ArgumentNullException("startWith");
if (connectBy == null)
throw new ArgumentNullException("connectBy");
foreach (T root in source.Where(startWith))
{
yield return root;
foreach (T child in source.ByHierarchy(c => connectBy(root, c), connectBy))
{
yield return child;
}
}
}
这是我所称之为的方法：
comments.ByHierarchy(comment => comment.ParentNum == parentNum,
(parent, child) => child.ParentNum == parent.CommentNum && includeChildren)
这段代码是在这里找到的代码的改进版，修复了一些错误。
- JarrettV
2
或者你可以查看他从哪里获取的：http://weblogs.asp.net/okloeten/archive/2006/07/09/Hierarchical-Linq-Queries.aspx - TheSoftwareJedi
1我为绝地武士添加了归属。我的版本更简化和改进了。 - JarrettV
2
在MS SQL 2008中，您可以直接使用HierarchyID，但在sql2005中，您可能需要手动实现它们。在大数据集上，ParentID不够高效。此外，请查看这篇文章以获取更多关于该主题的讨论。
- Ilya Ryzhenkov
2
在那里没有提到 HierarchyID 是否可在 LINQ to SQL 中使用。 - Anthony
它不能直接在linq2sql中使用。 - Dmitri Nesteruk
1
我从Rob Conery的博客中学到了这种方法（在第6部分附近检查此代码，也可以在codeplex上找到），我喜欢使用它。这可以重新设计以支持多个“子”级别。
var categories = from c in db.Categories
select new Category
{
CategoryID = c.CategoryID,
ParentCategoryID = c.ParentCategoryID,
SubCategories = new List<Category>(
from sc in db.Categories
where sc.ParentCategoryID == c.CategoryID
select new Category {
CategoryID = sc.CategoryID,
ParentProductID = sc.ParentProductID
}
)
};
- AugustLights
2
1但是它能被重新设计以支持无限数量的子层级吗？ - Anthony
你不会在这个查询中添加十几个子类别 - 它并不特别灵活。 - Kirk Broadhurst
0
- Jacob Jojan
2
我不喜欢那种方法 - "while"循环不是很好的SQL实践，如果有更声明性的方法可以做到，应该优先考虑。现在有了：使用视图或基于表的函数，使用公共表达式（CTE），使用WITH .. UNION ALL结构，如其他答案所示。 - Anthony
请考虑在您链接的页面上插入解决方案的摘录。链接可能会在某一天失效。 - Ricardo Souza
0
从客户端获取数据的问题在于你永远无法确定需要深入多少层。这种方法将每个深度执行一次往返，并且可以联合到一个往返中从0到指定深度执行。
public IQueryable<Node> GetChildrenAtDepth(int NodeID, int depth)
{
IQueryable<Node> query = db.Nodes.Where(n => n.NodeID == NodeID);
for(int i = 0; i < depth; i++)
query = query.SelectMany(n => n.Children);
//use this if the Children association has not been defined
//query = query.SelectMany(n => db.Nodes.Where(c => c.ParentID == n.NodeID));
return query;
}
然而，它无法进行任意深度的操作。如果您确实需要任意深度，则需要在数据库中执行该操作 - 以便您可以做出正确的决策并停止。
- David B
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接