PostgreSQLM '}#ltree 延伸提供您通过数据库的 proferation 路径 MS K1\ 本地超级功率\ GST 索引@,}这样的专业操作员 @> 和 <@, 和强势模式匹配. 如果您投入 PostgreSQL 并想要最好的等级查询性能@, ltree 很难击败 @.

好消息"M:" 缩略 Npgsql EF 核心提供商确实支持将LINQ翻译为树木作业 通过 LTree 类型类型@MSMK0} 您可以使用类似的方法 IsAncestorOf(), IsDescendantOf(),和 MatchesLQuery() 直接在 LINQ 查询@. 然而\ ,EF Core 尚未支持循环的 CTEs @, 所以您需要原始 SQL 来进行操作, 要求它们建立完整的子树结果

感谢 沙伊·罗扬斯基 指出 LINQ 翻译支持@! @%

系列导航

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

什么是Itree ??

ltree 是一个 PostgreSQL 扩展名,它为等级标签路径提供本地数据类型@. 定文件路径 数据库理解此结构, 并提供优化操作员@,}函数 @,}和 GIST 索引支持.}

而不是将路径作为哑字符串处理,而是使用类似查询@, @PostgreSQL can:}

• 使用专业运算符 @ ( @%@> "_是",_的祖先 <@ {\fn黑体\fs22\bord1\shad0\3aHBE\4aH00\fscx67\fscy66\2cHFFFFFF\3cH808080}代表")的后代
• 高效等级查询应用 GST 索引
• 匹配通配符模式@ ( @% 1Top.*.Europe)
• 在路径上执行设置操作

关键洞察力: Ltree 是两个世界中最好的一棵树 @ - @ 通过数据库实现路径的简单化@ -native最优化 @.}#贸易_-off是 PostgreSQL lock-in,, 而许多通过 LIN, recionsCTE 进行树木操作的工作仍然需要原始 SQL=. *

lt树路徑格式

ltree 中的路径使用时间作为分隔符和字母数字标签@: @%

Top.Countries.Europe.UK
Top.Countries.Asia.Japan.Tokyo
Top.Products.Electronics.Computers.Laptops

规则#:

• 标签可以包含字母@, @ 位数 @ ,}和下划线
• 标签为大小写@-#敏感
• 最大标签长度为 @ 256% 字元
• 最大路径长度为 @ 65535% 标签

备注系统@,we'd使用 ID 作为标签 @: 1.3.7 意指@" @QSQQ:在批注 @3}下的评论 @7}(意为#1".)

建立树木

首个,启用扩展 {(}{requires数据库超级用户权限}):

CREATE EXTENSION IF NOT EXISTS ltree;

或经由EF核心移民

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.Sql("CREATE EXTENSION IF NOT EXISTS ltree");
}

定义实体

Npgsql 提供商包括 LTree 中键入直接映射到 PostgreSQL's ltree 并提供 LIN}-}(可传输方法) :}

using Microsoft.EntityFrameworkCore;

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

    public int PostId { get; set; }
    public BlogPost Post { get; set; } = null!;

    // ========== LTREE PATH ==========

    // The hierarchical path in ltree format
    // Format: ancestor1.ancestor2.thisNode
    // Examples:
    //   Root comment: "1"
    //   Child of 1: "1.5"
    //   Grandchild: "1.5.12"
    //
    // Using the LTree type enables LINQ translations for ltree operators
    public LTree Path { get; set; }

    // Keep ParentCommentId for convenience
    public int? ParentCommentId { get; set; }
    public Comment? ParentComment { get; set; }
    public ICollection<Comment> Children { get; set; } = new List<Comment>();

    // ========== HELPER METHODS ==========

    // Helper to get depth - LTree has NLevel property for this
    public int GetDepth() => Path.NLevel - 1;

    public IEnumerable<int> GetAncestorIds()
    {
        var pathString = Path.ToString();
        if (string.IsNullOrEmpty(pathString)) yield break;

        var parts = pathString.Split('.');
        // All except last (which is this node)
        for (int i = 0; i < parts.Length - 1; i++)
        {
            if (int.TryParse(parts[i], out var id))
                yield return id;
        }
    }
}

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

        // ========== PATH COLUMN ==========
        // The LTree type is automatically mapped to PostgreSQL's ltree type
        // by the Npgsql provider - no explicit column type needed
        builder.Property(c => c.Path)
            .IsRequired();

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

        // Self-referencing
        builder.HasOne(c => c.ParentComment)
            .WithMany(c => c.Children)
            .HasForeignKey(c => c.ParentCommentId)
            .OnDelete(DeleteBehavior.Restrict);

        // Standard indexes
        builder.HasIndex(c => c.PostId);
        builder.HasIndex(c => c.ParentCommentId);
    }
}

通过移民添加 GIST 指数@: @%

protected override void Up(MigrationBuilder migrationBuilder)
{
    // GiST index for ltree - enables efficient @>, <@, and ~ operators
    migrationBuilder.Sql(
        "CREATE INDEX ix_comments_path_gist ON comments USING GIST (path)");

    // Alternative: B-tree index for exact match and sorting
    // migrationBuilder.Sql(
    //     "CREATE INDEX ix_comments_path_btree ON comments USING BTREE (path)");
}

树木经营人

Npgsql EF 核心提供商翻译 LTree 用于这些运算符的方法@: @%

额外的 LIN-}可传输属性和方法_:

• ltree.NLevel → nlevel(ltree) -\ 路径中的标签数
• ltree.Subtree(start, end) → subltree(ltree, start, end) - 提取标签范围
• ltree.Subpath(offset) → subpath(ltree, offset) @-_% 后缀来自以下部分:
• ltree.Subpath(offset, len) → subpath(ltree, offset, len) -子字符串
• ltree.Index(subpath) → index(ltree, subpath) \ - 找到子路径位置
• LTree.LongestCommonAncestor(ltree1, ltree2) → lca(ltree1, ltree2) - 最低公有祖先

业务业务

插入新注释

public async Task<Comment> AddCommentAsync(
    int postId,
    int? parentId,
    string author,
    string content,
    CancellationToken ct = default)
{
    string path;

    if (parentId.HasValue)
    {
        // Get parent's path
        var parentPath = await context.Comments
            .Where(c => c.Id == parentId.Value)
            .Select(c => c.Path)
            .FirstOrDefaultAsync(ct);

        if (parentPath == null)
            throw new InvalidOperationException($"Parent comment {parentId} not found");

        // Create comment first to get the ID
        var comment = new Comment
        {
            PostId = postId,
            ParentCommentId = parentId,
            Author = author,
            Content = content,
            CreatedAt = DateTime.UtcNow,
            Path = string.Empty  // Temporary
        };

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

        // Build path: parentPath.newId
        // ltree uses periods as separators
        comment.Path = $"{parentPath}.{comment.Id}";
        await context.SaveChangesAsync(ct);

        logger.LogInformation("Added comment {CommentId} with ltree path {Path}",
            comment.Id, comment.Path);
        return comment;
    }
    else
    {
        // Root comment - path is just the ID
        var comment = new Comment
        {
            PostId = postId,
            ParentCommentId = null,
            Author = author,
            Content = content,
            CreatedAt = DateTime.UtcNow,
            Path = string.Empty
        };

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

        comment.Path = comment.Id.ToString();
        await context.SaveChangesAsync(ct);

        return comment;
    }
}

获得即时儿童

使用符合“ :” 的父版CommentId @ ( @ spoo@ MS K1 @ 或一树样式

public async Task<List<Comment>> GetChildrenAsync(int commentId, CancellationToken ct = default)
{
    // Option 1: Simple ParentCommentId lookup
    return await context.Comments
        .AsNoTracking()
        .Where(c => c.ParentCommentId == commentId)
        .OrderBy(c => c.CreatedAt)
        .ToListAsync(ct);
}

// Option 2: Using ltree pattern (demonstration)
public async Task<List<Comment>> GetChildrenLtreeAsync(int commentId, CancellationToken ct = default)
{
    // Get parent path first
    var parentPath = await context.Comments
        .Where(c => c.Id == commentId)
        .Select(c => c.Path)
        .FirstOrDefaultAsync(ct);

    if (parentPath == null)
        return new List<Comment>();

    // Children match pattern: parentPath.*{1}
    // The {1} means exactly one more label (immediate children only)
    var sql = @"
        SELECT * FROM comments
        WHERE path ~ ($1 || '.*{1}')::lquery
        ORDER BY created_at";

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

获取所有祖先

使用 LINQ 用 IsAncestorOf “(”翻译为 @> 运算符@): @%

public async Task<List<Comment>> GetAncestorsAsync(int commentId, CancellationToken ct = default)
{
    var targetPath = await context.Comments
        .Where(c => c.Id == commentId)
        .Select(c => c.Path)
        .FirstOrDefaultAsync(ct);

    if (targetPath == default)
        return new List<Comment>();

    // Find all nodes whose path is an ancestor of this path
    // Using IsAncestorOf which translates to @> operator
    return await context.Comments
        .AsNoTracking()
        .Where(c => c.Path.IsAncestorOf(targetPath) && c.Id != commentId)
        .OrderBy(c => c.Path.NLevel)
        .ToListAsync(ct);
}

获取所有后继者

使用 LINQ 用 IsDescendantOf “(”翻译为 <@ 运算符@): @%

public async Task<List<Comment>> GetDescendantsAsync(int commentId, CancellationToken ct = default)
{
    var parentPath = await context.Comments
        .Where(c => c.Id == commentId)
        .Select(c => c.Path)
        .FirstOrDefaultAsync(ct);

    if (parentPath == default)
        return new List<Comment>();

    // Find all nodes whose path is a descendant of this path
    // Using IsDescendantOf which translates to <@ operator
    return await context.Comments
        .AsNoTracking()
        .Where(c => c.Path.IsDescendantOf(parentPath) && c.Id != commentId)
        .OrderBy(c => c.Path)
        .ToListAsync(ct);
}

将后代控制到最大深度

使用 LINQ 用 NLevel 深度限制@: @%

public async Task<List<Comment>> GetDescendantsToDepthAsync(
    int commentId,
    int maxDepth,
    CancellationToken ct = default)
{
    var comment = await context.Comments
        .FirstOrDefaultAsync(c => c.Id == commentId, ct);

    if (comment == null)
        return new List<Comment>();

    var basePath = comment.Path;
    var baseLevel = comment.Path.NLevel;

    // NLevel property translates to nlevel() function
    // Filter descendants within maxDepth levels
    return await context.Comments
        .AsNoTracking()
        .Where(c => c.Path.IsDescendantOf(basePath) 
                 && c.Id != commentId
                 && c.Path.NLevel - baseLevel <= maxDepth)
        .OrderBy(c => c.Path)
        .ToListAsync(ct);
}

// If you need the depth value in results, you can project it:
public async Task<List<CommentWithDepth>> GetDescendantsWithDepthAsync(
    int commentId,
    int maxDepth,
    CancellationToken ct = default)
{
    var comment = await context.Comments
        .FirstOrDefaultAsync(c => c.Id == commentId, ct);

    if (comment == null)
        return new List<CommentWithDepth>();

    var basePath = comment.Path;
    var baseLevel = comment.Path.NLevel;

    return await context.Comments
        .AsNoTracking()
        .Where(c => c.Path.IsDescendantOf(basePath) 
                 && c.Id != commentId
                 && c.Path.NLevel - baseLevel <= maxDepth)
        .OrderBy(c => c.Path)
        .Select(c => new CommentWithDepth
        {
            Id = c.Id,
            Content = c.Content,
            Author = c.Author,
            CreatedAt = c.CreatedAt,
            PostId = c.PostId,
            ParentCommentId = c.ParentCommentId,
            Path = c.Path.ToString(),
            Depth = c.Path.NLevel - baseLevel
        })
        .ToListAsync(ct);
}

匹配查询的樣式

Ltree 支持强大的岩浆模式@ . @ use MatchesLQuery 在LIN:

// Find all comments at exactly depth 2 under comment 1
public async Task<List<Comment>> GetAtDepthAsync(int commentId, int depth, CancellationToken ct = default)
{
    var path = await context.Comments
        .Where(c => c.Id == commentId)
        .Select(c => c.Path)
        .FirstOrDefaultAsync(ct);

    if (path == default) return new List<Comment>();

    // Pattern: path.*{depth} matches exactly 'depth' more levels
    var pattern = $"{path}.*{{{depth}}}";
    
    return await context.Comments
        .AsNoTracking()
        .Where(c => c.Path.MatchesLQuery(pattern))
        .OrderBy(c => c.Path)
        .ToListAsync(ct);
}

// Find all paths matching a pattern like "1.*.7" (any path through 1 ending in 7)
public async Task<List<Comment>> MatchPatternAsync(string pattern, CancellationToken ct = default)
{
    // MatchesLQuery translates to the ~ operator
    return await context.Comments
        .AsNoTracking()
        .Where(c => c.Path.MatchesLQuery(pattern))
        .OrderBy(c => c.Path)
        .ToListAsync(ct);
}

删除子树

您可以使用 LINQ 选择子树@ , @ 然后删除\ :}

public async Task DeleteSubtreeAsync(int commentId, CancellationToken ct = default)
{
    var path = await context.Comments
        .Where(c => c.Id == commentId)
        .Select(c => c.Path)
        .FirstOrDefaultAsync(ct);

    if (path == default)
        throw new InvalidOperationException($"Comment {commentId} not found");

    // Delete all descendants (nodes where path is descendant of this path)
    // Note: ExecuteDeleteAsync requires EF Core 7+
    var deleted = await context.Comments
        .Where(c => c.Path.IsDescendantOf(path))
        .ExecuteDeleteAsync(ct);

    logger.LogInformation("Deleted {Count} comments with path prefix {Path}", deleted, path);
}

移动子树

ltree 提供功能来帮助路径操纵@: @

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

    try
    {
        var node = await context.Comments.FirstOrDefaultAsync(c => c.Id == commentId, ct);
        var newParent = await context.Comments.FirstOrDefaultAsync(c => c.Id == newParentId, ct);

        if (node == null || newParent == null)
            throw new InvalidOperationException("Node or parent not found");

        // Prevent cycles
        if (newParent.Path.StartsWith(node.Path))
            throw new InvalidOperationException("Cannot move under own descendant");

        var oldPath = node.Path;
        var newPath = $"{newParent.Path}.{node.Id}";

        // Update all descendants: replace old path prefix with new one
        // subpath(path, nlevel(oldPath)) gets the suffix after oldPath
        // We concatenate newPath with that suffix
        var sql = @"
            UPDATE comments
            SET path = $2::ltree || subpath(path, nlevel($1::ltree))
            WHERE path <@ $1::ltree";

        await context.Database.ExecuteSqlRawAsync(
            sql,
            new object[] { oldPath, newPath },
            ct);

        // Update parent reference
        node.ParentCommentId = newParentId;
        await context.SaveChangesAsync(ct);

        await transaction.CommitAsync(ct);

        logger.LogInformation("Moved subtree from {OldPath} to {NewPath}", oldPath, newPath);
    }
    catch
    {
        await transaction.RollbackAsync(ct);
        throw;
    }
}

ltree 函数引用

PostgreSQL 提供了许多有用的 ltree 函数@: @

查询流视觉化

sequenceDiagram
    participant App as Application
    participant EF as EF Core
    participant PG as PostgreSQL + ltree

    Note over App,PG: Getting Descendants (GiST index)
    App->>EF: GetDescendantsAsync(commentId)
    EF->>PG: SELECT path FROM comments WHERE id = @id
    PG-->>EF: Path "1.3"
    EF->>PG: SELECT * FROM comments WHERE path <@ '1.3'::ltree
    Note over PG: Uses GiST index - O(log n)
    PG-->>EF: All descendants
    EF-->>App: List<Comment>

    Note over App,PG: Pattern Match Query
    App->>EF: MatchPatternAsync("1.*.7")
    EF->>PG: SELECT * FROM comments WHERE path ~ '1.*.7'::lquery
    Note over PG: GiST index supports pattern matching
    PG-->>EF: Matching comments
    EF-->>App: List<Comment>

性能特点

“|”操作“|”复杂度“MS K2”说明“MSC3” |-----------|------------|-------| | 插入 @| O(1) | 只是设置路径字符串 @MS K4 -=YTET -伊甸园字幕组=- 翻译: “|” 获取祖先“|” O(1)” *|" @@>"接线员使用 GIST 指数 |# “| ” 获取子孙的“|” O(1) | MSSK4 ” GIST指数操作员 @| |GiST索引支持 lquery @| @| 移動子樹 | OMS K2s)}MSC4 更新後裔路径 <|# | 删去子树 | O(1) @ MS K3 @ @ I<@ 操作器以选择@|

使用 GST 索引@, @ ltree 查询非常有效 @ - @ 通常为 OQ(\log n)} 不管树的深度如何 @ MPK4 @

Pros和 Cons

-=YTET -伊甸园字幕组=- 翻译: |------|------| |Database@-native 优化 @|PostgreSQL-只有{|} 用于所有等级查询的|GIST索引 “|”和“|”相匹配的强大图案 | 内建- 路径处理函数 @| 递归性 CTE 需要原始 SQL {|} @(1)OMSK 1 祖先/ 后代询问#|}小于纯EF核心解决方案的便携性 | 常规存储 @|LINQ 通过 Npgsql}' 支持 LTree 类型@| @ @% | @%

何时使用树叶

**选择 ltree 时@ : @ ***

• 您承诺加入 PostgreSQL
• 业绩对于等级查询至关重要
• 您需要匹配 & ( findd all X.}的样式*@._Y 路径@)} @
• 你想要最好的实足路径
• 您想要对大多数层次化操作的 LINQ 支持

避免树上出现:

• 您需要数据库可移植性 @ ( @ SQL 服务器@ MS K1 @ MySQL,@ etcás.)}
• 您的团队不熟悉 PostgreSQL 扩展
• 标签需要非@ - @ alphainteric 字符
• 您需要循环 CTE 并想要避免任何原始 SQL

与定点路径的比较

Query 完整注释树查询

@-#得到一整棵有深度的评论树,

public async Task<List<CommentTreeItem>> GetPostCommentTreeAsync(
    int postId,
    int maxDepth = 5,
    CancellationToken ct = default)
{
    // Get all comments for the post with calculated depth
    // nlevel() counts the labels in the path
    var sql = @"
        WITH root_comments AS (
            -- Find root comments for this post (no dot in path = root)
            SELECT path, nlevel(path) as root_level
            FROM comments
            WHERE post_id = $1 AND path !~ '*.*'
        )
        SELECT
            c.id,
            c.content,
            c.author,
            c.created_at,
            c.post_id,
            c.parent_comment_id,
            c.path::text as path,
            nlevel(c.path) - COALESCE(
                (SELECT root_level FROM root_comments r
                 WHERE c.path <@ r.path
                 ORDER BY nlevel(r.path) DESC LIMIT 1),
                nlevel(c.path)
            ) as depth
        FROM comments c
        WHERE c.post_id = $1
          AND nlevel(c.path) <= $2 + 1  -- +1 because depth is 0-indexed
        ORDER BY c.path";  -- Perfect depth-first order!

    return await context.Database
        .SqlQueryRaw<CommentTreeItem>(sql, postId, maxDepth)
        .ToListAsync(ct);
}

public class CommentTreeItem
{
    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 string Path { get; set; } = string.Empty;
    public int Depth { get; set; }
}

系列导航

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

什么是' 下一步 @?

这个系列包括五种处理等级数据的方法, 使用 EF Core.}Part {2}*将探索如何利用原始 SQL 和 Dapper 来进一步控制等级查询@-(即将到来)_!}