Розширення ltree PostgreSQL надає вам матеріальні шляхи з інструментами для роботи з базами даних: індекси GiST, спеціалізовані оператори, зокрема @> і <@, і потужний збіг з шаблоном. Якщо ви віддаєте файл PostgreSQL і бажаєте, щоб програма виконувала найкращі дії з ієрархійних запитів, ltree важко перевершити.
Хороші новини: The Npgsql EF Основний постачальник підтримує переклади LINQ для операцій з ltree через LTree type. Ви можете використовувати такі методи, як IsAncestorOf(), IsDescendantOf(), і MatchesLQuery() безпосередньо у запитах до LINQ. Але EF Core ще не підтримує рекурсивні CTES, отже, вам потрібні будуть сирі SQL для операцій, які потребують їх (наприклад, створення повного піддерева результатів з обчисленими глибинами).
Дякую. Shay Rojansky замітка підтримки перекладу LINQ!
ltree є суфіксом PostgreSQL, який надає природній тип даних для шляхів ієрархічної мітки. Думайте про це як Матеріальний шлях з супердержавами - база даних розуміє структуру і надає оптимальну підтримку операторів, функцій та індексів GiST.
Замість того, щоб вважати шлях дурнем і використовувати запити, PostgreSQL може:
@> бо предок <@ for " є нащадком ")Top.*.Europe)Прозорість ключа: ltree є найкращим з обох світів - простотою матеріалізованих шляхів з оптимізацією бази даних. Випромінювання є блокуванням PostgreSQL, а багато операцій з ltree працюють за допомогою LINQ, рекурсивні CTE все ж потребують необробленого SQL.
Шляхи у ltree використовують періоди як роздільники і мітки альфа- цифрові:
Top.Countries.Europe.UK
Top.Countries.Asia.Japan.Tokyo
Top.Products.Electronics.Computers.Laptops
Правила:
Для систем коментарів ми б використовували ІД як мітки: 1.3.7 означає " comment 7 під коментарем 3 під коментарем 1 ."
Спочатку увімкніть суфікс (дозвольте права доступу суперкористувача бази даних):
CREATE EXTENSION IF NOT EXISTS ltree;
Або за допомогою EF- міграцій ядра:
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.Sql("CREATE EXTENSION IF NOT EXISTS ltree");
}
До складу постачальника Npgsql включено LTree введіть, що відповідає 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)");
}
ltree надає у розпорядження потужних операторів. Перекладається провайдером EF Npgsql LTree методи для цих операторів:
♪LINQ} + SQLOF}
|----------|---------|-------------|-------------|
| @> ♪ Є передньою частиною ['s] ♪ ltree1.IsAncestorOf(ltree2) | '1.3'::ltree @> '1.3.7'::ltree → істина
| <@ Передня частина (вміщена на) ♪ ltree1.IsDescendantOf(ltree2) | '1.3.7'::ltree <@ '1.3'::ltree → істина
| ~ Територія lqueryStencils ltree.MatchesLQuery(pattern) | '1.3.7'::ltree ~ '1.*'::lquery → істина
| @ ltxtquery ♪ 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;
}
}
Використання ParentCommentId (простих) або відповідності шаблонів 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 підтримує потужні візерунки lquery. Використовуйте 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);
}
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;
}
}
У PostgreSQL передбачено багато корисних функцій ltree:
♪
|----------|-------------|---------|
| nlevel(ltree) ♪Mone of tag ♪ nlevel('1.3.7') → 3 |
| subpath(ltree, offset) Дівчино. subpath('1.3.7', 1) → '3.7' |
| subpath(ltree, offset, len) ♪Sepring ♪ subpath('1.3.7', 1, 1) → '3' |
| subltree(ltree, start, end) Д. д. д. д. д. д. д. д. д. д. д. д. subltree('1.3.7', 0, 2) → '1.3' |
| lca(ltree, ltree) ♪thest computer' spellow' spelling moon' s mouse lca('1.3.7', '1.3.9') → '1.3' |
| text2ltree(text) ♪ Перетворює текст на ltree' 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>
♪ |-----------|------------|-------| Д. д. д. д. д. д. д. д. д. д. д. д. д. д. д. д. д. д. д. д. д. д. д. д. д. д. д. проп. ♪ Getting childs O'00} +} відповідає індексу GiST} Get precreates operатор з індексом GiST} Get stars} {\ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ >\ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ >\ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ >\ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ > log n)} Індикатор "Чінка" підтримує lquery} } Перемістити підібзад) Передавання прямих дробів ♪ Вилучити підкатегорію [окрім] <\ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ >\ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ >
За допомогою індексів GiST дуже ефективні запити на дерева, як правило, " O'log n ," незалежно від глибини дерева.
Збоченець
|------|------|
♪ Data- native оптимізація} PostgreSQL- only}
Індекс GiST для всіх ієрархій *
шаблон =} Мітки обмежено альфа-number}
Дзвінок, що використовує траєкторію
Д-р Харріс:
Дівчино
Підтримка LINQ з підтримкою Npgsql LTree c type сягментиunit synonyms for matching user input
Виберіть ltree, якщо:
Уникайте ltree, якщо:
♪
|--------|-------------------|-------|
♪ Topd Type } B- tree (лише prefix)} ЧЧЧ (всі шаблони) ♪
♪ ♪
*
♪ Port} Щось з базою даних лише PostgreSQL ♪
Д-р Цукер: "Тео-е-е-а-а-а-а-а-а" означає "підтримка." LTree type (CTEs потребують сирого SQL)}
Д_ д. д. д. д. д. д. д. д. д. д. д. д. д.
None (manual parsing) +} {\ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ >
Об' єднати все це - отримати ціле дерево коментарів з глибиною для допису блогу:
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; }
}
У цій серії статей було описано п' ять підходів до ієрархічних даних за допомогою EF Core. Частина 2 вивчатиме використання сирих SQL і Dapper для ще більшого контролю над запитами на ієрархію - незабаром!
© 2026 Scott Galloway — Unlicense — All content and source code on this site is free to use, copy, modify, and sell.