PostgreSQL 的“ PostgreSQL” 一树延伸扩展为您提供带有数据库本地超级功率的实现路径: GST 索引, 专业操作员等 @> 和 <@如果您致力于 PostgreSQL 并想要最好的等级查询性能, ltree 是很难打败的。
好消息: 缩略 Npgsql Npgsql EF 核心提供商确实支持为树木作业翻译LINQ 通过 LTree 类型中,您可以使用如下方法: IsAncestorOf(), IsDescendantOf(), 和 MatchesLQuery() 直接在 LINQ 查询中查询。 然而, EF Core 尚未支持循环的 CTE, 所以您需要原始 SQL 来进行需要它们的行动( 如建立完整的子树的深度计算结果 ) 。
感谢 沙伊·罗扬斯基 指出LINQ翻译支持!
ltree PostgreSQL 扩展为 PostgreSQL 扩展,为等级标签路径提供本地数据类型。将其视为 定文件路径 拥有超级功率 - 数据库能理解结构,并提供最佳操作员、功能和GiST指数支持。
PostgreSQL 与其将路径当作哑字串,
@> 对于"是祖先", <@ "是"的子孙)Top.*.Europe)关键洞察力: Itree 是两个世界中最好的一环- 简单化的实现路径, 以数据库为主优化 。 权衡是 PostgreSQL 锁定, 尽管许多树的操作通过 LINQ 进行, 循环的 CTE 仍然需要原始 SQL 。
ltree 中的路径使用时间作为分隔符和字母数字标签:
Top.Countries.Europe.UK
Top.Countries.Asia.Japan.Tokyo
Top.Products.Electronics.Computers.Laptops
规则:
对于评论系统,我们会使用 ID作为标签: 1.3.7 意思是"第1条评论3下的评论7"
首先,启用扩展(要求数据库超级用户特权):
CREATE EXTENSION IF NOT EXISTS ltree;
或通过EF核心移徙:
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.Sql("CREATE EXTENSION IF NOT EXISTS ltree");
}
Npgsql 提供商包括 LTree 键入中键将直接映射到 PostgreSQL 的树上,并提供 LINQ 可转换的方法:
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;
}
}
}
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 对这些运算符使用的方法 :
运算符 = = = = = = = = = = = = = = = = = = = = = = = = SQL 例 = = = = = SQL = = = = = = = = = = SQL = = = SQL = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = SQL
|----------|---------|-------------|-------------|
| @> * 是(容器)的祖先 * * * * 是(容器)的祖先 * * * * 是(容器)的祖先 * * ltree1.IsAncestorOf(ltree2) | '1.3'::ltree @> '1.3.7'::ltree 真实的 真实的
| <@ * 是(由)的后裔 ltree1.IsDescendantOf(ltree2) | '1.3.7'::ltree <@ '1.3'::ltree 真实的 真实的
| ~ 吻合的乳房型态 ltree.MatchesLQuery(pattern) | '1.3.7'::ltree ~ '1.*'::lquery 真实的 真实的
| @ * 吻合它 * * 吻合它 * ltree.MatchesLTxtQuery(query) | '1.3.7'::ltree @ '3 & 7'::ltxtquery 真实的 真实的
| || 连接路径 (使用字符串连接) '1.3'::ltree || '7'::ltree → '1.3.7' |
| <, >, <=, >= * 标准操作员 * * 分类的标准操作员 * * 比较 * * 标准操作员 * * 分类 * * 标准操作员 * * 标准操作员 * 标准操作员 * 标准操作员 * 比较 * 标准操作员 * 标准操作员 * 分类 * * 标准操作员 * 标准操作员 * 标准操作员 *
LINQ-可转换特性和方法:
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( 简单) 或 ltree 模式匹配 :
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 支持强大的岩浆模式。 使用 MatchesLQuery 在 LINQ 中 :
// 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);
}
ltreet 提供了帮助操纵路径的功能 :
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;
}
}
PostgreSQL提供了许多有用的树林功能:
|----------|-------------|---------|
| nlevel(ltree) 标签数量 = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = nlevel('1.3.7') → 3 |
| subpath(ltree, offset) 校对:Soup subpath('1.3.7', 1) → '3.7' |
| subpath(ltree, offset, len) subpath('1.3.7', 1, 1) → '3' |
| subltree(ltree, start, end) 标签范围 subltree('1.3.7', 0, 2) → '1.3' |
| lca(ltree, ltree) 最低的常见祖先 lca('1.3.7', '1.3.9') → '1.3' |
| text2ltree(text) * 将文字转换为树枝 * text2ltree('1.3.7') |
| ltree2text(ltree) 将 ltree 转换为文本 { ltree2text('1.3.7'::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>
行动 复杂度 注意 注意 |-----------|------------|-------| 插入 O(1) 让孩子们 O(1) 和 GIST 指数匹配的方式 以GiST指数接线员 获取子孙 O 匹配 O(log n) GiST 索引的樣式支持 lquery 移动子树 O 删去子树 O(1)
以GiST指数计算,树枝查询效率极高,通常为O(log n),不论树的深度如何。
|------|------| PostgreSQL - 仅 用于所有等级查询的GIST指数 扩展依赖性 与 内建路径操纵函数 递归性 CTE 需要原始 SQL
LTree 类型选择 ltree 时 :
避免树上出现下列情况:
|--------|-------------------|-------| 索引类型 B- Tree (仅前缀) GiST (所有模式) 符合 } 的樣式 {prefix% } 只 完整通配符 接线员 字符串比较 土著 本地
LTree 类型 (CTEs 需要原始 SQL) {
成绩好与指数好 优秀与GiST
函数再加上博客文章的深度:
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; }
}
第二部分将探索如何使用原始SQL和Dapper对等级查询进行更多的控制。
© 2026 Scott Galloway — Unlicense — All content and source code on this site is free to use, copy, modify, and sell.