对嵌套递归对象查询的性能问题

本文关键字：性能 问题 查询 对象 嵌套 递归 | 更新日期: 2023-09-27 18:02:02

我想知道从对象的嵌套层次结构中选择什么是最好的方法?设MyRecursiveObject类如下:

 public class MyRecursiveObject
 {
   public Int64 Id { get; set; }
   public MyRecursiveObject Parent { get; set; }
 }

在选择MyRecursiveObject实例的所有父id时，我如何达到最大性能?

对嵌套递归对象查询的性能问题

可以使用简单的循环来代替递归:

public IEnumerable<long> GetAllParentIdsOf(MyRecursiveObject obj)
{
    MyRecursiveObject child = obj;
   while (child.Parent != null)
   {
       child = child.Parent;
       yield return child.Id;
   }
}
示例:

MyRecursiveObject obj = new MyRecursiveObject {    
    Id = 1,
    Parent = new MyRecursiveObject {
        Id = 2,
        Parent = new MyRecursiveObject { Id = 3 }
    }
};
GetAllParentIdsOf(obj).ToList().ForEach(Console.WriteLine);
// 2
// 3

LinqToSql不支持任意深度的遍历树。您应该编写一个带有TSQL While循环的sql函数来生成结果，并从linqtosql调用该sql函数。

类似:

DECLARE @MyNodeID int
SET @MyNodeID = 42
  -- we're looking for the path from this ID back up to the root
  -- we don't know the length of the path.
DECLARE @MyTable TABLE
(
  int ID PRIMARY KEY,
  int ParentID,
)
DECLARE @ID int
DECLARE @ParentID int
SELECT @ID = ID, @ParentId = ParentId
FROM MyRecursiveObject
WHERE ID = @MyNodeID
WHILE @ID is not null
BEGIN
  INSERT INTO @MyTable (ID, ParentID) SELECT @ID, @ParentID
  SET @ID = null
  SELECT @ID = ID, @ParentId = ParentID
  FROM MyRecursiveObject
  WHERE ID = @ParentID
END
SELECT ID, ParentID FROM @MyTable  --results

相关文章：