带有 EF 核心的封闭表 (中文 (Chinese Simplified))

封存表格预数并存储每个祖先的 - 世系关系@, @ 交易存储空间, 用于点燃 <-\fast reading>}<. 这是博客在评论系统 {-} 使用的方法。当阅读数量大大超过_,} 额外插入复杂内容时, O(1) 查询先辈_MS K7}后代\ ,}和深度}-}限量子树=.}

系列导航

什么是关闭表 {?

关闭表 @(}(也称为“"”) Joe CelkoM's SQL的树木和等级用于综合治疗的)是一张单独的表格, 预估和存储每个祖先的子孙关系 @.} □ " { msk1 在查询时是Comment@7?"的祖先通过翻过树_, 我们' 已保存了答案 k5 MS K5 这上面写着: <(1, _7), NSK8 @ @ 7), @ I(7, posK11 意谓著"" Comments MS K13 Š3, 和MSC15 都是评论<7" *# MSKO17 的祖先,在深水中Z7 自己是一个祖先

关键洞察力 : 我们交换存储空间并写出复杂内容寻找祖先或后代成为简单的索引搜索而不是循环十字路口

当您在 post @,上加载评论时, 我们可以用有效的查询来获取整个线条结构@.

审结表概念

每对连接到子孙的结点“(”和“)”的节点, 我们都会在关闭表格中保留一行“ .” 。 “crucially” “,” 我们也会储存这些结晶 深度深 @-有多少跳分开他们是#.

flowchart TD
    subgraph "Comment Tree"
        C1[Comment 1]
        C2[Comment 2]
        C3[Comment 3]
        C4[Comment 4]
    end

    C1 --> C2
    C1 --> C3
    C3 --> C4

    subgraph "Closure Table Entries"
        direction LR
        E1["(1,1,0) - 1 is ancestor of 1 at depth 0"]
        E2["(2,2,0) - 2 is ancestor of 2 at depth 0"]
        E3["(3,3,0) - 3 is ancestor of 3 at depth 0"]
        E4["(4,4,0) - 4 is ancestor of 4 at depth 0"]
        E5["(1,2,1) - 1 is ancestor of 2 at depth 1"]
        E6["(1,3,1) - 1 is ancestor of 3 at depth 1"]
        E7["(1,4,2) - 1 is ancestor of 4 at depth 2"]
        E8["(3,4,1) - 3 is ancestor of 4 at depth 1"]
    end

    style C1 stroke:#6366f1,stroke-width:2px
    style C2 stroke:#8b5cf6,stroke-width:2px
    style C3 stroke:#8b5cf6,stroke-width:2px
    style C4 stroke:#a855f7,stroke-width:2px

通知@:

每个节点都是自己深处的祖先 0 ( @ theself@-}首选項目 @). 这简化了查询@MS K3}
Comment *4 中含有3个关闭条目@: 对其本身的 < (0), n对评论{3} _(1), 和\1\ > @(2)
查找评论“#4:”的所有祖先 WHERE descendant_id = 4
寻找注释“ @1:”中的所有子孙 WHERE ancestor_id = 1

实体定义

我们需要两个实体 -: - 评论本身和关闭条目

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

    // ========== CLOSURE TABLE: Relationships stored in separate table ==========

    // We still keep ParentCommentId for convenience - it's useful for:
    // 1. Getting immediate parent without joining closure table
    // 2. Keeping the option to use EF Core navigation properties
    // 3. Human readability when debugging
    public int? ParentCommentId { get; set; }
    public Comment? ParentComment { get; set; }
    public ICollection<Comment> Children { get; set; } = new List<Comment>();

    // Navigation to the closure entries (optional - sometimes useful for eager loading)
    // AncestorClosures: entries where THIS comment is the descendant
    // DescendantClosures: entries where THIS comment is the ancestor
    public ICollection<CommentClosure> AncestorClosures { get; set; } = new List<CommentClosure>();
    public ICollection<CommentClosure> DescendantClosures { get; set; } = new List<CommentClosure>();
}

// The Closure Table entity
// Each row represents: "AncestorId is an ancestor of DescendantId at distance Depth"
public class CommentClosure
{
    // Composite primary key: (AncestorId, DescendantId)
    // This prevents duplicate entries and enables efficient lookups

    public int AncestorId { get; set; }
    public int DescendantId { get; set; }

    // How many levels apart are they?
    // 0 = same node (self-reference)
    // 1 = immediate parent/child
    // 2 = grandparent/grandchild
    // etc.
    public int Depth { get; set; }

    // Navigation properties for joining back to Comments
    public Comment Ancestor { get; set; } = null!;
    public Comment Descendant { get; set; } = null!;
}

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

        // Relationship to blog post
        builder.HasOne(c => c.Post)
            .WithMany(p => p.Comments)
            .HasForeignKey(c => c.PostId)
            .OnDelete(DeleteBehavior.Cascade);

        // Self-referencing relationship (kept for convenience, not strictly needed)
        builder.HasOne(c => c.ParentComment)
            .WithMany(c => c.Children)
            .HasForeignKey(c => c.ParentCommentId)
            .OnDelete(DeleteBehavior.Restrict);

        // Indexes for common queries
        builder.HasIndex(c => c.PostId);
        builder.HasIndex(c => c.ParentCommentId);
        builder.HasIndex(c => new { c.PostId, c.CreatedAt });
    }
}

public class CommentClosureConfiguration : IEntityTypeConfiguration<CommentClosure>
{
    public void Configure(EntityTypeBuilder<CommentClosure> builder)
    {
        // ========== COMPOSITE PRIMARY KEY ==========
        // The combination of (AncestorId, DescendantId) uniquely identifies each relationship
        // This also creates an implicit index on (AncestorId, DescendantId)
        builder.HasKey(cc => new { cc.AncestorId, cc.DescendantId });

        // ========== RELATIONSHIPS ==========

        // Each closure entry has an Ancestor - the "higher up" comment
        // One Comment can be the ancestor in MANY closure entries
        // (a root comment is ancestor to all its descendants)
        builder.HasOne(cc => cc.Ancestor)
            .WithMany(c => c.DescendantClosures)  // Comment's DescendantClosures = where it's the ancestor
            .HasForeignKey(cc => cc.AncestorId)
            .OnDelete(DeleteBehavior.Cascade);    // Delete closures when comment is deleted

        // Each closure entry has a Descendant - the "lower down" comment
        // One Comment can be the descendant in MANY closure entries
        // (a deeply nested comment has many ancestors)
        builder.HasOne(cc => cc.Descendant)
            .WithMany(c => c.AncestorClosures)    // Comment's AncestorClosures = where it's the descendant
            .HasForeignKey(cc => cc.DescendantId)
            .OnDelete(DeleteBehavior.Cascade);

        // ========== INDEXES ==========

        // Index for "get all descendants of X" queries
        // WHERE ancestor_id = @id
        builder.HasIndex(cc => cc.AncestorId);

        // Index for "get all ancestors of X" queries
        // WHERE descendant_id = @id
        builder.HasIndex(cc => cc.DescendantId);

        // Index for "get immediate children" (depth = 1 queries)
        // WHERE ancestor_id = @id AND depth = 1
        builder.HasIndex(cc => new { cc.AncestorId, cc.Depth });

        // Index for depth-limited queries
        // WHERE ancestor_id = @id AND depth <= @maxDepth
        builder.HasIndex(cc => new { cc.DescendantId, cc.Depth });
    }
}

数据库

由此产生的方案有两个表格“:”

erDiagram
    COMMENT {
        int id PK
        string content
        string author
        datetime created_at
        int post_id FK
        int parent_comment_id FK "optional - for convenience"
    }

    COMMENT_CLOSURE {
        int ancestor_id PK,FK
        int descendant_id PK,FK
        int depth "0=self, 1=parent, 2=grandparent..."
    }

    BLOG_POST {
        int id PK
        string title
        string content
    }

    BLOG_POST ||--o{ COMMENT : "has"
    COMMENT ||--o{ COMMENT : "parent-child"
    COMMENT ||--o{ COMMENT_CLOSURE : "as ancestor"
    COMMENT ||--o{ COMMENT_CLOSURE : "as descendant"

业务业务

插入新注释

这是关闭表格比对齐列表需要更多工作的地方 @ . @ 我们必须为每个祖先添加关闭条目@ MS K1}

public async Task<Comment> AddCommentAsync(
    int postId,
    int? parentId,
    string author,
    string content,
    CancellationToken ct = default)
{
    // Use a transaction to ensure atomicity
    // We need to insert the comment AND all its closure entries together
    await using var transaction = await context.Database.BeginTransactionAsync(ct);

    try
    {
        // Step 1: Create the comment
        var comment = new Comment
        {
            PostId = postId,
            ParentCommentId = parentId,
            Author = author,
            Content = content,
            CreatedAt = DateTime.UtcNow
        };

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

        // Step 2: Add the self-referencing closure entry
        // EVERY node has a row pointing to itself at depth 0
        // This simplifies queries - "get all ancestors including self" just needs depth >= 0
        var selfClosure = new CommentClosure
        {
            AncestorId = comment.Id,
            DescendantId = comment.Id,
            Depth = 0
        };
        context.Set<CommentClosure>().Add(selfClosure);

        // Step 3: If this is a reply, copy parent's closure entries with depth + 1
        if (parentId.HasValue)
        {
            // Find all ancestors of the parent
            // These become ancestors of our new comment too, but one level deeper
            var parentClosures = await context.Set<CommentClosure>()
                .Where(cc => cc.DescendantId == parentId.Value)
                .ToListAsync(ct);

            // For each ancestor of parent, add a closure to our new comment
            foreach (var parentClosure in parentClosures)
            {
                var newClosure = new CommentClosure
                {
                    AncestorId = parentClosure.AncestorId,  // Same ancestor
                    DescendantId = comment.Id,               // Points to new comment
                    Depth = parentClosure.Depth + 1          // One level deeper
                };
                context.Set<CommentClosure>().Add(newClosure);
            }
        }

        await context.SaveChangesAsync(ct);
        await transaction.CommitAsync(ct);

        logger.LogInformation("Added comment {CommentId} with {ClosureCount} closure entries",
            comment.Id, parentId.HasValue ? "multiple" : "1");

        return comment;
    }
    catch
    {
        await transaction.RollbackAsync(ct);
        throw;
    }
}

获得即时儿童

与匹配列表不同的是,我们“'”查询由父查询CommentId,}以关闭方式按深度查询 @=}{1:}

public async Task<List<Comment>> GetChildrenAsync(int commentId, CancellationToken ct = default)
{
    // Find all descendants at exactly depth 1 (immediate children)
    // The closure table makes this a simple indexed lookup
    return await context.Set<CommentClosure>()
        .AsNoTracking()
        .Where(cc => cc.AncestorId == commentId && cc.Depth == 1)
        .Select(cc => cc.Descendant)  // Navigate to the actual Comment
        .OrderBy(c => c.CreatedAt)
        .ToListAsync(ct);
}

获取所有祖先

关闭表在此光亮@ - @ a 单一索引查询}\ MS K1} no recursion * :}

public async Task<List<Comment>> GetAncestorsAsync(int commentId, CancellationToken ct = default)
{
    // All ancestors = all closure entries where this comment is the descendant
    // Exclude depth 0 (self-reference) unless you want "including self"
    return await context.Set<CommentClosure>()
        .AsNoTracking()
        .Where(cc => cc.DescendantId == commentId && cc.Depth > 0)
        .OrderByDescending(cc => cc.Depth)  // Root ancestor first
        .Select(cc => cc.Ancestor)
        .ToListAsync(ct);
}

// Version that includes the comment itself
public async Task<List<Comment>> GetAncestorsIncludingSelfAsync(int commentId, CancellationToken ct = default)
{
    return await context.Set<CommentClosure>()
        .AsNoTracking()
        .Where(cc => cc.DescendantId == commentId)  // Depth >= 0
        .OrderByDescending(cc => cc.Depth)
        .Select(cc => cc.Ancestor)
        .ToListAsync(ct);
}

获取所有后继者

平坦的“ -” 只要翻转查询“ I:” 。

public async Task<List<Comment>> GetDescendantsAsync(int commentId, CancellationToken ct = default)
{
    // All descendants = all closure entries where this comment is the ancestor
    return await context.Set<CommentClosure>()
        .AsNoTracking()
        .Where(cc => cc.AncestorId == commentId && cc.Depth > 0)
        .OrderBy(cc => cc.Depth)  // Closest descendants first
        .ThenBy(cc => cc.Descendant.CreatedAt)
        .Select(cc => cc.Descendant)
        .ToListAsync(ct);
}

获取有深度限制的后代

一个共同的要求是,由于性能或 UX 原因限制嵌套深度@:

public async Task<List<CommentWithDepth>> GetDescendantsToDepthAsync(
    int commentId,
    int maxDepth,
    CancellationToken ct = default)
{
    // The depth column makes this trivial - just add a WHERE clause
    return await context.Set<CommentClosure>()
        .AsNoTracking()
        .Where(cc => cc.AncestorId == commentId
                  && cc.Depth > 0
                  && cc.Depth <= maxDepth)
        .OrderBy(cc => cc.Depth)
        .ThenBy(cc => cc.Descendant.CreatedAt)
        .Select(cc => new CommentWithDepth
        {
            Id = cc.Descendant.Id,
            Content = cc.Descendant.Content,
            Author = cc.Descendant.Author,
            CreatedAt = cc.Descendant.CreatedAt,
            PostId = cc.Descendant.PostId,
            ParentCommentId = cc.Descendant.ParentCommentId,
            Depth = cc.Depth
        })
        .ToListAsync(ct);
}

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

Get alltire 注释树,用于邮报

用于显示带有线索结构的柱子上的所有批注@: @%

public async Task<List<CommentTreeNode>> GetCommentTreeAsync(int postId, CancellationToken ct = default)
{
    // STRATEGY:
    // 1. Get all root comments for this post (no parent)
    // 2. For each root, get its descendants from closure table
    // 3. Build tree structure in memory

    // First, get all comments for the post with their depths relative to root
    var allComments = await context.Comments
        .AsNoTracking()
        .Where(c => c.PostId == postId)
        .ToListAsync(ct);

    if (!allComments.Any())
        return new List<CommentTreeNode>();

    // Get root comment IDs (comments with no parent)
    var rootIds = allComments
        .Where(c => c.ParentCommentId == null)
        .Select(c => c.Id)
        .ToHashSet();

    // Get all closure entries to know the depths
    var closures = await context.Set<CommentClosure>()
        .AsNoTracking()
        .Where(cc => allComments.Select(c => c.Id).Contains(cc.DescendantId)
                  && rootIds.Contains(cc.AncestorId))
        .ToListAsync(ct);

    // Build lookup: comment ID -> its depth under its root ancestor
    var depthLookup = closures
        .GroupBy(cc => cc.DescendantId)
        .ToDictionary(
            g => g.Key,
            g => g.Min(cc => cc.Depth)  // Take minimum depth (from its root)
        );

    // Build the tree
    var lookup = allComments.ToLookup(c => c.ParentCommentId);
    return BuildTree(lookup, null);
}

private List<CommentTreeNode> BuildTree(ILookup<int?, Comment> lookup, int? parentId)
{
    return lookup[parentId]
        .Select(c => new CommentTreeNode
        {
            Comment = c,
            Children = BuildTree(lookup, c.Id)
        })
        .ToList();
}

删除子树

关闭表格使此直截了当的“ -” 通过关闭查找所有后代@,}(然后删除) @:

public async Task DeleteSubtreeAsync(int commentId, CancellationToken ct = default)
{
    await using var transaction = await context.Database.BeginTransactionAsync(ct);

    try
    {
        // Step 1: Find all descendants (including the comment itself)
        var descendantIds = await context.Set<CommentClosure>()
            .Where(cc => cc.AncestorId == commentId)
            .Select(cc => cc.DescendantId)
            .ToListAsync(ct);

        // Step 2: Delete closure entries for all these nodes
        // This includes both:
        // - Entries where they are descendants (their ancestor relationships)
        // - Entries where they are ancestors (their descendant relationships)
        await context.Set<CommentClosure>()
            .Where(cc => descendantIds.Contains(cc.AncestorId)
                      || descendantIds.Contains(cc.DescendantId))
            .ExecuteDeleteAsync(ct);

        // Step 3: Delete the comments themselves
        await context.Comments
            .Where(c => descendantIds.Contains(c.Id))
            .ExecuteDeleteAsync(ct);

        await transaction.CommitAsync(ct);

        logger.LogInformation("Deleted {Count} comments in subtree rooted at {CommentId}",
            descendantIds.Count, commentId);
    }
    catch
    {
        await transaction.RollbackAsync(ct);
        throw;
    }
}

移动子树

移动子树需要% :@

删除子树的旧关闭条目
基于新父创建新的关闭条目

public async Task MoveSubtreeAsync(
    int commentId,
    int newParentId,
    CancellationToken ct = default)
{
    await using var transaction = await context.Database.BeginTransactionAsync(ct);

    try
    {
        // Step 1: Get all descendants of the moving subtree (including itself)
        var subtreeIds = await context.Set<CommentClosure>()
            .Where(cc => cc.AncestorId == commentId)
            .Select(cc => cc.DescendantId)
            .ToListAsync(ct);

        // Step 2: Get ancestors of the subtree root (nodes we're disconnecting from)
        var oldAncestorIds = await context.Set<CommentClosure>()
            .Where(cc => cc.DescendantId == commentId && cc.Depth > 0)
            .Select(cc => cc.AncestorId)
            .ToListAsync(ct);

        // Step 3: Prevent cycles - can't move under own descendant
        if (subtreeIds.Contains(newParentId))
        {
            throw new InvalidOperationException("Cannot move a node under its own descendant");
        }

        // Step 4: Delete old ancestor relationships
        // Remove all closure entries that link old ancestors to subtree nodes
        await context.Set<CommentClosure>()
            .Where(cc => oldAncestorIds.Contains(cc.AncestorId)
                      && subtreeIds.Contains(cc.DescendantId))
            .ExecuteDeleteAsync(ct);

        // Step 5: Get new ancestors (ancestors of new parent + new parent itself)
        var newAncestors = await context.Set<CommentClosure>()
            .Where(cc => cc.DescendantId == newParentId)
            .ToListAsync(ct);

        // Step 6: Get current subtree structure (relative depths within subtree)
        var subtreeClosures = await context.Set<CommentClosure>()
            .Where(cc => cc.AncestorId == commentId)
            .ToListAsync(ct);

        // Step 7: Create new closure entries
        // For each new ancestor, link to each subtree node
        var newClosures = new List<CommentClosure>();

        foreach (var ancestorClosure in newAncestors)
        {
            foreach (var subtreeClosure in subtreeClosures)
            {
                // New depth = distance to new parent + 1 + depth within subtree
                newClosures.Add(new CommentClosure
                {
                    AncestorId = ancestorClosure.AncestorId,
                    DescendantId = subtreeClosure.DescendantId,
                    Depth = ancestorClosure.Depth + 1 + subtreeClosure.Depth
                });
            }
        }

        context.Set<CommentClosure>().AddRange(newClosures);

        // Step 8: Update the direct parent reference on the root of moved subtree
        var comment = await context.Comments.FindAsync(new object[] { commentId }, ct);
        if (comment != null)
        {
            comment.ParentCommentId = newParentId;
        }

        await context.SaveChangesAsync(ct);
        await transaction.CommitAsync(ct);

        logger.LogInformation("Moved subtree of {Count} nodes from comment {CommentId} to new parent {NewParentId}",
            subtreeIds.Count, commentId, newParentId);
    }
    catch
    {
        await transaction.RollbackAsync(ct);
        throw;
    }
}

查询流视觉化

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

    Note over App,DB: Getting Descendants (Simple lookup)
    App->>EF: GetDescendantsAsync(commentId)
    EF->>DB: SELECT * FROM closure WHERE ancestor_id = @id
    DB-->>EF: Results (indexed lookup, O(1))
    EF-->>App: List<Comment>

    Note over App,DB: Inserting with Closures
    App->>EF: AddCommentAsync(parentId, ...)
    EF->>DB: INSERT comment
    DB-->>EF: New ID
    EF->>DB: SELECT * FROM closure WHERE descendant_id = parent_id
    DB-->>EF: Parent's ancestors
    EF->>DB: INSERT multiple closure entries
    DB-->>EF: Done
    EF-->>App: Comment

性能特点

“	”操作“	”复杂度“MS K2”数据库查询	“笔记”
@	}插入	O(dMS K3	@ @#	d□=深度 @;一插入 <+ 关闭创建 *{	#
@	收留孩子 @O}	O(1)	NSK4 @ MS K5 简单索引到哪里@
@	_ 获得祖先的	% O(1)% _	# #1# #MSSK5# 简单索引到 <	>
@	获得子孙 {	} O(1)} *	* * 1* * * I	简单索引
@	}到最深的深度 : @MS K1 @O(1) @ @#	@ 1 @ @ @%	_加入深度过滤器 @	#
移動子樹	O(ZMS K3d)Q}MSKO5多MSSK6S{=亚树大小MSG8DMS卡9深度MSC10
删去子树 MS K1 O(s) @ @	@2}MS K6 找到{+ 批量删除□	#

储存要求

关闭表仓库 O(n MS K1 d)列,其中 nMSKO3 节点数和 d {=} 平均深度@:

在深度@5 上有一个批注\ 6 关闭条目 @(} @ itself @ MS K3 @ @ I5 祖先)
树上带有 @ 1000 @ 批注的平均深度@ MS K1 @ 有 @ MPK2} 关闭行
每个关闭行都是小@ : @ 只要三个整数 \ MS K1} bytes @ + @ subject

对于大多数博客评论系统来说,

Pros和 Cons

-=YTET -伊甸园字幕组=- 翻译: |------|------| @|O(1) 祖先MSKO2后裔问询@| OMS K4d)插入复杂度 {(深度插入□)Q}#| @(_O(}×d{) 列@)}~|# @| 不需要递归 SQL\ | 移动子树是昂贵的@| | 能在深度查询 @" 全部 N" 高效@| 更复杂的插入逻辑 *| | 与任何 SQL 数据库合作 @| 两张表格以维护 @MS K2 @|}@-#highavy 工作量=%|}*插入 @MS K3}所需的交易{|}

何时使用封闭表格

选择关闭表格时 :\

您已经读过“ -” 繁忙的工作量 @ MS K1 @ comments @ MPK2} 类别@ , org 图表@ I)
您需要在特定的深度查询 @ (" @ get孙子@ MS K1} @ I" @ limit to @ MPK3 @ level_ ")
移动子树是稀有的
您可以接受稍慢一些的插入, 快速读取
您需要灵活性来查询任何关系而不重现

当:时避免关闭表格

你常在下临诸林,
插入性能至关重要
储存空间受到严重限制
您的等级非常深 : @ (10+ @ level@ MS K1} @ I- 存储量大幅增长
你很少需要祖先的查询

世界使用量% :_ 此博客

此博客“ ' @%s 批注系统完全使用此模式@ MS K1 @ @ 选择是因为 @ : @ @

所阅读的评论远多于书面。 -}每个页面视图加载批注@,}但提交文件并不常见
深度限制很重要 - 我们的顶级注释嵌套在 @ MS K1 级别, 以防止难以读取的深线
面包屑有用 @-_ 显示@ "} 回复 [作者@]..." 需要祖先查找
注释移动非常罕见 @-_ 主持人几乎从不需要重新整理评论

收尾表's读到性能效益远大于此用案的写复杂度