PostgreSQL's ltree extensie geeft u gematerialiseerde paden met database-native superpowers: GiST indexen, gespecialiseerde operators zoals @> en <@, en krachtige patroon matching. Als je bent toegewijd aan PostgreSQL en wilt de beste hiërarchie query prestaties, ltree is moeilijk te verslaan.
Goed nieuws: De Npgsql EF Core provider ondersteunt LINQ vertalingen voor ltree operaties via de LTree type. U kunt methoden gebruiken zoals IsAncestorOf(), IsDescendantOf(), en MatchesLQuery() direct in LINQ queries. Echter, EF Core ondersteunt nog geen recursieve CTE's, dus je hebt ruwe SQL nodig voor operaties die ze nodig hebben (zoals het bouwen van volledige subtree resultaten met berekende dieptes).
Dankzij Shay Rojansky voor het aanwijzen van de LINQ vertaalondersteuning!
ltree is een PostgreSQL extensie die een native data type voor hiërarchische label paden biedt. Zie het als Gematerialiseerd pad met superkrachten - de database begrijpt de structuur en biedt geoptimaliseerde operators, functies en GiST index ondersteuning.
In plaats van het pad als een domme string te behandelen en LIKE queries te gebruiken, kan PostgreSQL:
@> in plaats van "is voorouder van," <@ in plaats van "is afstammeling van")Top.*.Europe)Belangrijkste inzicht: ltree is het beste van beide werelden - de eenvoud van gematerialiseerde paden met database-native optimalisatie. De trade-off is PostgreSQL lock-in, en terwijl veel ltree operaties werken via LINQ, moeten recursieve CTE's nog steeds rauwe SQL.
Paden in ltree gebruiken perioden als scheidingstekens en alfanumerieke labels:
Top.Countries.Europe.UK
Top.Countries.Asia.Japan.Tokyo
Top.Products.Electronics.Computers.Laptops
Regels:
Voor commentaarsystemen gebruiken we ID's als labels: 1.3.7 "commentaar 7 onder commentaar 3 onder commentaar 1."
Schakel eerst de extensie in (vereist database superuser privileges):
CREATE EXTENSION IF NOT EXISTS ltree;
Of via EF Core migratie:
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.Sql("CREATE EXTENSION IF NOT EXISTS ltree");
}
De Npgsql provider omvat een LTree typ dat direct naar PostgreSQL's ltree en biedt LINQ-vertaalbare methoden:
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);
}
}
Voeg de GiST-index toe via migratie:
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 biedt krachtige operators. De Npgsql EF Core provider vertaalt LTree methoden voor deze exploitanten:
Bedoeling LINQ-methode SQL-voorbeeld
|----------|---------|-------------|-------------|
| @> Is voorouder van (bevat) ltree1.IsAncestorOf(ltree2) | '1.3'::ltree @> '1.3.7'::ltree → waar
| <@ Is afstammeling van (bevat door) ltree1.IsDescendantOf(ltree2) | '1.3.7'::ltree <@ '1.3'::ltree → waar
| ~ Matches lquery patroon ltree.MatchesLQuery(pattern) | '1.3.7'::ltree ~ '1.*'::lquery → waar
| @ Komt overeen met ltxtquery ltree.MatchesLTxtQuery(query) | '1.3.7'::ltree @ '3 & 7'::ltxtquery → waar
| || Paadjes samenvoegen (string concatenation gebruiken) '1.3'::ltree || '7'::ltree → '1.3.7' |
| <, >, <=, >= Vergelijking Standaardoperatoren voor het sorteren
Aanvullende LINQ-vertaalbare eigenschappen en methoden:
ltree.NLevel → nlevel(ltree) - aantal labels in padltree.Subtree(start, end) → subltree(ltree, start, end) - uittrekbereik van etikettenltree.Subpath(offset) → subpath(ltree, offset) - achtervoegsel van offsetltree.Subpath(offset, len) → subpath(ltree, offset, len) - substringltree.Index(subpath) → index(ltree, subpath) - subpath positie vindenLTree.LongestCommonAncestor(ltree1, ltree2) → lca(ltree1, ltree2) - laagste gemeenschappelijke voorouderpublic 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;
}
}
Met behulp van ParentCommentId (simpel) of ltree patroon matching:
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);
}
Gebruik van LINQ met de IsAncestorOf methode (vertaalt naar @> exploitant):
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);
}
Gebruik van LINQ met de IsDescendantOf methode (vertaalt naar <@ exploitant):
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);
}
Gebruik van LINQ met NLevel voor dieptebegrenzing:
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 ondersteunt krachtige lquery patronen. MatchesLQuery in 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);
}
U kunt LINQ gebruiken om de subboom te selecteren en vervolgens verwijderen:
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 biedt functies om te helpen met padmanipulatie:
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 biedt vele nuttige ltree functies:
Functie Beschrijving Voorbeeld
|----------|-------------|---------|
| nlevel(ltree) Aantal etiketten nlevel('1.3.7') → 3 |
| subpath(ltree, offset) Het achtervoegsel van offset subpath('1.3.7', 1) → '3.7' |
| subpath(ltree, offset, len) Substring subpath('1.3.7', 1, 1) → '3' |
| subltree(ltree, start, end) Bereik van labels subltree('1.3.7', 0, 2) → '1.3' |
| lca(ltree, ltree) Laagste gemeenschappelijke voorouder lca('1.3.7', '1.3.9') → '1.3' |
| text2ltree(text) Tekst omzetten naar ltree text2ltree('1.3.7') |
| ltree2text(ltree) Converteer ltree naar tekst 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>
Operatie Complexiteit Notities |-----------|------------|-------| Zet gewoon de string van het pad in. Get children O(1) Patronen match met GiST index Krijg voorouders O(1) . . @> operator met GiST-index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Krijg afstammelingen O(1) <@ operator met GiST-index Het patroon dat overeenkomt met het patroon O(log n) GiST-index ondersteunt lquery Move subtree O(s) Update s afstammelingen Delete subtree O(1) > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > &
Met GiST indexen, ltree queries zijn uiterst efficiënt - typisch O(log n) ongeacht boomdiepte.
Bedankt voor je hulp.
|------|------|
Database-native optimalisatie PostgreSQL-only
GiST-index voor alle hiërarchie-vragen Uitbreidingsafhankelijkheid . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Krachtige patroon matching Labels gelimiteerd tot geamortiseerd Labels
Ingebouwde padmanipulatiefuncties Recursieve CTE's hebben rauwe SQL nodig
O(1) voorouder/afstammeling queries Minder draagbaar dan pure EF Core oplossingen
Compacte opslag
Ondersteuning voor LINQ via Npgsql's LTree type
Kies ltree wanneer:
Vermijd ltree wanneer:
Aspect Gematerialiseerde Pad . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
|--------|-------------------|-------|
Inhoudsopgave B-boom (alleen voorvoegsel) GiST (alle patronen)
Het patroon komt overeen met het patroon, zoals 'prefix%' alleen volledige wildcards
Opdrachtgevers Vergelijken van de tekenreeks Inheems @>, <@, ~
Alleen PostgreSQL
E.V. Core support via Full LINQ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . LTree type (CTE's hebben ruwe SQL nodig)
Uitstekend met GiST Uitstekend met GiST
Functies Geen (onbewerkte ontleden) Rijke functiebibliotheek
Het samenbrengen van alles - krijg een hele commentaar boom met diepte voor een blog post:
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; }
}
Deze serie heeft betrekking op vijf benaderingen van hiërarchische gegevens met behulp van EF Core. Deel 2 zal verkennen met behulp van ruwe SQL en Dapper voor nog meer controle over hiërarchie queries - komen binnenkort!
© 2026 Scott Galloway — Unlicense — All content and source code on this site is free to use, copy, modify, and sell.