相邻列表是储存等级数据的最简单、最直观的方法, 每行都指向父/ 母. It' 多数开发者对第一个( I,) 和经常移动的浅树所达到的目标@, itMS K5 通常是正确的选择 @. 这篇文章涵盖了执行细节 < , > , 包括替补树木和为 UI 显示建立嵌套结构的循环 CTE_.}

系列导航

• 部分=1:}概览 介绍和比较
• 部分=1.1: 相邻列表 @(_这篇文章#)}
• 缩略语
• 集成路径
• {1.4:} 内嵌套件部件
• 部份 @1.5:_ltree

什么是相邻列表? ?

相邻列表模型“(”这个术语由 乔·塞尔科)是大多数开发者第一次达到的“, ” 的方法, 而且有很好的理由 “- it's inutitive @. 每一个節点只要保留其父系的參考@. 如果你曾画过一棵家族树,你已经理解了这个模式".

关键特性: 每行只知道直行的右侧父寻找祖父母或孙子孙女 您需要多处查询或者重复询问

这是最常见的有文件记载的模式,得到下列机构的有力支持: EF Core' @ssel@-}引用关系.

定义实体

这个实体非常简单 -\ \ 我们只是添加一个无效的自定义\ MPK1 reference\ MASK2\

public class Comment
{
    public int Id { get; set; }
    public string Content { get; set; } = string.Empty;
    public string Author { get; set; } = string.Empty;
    public DateTime CreatedAt { get; set; }

    // Foreign key - which blog post this comment belongs to
    public int PostId { get; set; }
    public BlogPost Post { get; set; } = null!;

    // ========== ADJACENCY LIST: The hierarchy is defined by these three properties ==========

    // ParentCommentId is nullable because:
    // - Root-level comments (direct replies to the post) have NULL
    // - Nested replies have the ID of the comment they're replying to
    public int? ParentCommentId { get; set; }

    // Navigation property to load the parent comment when needed
    // Useful for breadcrumb trails: "Post > Comment by Alice > Comment by Bob"
    public Comment? ParentComment { get; set; }

    // Navigation property to load immediate children (NOT grandchildren!)
    // EF Core populates this when you use .Include(c => c.Children)
    // NOTE: This only gives you ONE level deep - you won't see replies to replies
    public ICollection<Comment> Children { get; set; } = new List<Comment>();
}

EF 核心配置

配置设置了自@-}引用关系,并关键添加查询的索引 we'}(通常情况下)“: ”

public class CommentConfiguration : IEntityTypeConfiguration<Comment>
{
    public void Configure(EntityTypeBuilder<Comment> builder)
    {
        builder.HasKey(c => c.Id);

        builder.Property(c => c.Content)
            .IsRequired()
            .HasMaxLength(10000);

        builder.Property(c => c.Author)
            .IsRequired()
            .HasMaxLength(200);

        // Standard relationship: comment belongs to a blog post
        // Cascade delete here is safe - deleting a post should remove all its comments
        builder.HasOne(c => c.Post)
            .WithMany(p => p.Comments)
            .HasForeignKey(c => c.PostId)
            .OnDelete(DeleteBehavior.Cascade);

        // ========== THE SELF-REFERENCING RELATIONSHIP ==========
        // This is what makes it an adjacency list - each comment points to its parent

        builder.HasOne(c => c.ParentComment)
            .WithMany(c => c.Children)           // One parent has many children
            .HasForeignKey(c => c.ParentCommentId)
            .OnDelete(DeleteBehavior.Restrict);  // WARNING: Don't use Cascade here!

        // Why Restrict and not Cascade?
        // With Cascade, deleting a parent would automatically delete ALL children,
        // grandchildren, etc. This can be:
        // 1. Unexpected behaviour for users
        // 2. A database performance issue (many deletes)
        // 3. A data integrity risk (accidental mass deletion)
        // Better to handle subtree deletion explicitly in application code

        // ========== INDEXES: Critical for performance ==========

        // Index on ParentCommentId - used when loading children
        // "SELECT * FROM comments WHERE parent_comment_id = @id"
        builder.HasIndex(c => c.ParentCommentId);

        // Index on PostId - used when loading all comments for a post
        // "SELECT * FROM comments WHERE post_id = @id"
        builder.HasIndex(c => c.PostId);

        // Composite index for the most common query:
        // "Get all comments for a post, ordered by creation date"
        builder.HasIndex(c => new { c.PostId, c.CreatedAt });
    }
}

数据库

所生成的图案是最小的 @- @ just a one self@- @ 引用外国密钥\ :}

erDiagram
    COMMENT {
        int id PK
        string content
        string author
        datetime created_at
        int post_id FK
        int parent_comment_id FK "nullable - NULL for root comments"
    }

    BLOG_POST {
        int id PK
        string title
        string content
    }

    BLOG_POST ||--o{ COMMENT : "has"
    COMMENT ||--o{ COMMENT : "has children"

业务业务

插入新注释

插入是美丽的简单@- 相邻列表模式在这里非常闪亮 @:}#

public async Task<Comment> AddCommentAsync(
    int postId,
    int? parentId,      // NULL for root comment, parent's ID for a reply
    string author,
    string content,
    CancellationToken ct = default)
{
    // Creating a comment is just setting the parent reference
    // No need to update closure tables, recalculate paths, or renumber anything
    var comment = new Comment
    {
        PostId = postId,
        ParentCommentId = parentId,  // This single reference defines the hierarchy
        Author = author,
        Content = content,
        CreatedAt = DateTime.UtcNow
    };

    context.Comments.Add(comment);
    await context.SaveChangesAsync(ct);

    logger.LogInformation("Added comment {CommentId} to post {PostId}", comment.Id, postId);
    return comment;
}

获得即时儿童

单一索引查询 @ -}快速和简单@ MS K1

public async Task<List<Comment>> GetChildrenAsync(int commentId, CancellationToken ct = default)
{
    // This is WHERE adjacency lists shine - getting children is trivial
    // Single indexed lookup: WHERE parent_comment_id = @id
    return await context.Comments
        .AsNoTracking()                              // Read-only, no tracking overhead
        .Where(c => c.ParentCommentId == commentId)  // Uses the index we defined
        .OrderBy(c => c.CreatedAt)                   // Chronological order
        .ToListAsync(ct);
}

Get anstestors {(} 硬部分=)

这里的 ' 列表显示它们的弱点 @ MS K1 @ 没有重复查询@ , @ 您的'} 需要做多次往返旅行@ I: @ 获得双亲=, 得到父/ 母\ MSKO6} 父母, 取得祖父母 ...# 等“ MSC9 ” @

感谢@, @ postgreSQL @'_%s 重复的CTEs (WISTH recursiveMS K1 来救我们

public async Task<List<Comment>> GetAncestorsAsync(int commentId, CancellationToken ct = default)
{
    // WHY RAW SQL?
    // EF Core doesn't have great support for recursive CTEs
    // We need to drop down to raw SQL for this

    // HOW THE CTE WORKS:
    // 1. Start with the target comment (WHERE id = {0})
    // 2. UNION ALL joins each result with its parent (JOIN on parent_comment_id)
    // 3. PostgreSQL keeps doing this until no more parents are found
    // 4. We exclude the starting comment (WHERE id != {0}) to get only ancestors

    var sql = @"
        WITH RECURSIVE ancestors AS (
            -- Base case: start with our target comment
            SELECT * FROM comments WHERE id = {0}

            UNION ALL

            -- Recursive case: join each result with its parent
            SELECT c.*
            FROM comments c
            INNER JOIN ancestors a ON c.id = a.parent_comment_id
        )
        -- Return all ancestors except the starting comment, in ID order (root first)
        SELECT * FROM ancestors WHERE id != {0}
        ORDER BY id";

    return await context.Comments
        .FromSqlRaw(sql, commentId)
        .AsNoTracking()
        .ToListAsync(ct);
}

获得带深度的完整子树

还需要一个循环的 CTE,}但这次我们要追踪深度

public async Task<List<CommentWithDepth>> GetDescendantsWithDepthAsync(
    int commentId,
    CancellationToken ct = default)
{
    // Similar to ancestors, but we go DOWN the tree instead of UP
    // We also track depth so we know how to indent in the UI

    var sql = @"
        WITH RECURSIVE descendants AS (
            -- Base case: start with our target comment at depth 0
            SELECT *, 0 as depth FROM comments WHERE id = {0}

            UNION ALL

            -- Recursive case: find children of each result, incrementing depth
            SELECT c.*, d.depth + 1
            FROM comments c
            INNER JOIN descendants d ON c.parent_comment_id = d.id
        )
        -- Return all descendants, ordered for display
        -- depth first, then by creation time within each level
        SELECT id, content, author, created_at, post_id, parent_comment_id, depth
        FROM descendants
        WHERE id != {0}
        ORDER BY depth, created_at";

    // Note: We need a special DTO to capture the depth column
    // EF Core's FromSqlRaw won't automatically map extra columns to entity properties
    return await context.Database
        .SqlQueryRaw<CommentWithDepth>(sql, commentId)
        .ToListAsync(ct);
}

// DTO to hold comment data plus computed depth
public class CommentWithDepth
{
    public int Id { get; set; }
    public string Content { get; set; } = string.Empty;
    public string Author { get; set; } = string.Empty;
    public DateTime CreatedAt { get; set; }
    public int PostId { get; set; }
    public int? ParentCommentId { get; set; }
    public int Depth { get; set; }  // Computed by the CTE
}

a 为UI建造嵌套树结构

通常您实际需要的是一个树状结构@, @ not a flat list @ . @ Here'_ 如何高效地构建一个#:}

public async Task<List<CommentTreeNode>> GetCommentTreeAsync(int postId, CancellationToken ct = default)
{
    // STRATEGY:
    // 1. Load ALL comments for the post in a single query (fast, one round trip)
    // 2. Build the tree structure in memory (also fast, just pointer manipulation)

    // Step 1: Get all comments for this post
    var allComments = await context.Comments
        .AsNoTracking()
        .Where(c => c.PostId == postId)
        .OrderBy(c => c.CreatedAt)  // Consistent ordering
        .ToListAsync(ct);

    // Step 2: Create a lookup by parent ID
    // This gives us O(1) access to children of any comment
    var lookup = allComments.ToLookup(c => c.ParentCommentId);

    // Step 3: Build the tree starting from root comments (ParentCommentId = null)
    return BuildTree(lookup, null);
}

private List<CommentTreeNode> BuildTree(
    ILookup<int?, Comment> lookup,
    int? parentId)
{
    // Recursively build tree nodes
    // lookup[parentId] gives us all comments whose parent is 'parentId'
    return lookup[parentId]
        .Select(c => new CommentTreeNode
        {
            Comment = c,
            Children = BuildTree(lookup, c.Id)  // Recurse to get children
        })
        .ToList();
}

public class CommentTreeNode
{
    public Comment Comment { get; set; } = null!;
    public List<CommentTreeNode> Children { get; set; } = new();

    // Convenience property for UI
    public bool HasChildren => Children.Count > 0;
}

删除子树

删除需要先找到所有后代@: @%

public async Task DeleteSubtreeAsync(int commentId, CancellationToken ct = default)
{
    // We need to find and delete all descendants, then the comment itself
    // Using a CTE to get all IDs, then bulk delete

    var sql = @"
        WITH RECURSIVE subtree AS (
            SELECT id FROM comments WHERE id = {0}
            UNION ALL
            SELECT c.id FROM comments c
            INNER JOIN subtree s ON c.parent_comment_id = s.id
        )
        DELETE FROM comments WHERE id IN (SELECT id FROM subtree)";

    var deleted = await context.Database.ExecuteSqlRawAsync(sql, new object[] { commentId }, ct);

    logger.LogInformation("Deleted {Count} comments in subtree rooted at {CommentId}",
        deleted, commentId);
}

移动子树

这里的相邻列表真正闪耀着 @ - @ 移动是微不足道@ MS K1 @

public async Task MoveSubtreeAsync(
    int commentId,
    int newParentId,
    CancellationToken ct = default)
{
    // In adjacency list, moving a subtree is just updating ONE row!
    // All descendants automatically move with their parent because
    // their ParentCommentId still points to their direct parent

    var comment = await context.Comments.FindAsync(new object[] { commentId }, ct);
    if (comment == null)
    {
        throw new InvalidOperationException($"Comment {commentId} not found");
    }

    // Prevent creating a cycle (moving a node under its own descendant)
    // This would create an infinite loop in our tree
    var ancestors = await GetAncestorsAsync(newParentId, ct);
    if (ancestors.Any(a => a.Id == commentId))
    {
        throw new InvalidOperationException("Cannot move a comment under its own descendant");
    }

    comment.ParentCommentId = newParentId;
    await context.SaveChangesAsync(ct);

    logger.LogInformation("Moved comment {CommentId} to new parent {NewParentId}",
        commentId, newParentId);
}

查询流视觉化

sequenceDiagram
    participant App as Application
    participant EF as EF Core
    participant DB as PostgreSQL

    Note over App,DB: Getting Children (Simple - O(1))
    App->>EF: GetChildrenAsync(commentId)
    EF->>DB: SELECT * FROM comments WHERE parent_id = @id
    DB-->>EF: Results (indexed lookup)
    EF-->>App: List<Comment>

    Note over App,DB: Getting Ancestors (Recursive - O(d) where d=depth)
    App->>EF: GetAncestorsAsync(commentId)
    EF->>DB: WITH RECURSIVE ancestors AS (...)
    DB->>DB: Traverse parent_id chain recursively
    DB-->>EF: All ancestors
    EF-->>App: List<Comment>

性能特点

“|”操作“|”复杂度“MS K2”数据库圆回合 |-----------|------------|---------------------|-------| @|_ 插入 @ |_ O(1)} @ I|_ * MS K4Q _ |} 只是设置了父性CommentId@|# -=YTET -伊甸园字幕组=- 翻译: 和CTE)一起工作 在DB | |+ 获取子树 MS K1+OQ(+N)+ @ |+#1+ (+CTE)+ @ @|+N=+亚树大小_|} @| 移動子樹| O(1) |(@1)| 只要更新一行#| | 刪除子樹 MS K1 O(n) NSK4 I1 □ 和CTEMS K7 * @| nMSKO9 亚树_ |

Pros和 Cons

-=YTET -伊甸园字幕组=- 翻译: |------|------| @|_simple to understand and protection #|}获得祖先{/}后代需要再生查询 @|_Minal minimal demission oversubject@(}只要加一列#)}| recursive CTE 在非常深的树上可以慢一点 移动子树是无关紧要的 “(”更新一行“)”“ @|”没有捷径去深度, @|_EF 核心导航特性自然正常工作 |}Can'}无法轻易计算后代而不加载它们 | 没有数据冗余以维持 @| 重复查询 N+1问题, 如果不小心的话@|

何时使用相邻列表

** :\ \ 时选择相邻列表**

• 您的等级比 @ 5-6_ level@ MS K2\ 浅为 @ I( @ ferwer
• 你常在下临诸林,
• 您想要 EF 核心@ ' @%s 导航属性自然工作
• 简单要求,即可接受循环毒性效应浓度的递转性TE
• 插入性能比读化要重要

** :\ \ 时避免对应列表**

• 您有深层次的等级 : @ (10+ @ level * ) @
• 您经常查询“ "}所有后代 ” @ MS K1} 或“ I" ” 。
• 阅读性能至关重要
• 需要计数后代而不加载它们

系列导航

• 部分=1:}概览
• 部分=1.1: 相邻列表 @(_这篇文章#)}
• 缩略语
• 集成路径
• {1.4:} 内嵌套件部件
• 部份 @1.5:_ltree