相邻列表是保存等级数据的最简单和最直观的方法 — 每个行只指向其父。 这是大多数开发者首先达到的目标, 而对于经常移动的浅树来说, 通常是正确的选择。 这篇文章涵盖了执行细节, 包括用于横跨树的循环 CTE 和用于 UI 显示的嵌套结构 。

系列导航

• 第1部分:概览 - 导言和比较
• 第1.1部分:相邻清单 (本条)
• 第1.2部分:最后表格
• 第1.3部分:具体路径
• 第1.4部分:内嵌套件
• 第1.5部分:树

什么是相邻名单?

相邻列表模型是大多数开发者首先达到的方法, 并且有充分的理由 这是直观的。 每个节点都只保存与其父系的引用。 如果您画过一棵家族树, 您已经理解了这个模式 。

关键特征: 每行只知道直行的右侧父父。要找到祖父母或孙辈,您需要多次调查或重复查询。

这是最常见的有文件记载的模式,得到以下文件的有力支持: EF 核心自我参照关系.

定义实体

这个实体非常简单我们只添加一个无效的自我参考:

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 核心配置

配置设置了自我参照关系, 并关键地增加了索引, 用于我们最常处理的查询 :

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 });
    }
}

数据库

其结果只是一种自我参照的外国密钥:

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

获得即时儿童

单一索引查询 - 快速和简单:

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);
}

获取祖先( 硬部分)

这里的邻接名单显示他们的弱点。不重复查询,你不得不做多次往返旅行: 得到父母,得到父母的父母,得到祖父母...等等。

幸好,PostgreSQL的 循环 CTE 来营救:

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建造嵌套树结构

通常你实际需要的是一个树结构,而不是一个平板的列表。这里是如何高效地构建一个 :

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);
}

移动子树

这是相邻列表真正闪耀的地方 - 移动是微不足道的:

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>

性能特点

操作 复杂度 数据库回合 Trips \ 注意 \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \
|-----------|------------|---------------------|-------| 插入 O (1) 1 找孩子 O i 1 获取祖先 O(d) 1 (与CTE) d = 深度, CTE 在 DB 中工作 获取子树 O(n) 1 (与CTE) n = 子树大小 移动子树 O(1) 1 删去子树 O(n) 1 (加上 CTE) n = 子树大小

Pros 和 Cons Pros 和 Cons

|------|------| 获取祖先/后代需要再生查询 最起码的储存管理(仅增加一栏) 挪动子树是微不足道的(更新一行) EF 核心导航特性自然工作 无法轻易计算后代 而不加载它们 没有数据冗余以维持 N+1 问题的重复查询, 如果不加注意 N+1

何时使用相邻列表

选择相邻列表时 :

• 您的等级是浅的( 低于 5-6 级)
• 你常在下临诸林,
• 您想要EF Core 的导航特性自然工作
• 简单要求,即可接受循环毒性效应浓度的循环毒性可接受
• 插入性能比读读性能更重要

在下列情况下避免列出相邻名单:

• 您有深层次的等级( 10+ 级别)
• 你经常问"所有后代"或"所有祖先"
• 阅读性能至关重要
• 需要计数后代而不加载它们

系列导航

• 第1部分:概览
• 第1.1部分:相邻清单 (本条)
• 第1.2部分:最后表格
• 第1.3部分:具体路径
• 第1.4部分:内嵌套件
• 第1.5部分:树