This is a viewer only at the moment see the article on how this works.
To update the preview hit Ctrl-Alt-R (or ⌘-Alt-R on Mac) or Enter to refresh. The Save icon lets you save the markdown file to disk
This is a preview from the server running through my markdig pipeline
Ієрархії даних - частина 1.5: PostgreSQL ltree з ядром EF
Saturday, 06 December 2025
PostgreSQL's ltree extension дасть вам матеріалізовані шляхи з базою даних -нативні супермені МSK2 GiST-indeksи M SK3 спеціалізовані оператори, такі як @> і <@, і потужне співпадіння шаблонів . Якщо ви МSK2 привернулися до PostgreSQL і хочете найкращого результату в питаннях по ієрархії МSK3 Ltree важко перемогти
Хороші новини: І Допоміжник Npgsql EF Core підтримує переклади LINQ для операцій ltree через LTree typ. Ви можете використовувати такі методи, як IsAncestorOf(), IsDescendantOf(), і MatchesLQuery() безпосередньо в LINQ queries. Однак, EF Core ще не поддерживает рекурсивні CTEs , тож вам МSK3 буде потрібен сировий SQL для операцій, які їх потребують МSK4 так само, як будування повних результатів піддербів з розрахункованою глибиною M SK5
Дякую Шай Роганський для вказування на підтримку перекладу LINQ!
Навігація серії
- Частина 1: Обзор - Вprowadzenie та порівняння
- Частина 1.1: Допомічник
- Частина 1.2: Закривальний стіл
- Частина 1.3: Матеріалізований шлях
- Частина 1.4: Насаджені набіри
- Частина 1.5: ltree (таков artykułМSK1
Що таке ltree?
ltree - це розширення PostgreSQL, що надає нативний тип даних для ієрархічних шляхів ярликів. Подумайте про це як Матеріалізований шлях з супермощами - база даних розуміє структуру і надає оптимізовані оператори , функції МSK2 та підтримку індексу GiST
Замість того, щоб сприймати шлях як дурну стрічку і використовувати LIKE queries, PostgreSQL can:
- Використовуйте спеціалізовані оператори (
@>для " - попередник МSK1<@для " є нащадком МSK1 - Застосувати індекси GiST для ефективного аналізу ієрархічних запитів
- Порівняйте шаблони з wildcards (
Top.*.Europe) - Виконувати встановлені операції на шляхах
Ключеві ідеї: ltree - це найкращий з обох світів - простота матеріалізованих шляхів з базою даних - нативна оптимізація МSK2 Торгівля M SK3 від PostgreSQL замок - в , і, в той час як багато операцій ltree працюють за допомогою LINQ MSC6 рекурсивні CTEs все ще потребують сирого SQL
Формат маршруту в дереві
Шляхи в дереві Ltree використовують періоди як відокремлення та алфанійні літери:
Top.Countries.Europe.UK
Top.Countries.Asia.Japan.Tokyo
Top.Products.Electronics.Computers.Laptops
Правила:
- На ярликах можуть бути літери, цифриM SK1 та підмітки
- Labels are case-sensitive
- Максимальна довжина літери - символи 256
- Максимальна довжина маршруту - 65535 ярлики
Для систем коментації ми використовуємо ID як літери 1.3.7 значення "коммент МSK1 внизу, унизу, внизу
Налагодження дерева
Перше, ввімкнути розширення (забаровує привілеї суперпользователя бази данихM SK2
CREATE EXTENSION IF NOT EXISTS ltree;
Або через EF Core migration:
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.Sql("CREATE EXTENSION IF NOT EXISTS ltree");
}
Визначення об 'єкту
Додавець Npgsql включає в себе LTree тип, який maps directly to PostgreSQL's ltree and provides LINQM SK1translatable methods:
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)");
}
Оператори ltree
ltree надає потужні оператори. Пропонувач EF Core Npgsql перекладає LTree методи для цих операторів:
| Оператор ♫ ♫ | ♫ Мова ♫ | ||
|---|---|---|---|
@> |
- попередник МSK1, що містить МSK2 | ltree1.IsAncestorOf(ltree2) |
'1.3'::ltree @> '1.3.7'::ltree → правда МSK1 |
<@ |
є потомком МSK1, що складається з МSK2 | ltree1.IsDescendantOf(ltree2) |
'1.3.7'::ltree <@ '1.3'::ltree → правда МSK1 |
~ |
Схоже на модель тесту МSK1 ltree.MatchesLQuery(pattern) |
'1.3.7'::ltree ~ '1.*'::lquery → правда МSK1 |
|
@ |
Схоже на ltxtquery | ltree.MatchesLTxtQuery(query) |
'1.3.7'::ltree @ '3 & 7'::ltxtquery → правда МSK1 |
|| |
Конкатеновані шляхи '1.3'::ltree || '7'::ltree → '1.3.7' |
||
<, >, <=, >= |
Порівняння МSK1 Стандартні оператори МSK2 Для сортування |
Dodatkowі лінкQ-переводні властивості та методиM SK1
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)- найменший спільний предок
Операції
Вsert a New Comment
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 (simple) або ltree pattern matchingM SK2
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);
}
Візьміть усіх пращурів
Використовуючи Linux з 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);
}
Візьміть усіх дітей
Використовуючи Linux з 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;
}
}
Довідка про функції ltree
PostgreSQL надає багато корисних funkcji ltree:
| Функція МSK1 Описание МSK2 Приклад | ||
|---|---|---|
nlevel(ltree) |
Кількість ярликів МSK1 nlevel('1.3.7') → 3 |
|
subpath(ltree, offset) |
Наставка з відхиленням | subpath('1.3.7', 1) → '3.7' |
subpath(ltree, offset, len) |
Субстром МSK1 subpath('1.3.7', 1, 1) → '3' |
|
subltree(ltree, start, end) |
Диапазон ярликів МSK1 subltree('1.3.7', 0, 2) → '1.3' |
|
lca(ltree, ltree) |
Найнижчий спільний предок МSK1 lca('1.3.7', '1.3.9') → '1.3' |
|
text2ltree(text) |
Перетворити текст на дерево | text2ltree('1.3.7') |
ltree2text(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>
Характеристики ефективності
| Операція | Сложність МSK2 Записки МSK3 |
|---|---|
| Вставляйте | |
| Додайте дітей | OM SK2 МSK3 Порівняйте візерунок з індексом GiST МSK4 |
| Згадайте предків | OM SK2 МSK3 @> оператора з індексом GiST МSK5 |
| Зробіть потомки МSK1 OM SK2 | <@ оператор з індексом GiST МSK5 |
| Схожість шаблону | O(log nM SK3 МSK4 Індекс GiST підтримує lquery МSK5 |
| Переміщайте піддерево | OM SK2s) МSK4 Обновляйте послідовні шляхи МSK5 |
| Використовуйте оператор для вибору |
Із індексами GiST, ltree queries є надзвичайно ефективними - tipично OM SK2log nMSC3 незалежно від глибини дереваMスク4
Привілеї та мінуси
| Преподобання МSK1 Загнічення | |
|---|---|
| база данихM SK1 нативна оптимізація | PostgreSQL- тільки МSK4 |
| Індекс GiST для всіх ієрархічних запитів | |
| Сильне співпадіння шаблонів | Etiketи обмежені алфаномічними МSK2 |
| ВбудованіM SK1вбудовані функції маніпуляції маршрутом МSK2 Рекурсивні CTE потребують сирого SQL | |
| OM SK1 пращур / питання спадщиків МSK3 менш портативні, ніж чисті EF Коренные рішення МSK4 | |
| Компактна накопичувача | |
поддержка LINQ за допомогою Npgsql's LTree тип |
МSK1 |
Коли використовувати дерево
Виберите дерево, коли:
- You're committed to PostgreSQL
- Вдачі є вирішальними для ієрархічних запитів
- Вам потрібна послідовність (найти все XM SK1*МSK0Y шляхи)
- Ви хочете найкращих матеріалізованих шляхів
- Ви хочете підтримку LINQ для більшості ієрархічних операцій
Уникніть дерева, коли:
- Вам потрібна портативність бази даних (SQL Server, MySQLM SK2 і т.д.
- Ваша команда не знайома з розширеннями PostgreSQL
- Labels need non-alphanumeric characters
- Вам потрібні рекурсивні CTE і ви хочете уникнути будь-якого сирого SQL
Порівняння з матеріалізованим шляхом
| аспект МSK1 матеріалізований шлях МSK2 дерево | |
|---|---|
| Індексовий тип МSK1 Б МSK2 дерево ( тільки префикс MSК4 МСК5 GiST МСК6 всі шаблони МСК7 \ \ | |
| Схоже на шаблон МSK1 Який-то МSK2предп 'ятникM SK3 тільки | Полні wildcards M |
| МSK0 Оператори | Сравнення струн МSK2 Нативні @>, |
| Портативність МSK1 Будь-яка база даних | Тільки для PostgreSQL |
| EF Core Support | Полний LINQ LTree тип (CTEs потребують сирого SQLM SK1 |
| Досконалість МSK1 Хороша з індексом МSK2 Чудова з GiST | |
| Функції МSK1 Ніхто МSK2ручне обчислення ) M | Богата бібліотека funkcji МСК5 |
До прикладу: Полне питання Tree
Зіставляючи все разом, - отримуєте цілу дерево спостережень з глибиною для блогового посту
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.2: Закривальний стіл
- Частина 1.3: Матеріалізований шлях
- Частина 1.4: Насаджені набіри
- Частина 1.5: ltree (таков artykułМSK1
Що' СледующаяM SK1
Ця серія охопила п ’ ять підходів до ієрархічних даних, використовуючи EF Core. Część 2 досліджуватиме використання сирого SQL та Dapper для ще більшої контролю над ієрarchijskimи запитами МSK2 незабаром