Back to "Partie des hiérarchies de données 1.5: PostgreSQL ltree avec EF Core"

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

EF Hierarchies Entity Framework PostgreSQL

Partie des hiérarchies de données 1.5: PostgreSQL ltree avec EF Core

Saturday, 06 December 2025

PostgreSQL's ltree extension vous donne des chemins matériellisés avec la base de données -superpuissances nativesM SK2 indices GiST, opérateurs spécialisés comme @> et <@, et une combinaison puissante de patrons . Si vous êtes engagé(e) dans PostgreSQL et désirez obtenir le meilleur rendement en matière de recherche hiérarchique, ltree est difficile à battre

Bonnes nouvelles: L'article 5 de la Loi sur l'immigration et le statut des réfugiés Le fournisseur EF Core de Npgsql appuie les traductions LINQ pour les opérations ltree par l’entremise du LTree type. Vous pouvez utiliser des méthodes comme IsAncestorOf(), IsDescendantOf(), et MatchesLQuery() Directement dans les demandes de LINQ. Cependant , EF Core ne supporte pas encore les CTE récursives, vous aurez donc besoin de SQL brut pour les opérations qui en ont besoin M SK4 comme pour construire des résultats complets d'un sous-trei avec des profondeurs calculées

Merci à Shay Rojansky pour indiquer le support de traduction LINQ!

Qu'est-ce que ltree?

ltree est une extension PostgreSQL qui fournit un type de données natif pour les chemins d’étiquetage hiérarchiques. Voie materialisée avec les superpuissances - la base de données comprend la structure et fournit des opérateurs optimisés , fonctions

Au lieu de traiter le chemin comme une chaîne stupide et d’utiliser des questions LIKE, PostgreSQL peut:

  • Utiliser des opérateurs spécialisés (@> pour " est le prédécesseur deM SK1 <@ pour " est le descendant deM SK1
  • Appliquer les indices GiST pour effectuer des recherches de hiérarchie efficace
  • Coordonner les patrons avec des cartes franches (Top.*.Europe)
  • Exécuter des opérations de mise en place sur les chemins

Perspectives clés: ltree est le meilleur des deux mondes - la simplicité des voies matérialisées avec une base de données - optimisation initiale M SK2 Le commerce-off est PostgreSQL lockM SK4inMSC5 et bien que nombre d'opérations ltree fonctionnent via LINQMNK6 les CTE récursives requièrent encore un SQL brutMRK7

Format du chemin de l'arbre

Les chemins dans l'arbre utilisent les périodes comme séparateurs et étiquettes alphanumériques:

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

Règles:

  • Les étiquettes peuvent contenir des lettres, des chiffres , et des sous-titres
  • Les étiquettes sont case-sensibles
  • La longueur maximale de l'étiquette est 256 caractères
  • La longueur maximale du chemin est de 65535 labels

Pour les systèmes de commentaires, nous utilisons des identifiants comme étiquettes. 1.3.7 signification "comment 7 sous commentaire MSC2 sous commentaires MSC3

Établissement de l'arbre

Premièrement, activer l'extension (requitte les privilèges de superutilisateur de la base de donnéesM SK2

CREATE EXTENSION IF NOT EXISTS ltree;

Ou via EF Core migration:

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

Définition de l'entité

Le fournisseur de Npgsql comprend un LTree type qui représente directement l'arbre de PostgreSQL's et fournit LINQM SK1méthodes traduites:

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

Configuration de l'EF Core

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

Ajouter l’indice GiST par migration:

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

Opérateurs de l'arbre

ltree fournit des opérateurs puissants. Le fournisseur EF Core de Npgsql traduit LTree méthodes à ces opérateurs:

opérateur signification Méthode de l'LINQ M Exemple SQL
@> est le prédécesseur de ( contientM SK2 ltree1.IsAncestorOf(ltree2) '1.3'::ltree @> '1.3.7'::ltree → vrai
<@ est le descendant de ( contenant d) ltree1.IsDescendantOf(ltree2) '1.3.7'::ltree <@ '1.3'::ltree → vrai
~ Correspond à l'échantillonnage ltree.MatchesLQuery(pattern) '1.3.7'::ltree ~ '1.*'::lquery → vrai
@ Correspond à l'enquête ltxt ltree.MatchesLTxtQuery(query) '1.3.7'::ltree @ '3 & 7'::ltxtquery → vrai
|| Voies de concatenation (utiliser la concaténation des chaînes) '1.3'::ltree || '7'::ltree → '1.3.7'
<, >, <=, >= Comparaison Exploitants normalisés MSC2 Pour le tri MSC3

Autres LINQ-propriétés et méthodes traduites:

  • ltree.NLevel → nlevel(ltree) - nombre d'étiquettes dans le chemin
  • ltree.Subtree(start, end) → subltree(ltree, start, end) - range d'extraction des étiquettes
  • ltree.Subpath(offset) → subpath(ltree, offset) - suffix de l'offset
  • ltree.Subpath(offset, len) → subpath(ltree, offset, len) Sous-string -
  • ltree.Index(subpath) → index(ltree, subpath) - trouver la position de sous-route
  • LTree.LongestCommonAncestor(ltree1, ltree2) → lca(ltree1, ltree2) - Ancêtre commun le plus bas

Opérations

Insérer un nouveau commentaire

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

Obtenir des enfants immédiats

Utilisation de parentCommentId (simpleM SK1 ou ltree pattern 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);
}

Obtenir tous les ancêtres

Utilisation de LINQ avec le IsAncestorOf méthode ( traduit à @> opérateur):

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

Obtenir tous les descendants

Utilisation de LINQ avec le IsDescendantOf méthode ( traduit à <@ opérateur):

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

Mettre les descendants à la profondeur maximale

Utilisation de LINQ avec NLevel pour la limite de profondeur:

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

Demandes de concordance du modèle

ltree appuie de puissants modèles d'enquête. Utilisation MatchesLQuery dans 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);
}

Supprimer un sous-arbre

Vous pouvez utiliser LINQ pour sélectionner le sous-arbre, puis supprimer:

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

Déplacer un sous-arbre

ltree fournit des fonctions pour faciliter la manipulation du chemin:

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

Référence aux fonctions de ltree

PostgreSQL fournit de nombreuses fonctions ltree utiles:

Fonction Description Exemple S
nlevel(ltree) Nombre d'étiquettes nlevel('1.3.7') → 3
subpath(ltree, offset) Suffix de l'offset subpath('1.3.7', 1) → '3.7'
subpath(ltree, offset, len) Sous-string subpath('1.3.7', 1, 1) → '3'
subltree(ltree, start, end) Éventail des étiquettes subltree('1.3.7', 0, 2) → '1.3'
lca(ltree, ltree) Ancêtre commun le plus bas lca('1.3.7', '1.3.9') → '1.3'
text2ltree(text) Convertez le texte à l'arbre text2ltree('1.3.7')
ltree2text(ltree) Convertir l'arbre en texte ltree2text('1.3.7'::ltree)

Visualisation du flux de demandes

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>

Caractéristiques du rendement

Exploitation Complexité MSC2 Notes MSC3
Insérer
Obtenir des enfants O(1) Coordonner le modèle avec l'indice GiST
Obtenir les ancêtres O(1) ≥@> opérateur avec indice GiST
Obtenir des descendants O(1) ≥<@ opérateur avec indice GiST MSC5
Correspondance du parallèle OM SK2log n L'indice GiST supporte lquery
Déplacer l'arbre de sous-treivre OM SK2s) MSC4 Mise à jour des chemins descendants s SMC5
Supprimer l'arbre de sous-treivre O(1)

Avec les indices GiST, les demandes d'arbre sont extrêmement efficaces - typiquement OM SK2log nMSC3 indépendamment de la profondeur de l'arbre

Pros et Cons

Faits positifs Cons
Base de donnéesM SK1 Optimisation native PostgreSQL-seulement
Indice GiST pour toutes les demandes de la hiérarchie
Correspondance puissante des patrons Étiquettes limitées à l'alphanumérique
Functions de manipulation des voies -Construites CTE récursives requièrent un SQL brut
OM SK1 ancêtre/ demandes d'ascendance Moins portatif que les solutions EF Core pures
Entreposage compact
Soutien à l'LINQ par le biais de Npgsql's LTree type

Quand utiliser l'arbre

Choisissez l'arbre lorsque:

  • Vous'êtes engagé à PostgreSQL
  • Le rendement est essentiel pour les demandes hiérarchiques
  • Vous avez besoin d'un modèle correspondant (Trouver tous X.*.Paths YM SK1
  • Vous voulez le meilleur des chemins matérialisés
  • Vous voulez un support LINQ pour la plupart des opérations hiérarchiques

Évitez l'arbre lorsque:

  • Vous avez besoin de portabilité de la base de données (SQL Server, MySQLM SK2 etc .)
  • Votre équipe n'est pas familiere avec les extensions PostgreSQL
  • Les étiquettes doivent comporter des caractères alphanumériques non-
  • Vous avez besoin de CTE recursives et vous voulez éviter toute SQL brute

Comparaison avec le chemin matérialisé

Aspect Voie materialisée SSK2 arbuste MSC3
Type d'indice BM SK2arbre MSC3seulement préfixe
Correspondance du paramètre LIKE MSC2 préfixeMSc3 seulement MSC4 Cartes franches complètes SMC5
Opérateurs Comparaison des chaînes
Portability Toute base de données
EF Support de base LINQ complet LTree type (CTEs ont besoin de SQL brut
Rendement Bon avec l'indice Excellent avec le taux de change GiST M
Fonctions Aucune parçage manuelM SK3 S Bibliothèque de fonctions riches ≥

Exemple: Demande d'arbre de commentaires complet

Mettre tout ensemble - obtenir un arbre de commentaires complet avec une profondeur pour un post de blog:

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

Qu’est-ce que's Suivant?

Cette série a porté sur cinq approches des données hiérarchiques en utilisant EF Core. Partie 2 explorera l’utilisation de SQL brut et du Dapper pour mieux contrôler les demandes hiérarchiques - prochainementM SK3

logo

© 2026 Scott Galloway — Unlicense — All content and source code on this site is free to use, copy, modify, and sell.