La liste d’agglomérations est l’approche la plus simple et la plus intuitive pour stocker les données hiérarchiques - chaque rangage indique simplement son parent . Celle-ci ' c’est ce que la plupart des développeurs atteignent pour le premier fois M SK3 et pour les arbres peu profonds avec des déplacements fréquents MSC4 Celles-ci≥ ' c'est souvent la bonne option MNK6 Cet article décrit les détails de la mise en œuvre MMK7 y compris les ETP récursifs permettant de traverser l’arbre et de construire des structures encadrées pour l’affichage de l’interface.

Navigation des séries

• Partie 1: Aperçu - Introduction et comparaison
• Partie 1.1: Liste de voisinage (le présent articleM SK1
• Partie 1.2: Tableau de fermeture
• Partie 1.3: Voie matérialisée
• Partie 1.4: Ensembles en positionnement
• Partie 1.5: ltree

Qu’est-ce qu’une liste de voisinage?

Le modèle de liste d'agglomération ( est un terme coincé par Joe Celko) est l’approche que la plupart des développeurs appliquent pour le premier , et c’est avec une bonne raison.

La caractéristique clé: chaque ligne ne connaît que son parent immédiat. Pour trouver les grands-parents ou les petits-enfants, vous avez besoin de recherches multiples ou de demandes récursives.

Ce modèle est le plus souvent documenté et est bien appuyé par EF Core's self-relations de référence.

Définition de l'entité

L'entité est remarquablement simple - nous ajoutons simplement un self non négligeable -reference:

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

    // Foreign key - which blog post this comment belongs to
    public int PostId { get; set; }
    public BlogPost Post { get; set; } = null!;

    // ========== ADJACENCY LIST: The hierarchy is defined by these three properties ==========

    // ParentCommentId is nullable because:
    // - Root-level comments (direct replies to the post) have NULL
    // - Nested replies have the ID of the comment they're replying to
    public int? ParentCommentId { get; set; }

    // Navigation property to load the parent comment when needed
    // Useful for breadcrumb trails: "Post > Comment by Alice > Comment by Bob"
    public Comment? ParentComment { get; set; }

    // Navigation property to load immediate children (NOT grandchildren!)
    // EF Core populates this when you use .Include(c => c.Children)
    // NOTE: This only gives you ONE level deep - you won't see replies to replies
    public ICollection<Comment> Children { get; set; } = new List<Comment>();
}

Configuration de l'EF Core

La configuration établit la relation de référence self- et, crucialement, ajoute des indices pour les demandes que nous poursuivrons le plus souvent.

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

        // Standard relationship: comment belongs to a blog post
        // Cascade delete here is safe - deleting a post should remove all its comments
        builder.HasOne(c => c.Post)
            .WithMany(p => p.Comments)
            .HasForeignKey(c => c.PostId)
            .OnDelete(DeleteBehavior.Cascade);

        // ========== THE SELF-REFERENCING RELATIONSHIP ==========
        // This is what makes it an adjacency list - each comment points to its parent

        builder.HasOne(c => c.ParentComment)
            .WithMany(c => c.Children)           // One parent has many children
            .HasForeignKey(c => c.ParentCommentId)
            .OnDelete(DeleteBehavior.Restrict);  // WARNING: Don't use Cascade here!

        // Why Restrict and not Cascade?
        // With Cascade, deleting a parent would automatically delete ALL children,
        // grandchildren, etc. This can be:
        // 1. Unexpected behaviour for users
        // 2. A database performance issue (many deletes)
        // 3. A data integrity risk (accidental mass deletion)
        // Better to handle subtree deletion explicitly in application code

        // ========== INDEXES: Critical for performance ==========

        // Index on ParentCommentId - used when loading children
        // "SELECT * FROM comments WHERE parent_comment_id = @id"
        builder.HasIndex(c => c.ParentCommentId);

        // Index on PostId - used when loading all comments for a post
        // "SELECT * FROM comments WHERE post_id = @id"
        builder.HasIndex(c => c.PostId);

        // Composite index for the most common query:
        // "Get all comments for a post, ordered by creation date"
        builder.HasIndex(c => new { c.PostId, c.CreatedAt });
    }
}

Système de base de données

Le schéma qui en résulte est minimal : - un seul utilisateur personnel;

erDiagram
    COMMENT {
        int id PK
        string content
        string author
        datetime created_at
        int post_id FK
        int parent_comment_id FK "nullable - NULL for root comments"
    }

    BLOG_POST {
        int id PK
        string title
        string content
    }

    BLOG_POST ||--o{ COMMENT : "has"
    COMMENT ||--o{ COMMENT : "has children"

Opérations

Insérer un nouveau commentaire

Les insertions sont magnifiquement simples - le modèle de liste d'agglomération brille vraiment ici:

public async Task<Comment> AddCommentAsync(
    int postId,
    int? parentId,      // NULL for root comment, parent's ID for a reply
    string author,
    string content,
    CancellationToken ct = default)
{
    // Creating a comment is just setting the parent reference
    // No need to update closure tables, recalculate paths, or renumber anything
    var comment = new Comment
    {
        PostId = postId,
        ParentCommentId = parentId,  // This single reference defines the hierarchy
        Author = author,
        Content = content,
        CreatedAt = DateTime.UtcNow
    };

    context.Comments.Add(comment);
    await context.SaveChangesAsync(ct);

    logger.LogInformation("Added comment {CommentId} to post {PostId}", comment.Id, postId);
    return comment;
}

Obtenir des enfants immédiats

Demande indexée unique - rapide et simpleM SK1

public async Task<List<Comment>> GetChildrenAsync(int commentId, CancellationToken ct = default)
{
    // This is WHERE adjacency lists shine - getting children is trivial
    // Single indexed lookup: WHERE parent_comment_id = @id
    return await context.Comments
        .AsNoTracking()                              // Read-only, no tracking overhead
        .Where(c => c.ParentCommentId == commentId)  // Uses the index we defined
        .OrderBy(c => c.CreatedAt)                   // Chronological order
        .ToListAsync(ct);
}

Obtenir des ancêtres (La partie dure)

Voici's où les listes d’agglomération montrent leur faiblesse . Sans questions récursives, vousM SK3 devriez faire plusieurs voyages de tournéeMSC4 prendre votre grand-parentMST5 prendre son parentM ST6 sa mèreM st7 prendre ses grands-parentsMSt8 et ainsi de suiteMst9

Mercifully, PostgreSQL's CTEs recursifs (WITH RECURSIVEM SK1 venir en rescue:

public async Task<List<Comment>> GetAncestorsAsync(int commentId, CancellationToken ct = default)
{
    // WHY RAW SQL?
    // EF Core doesn't have great support for recursive CTEs
    // We need to drop down to raw SQL for this

    // HOW THE CTE WORKS:
    // 1. Start with the target comment (WHERE id = {0})
    // 2. UNION ALL joins each result with its parent (JOIN on parent_comment_id)
    // 3. PostgreSQL keeps doing this until no more parents are found
    // 4. We exclude the starting comment (WHERE id != {0}) to get only ancestors

    var sql = @"
        WITH RECURSIVE ancestors AS (
            -- Base case: start with our target comment
            SELECT * FROM comments WHERE id = {0}

            UNION ALL

            -- Recursive case: join each result with its parent
            SELECT c.*
            FROM comments c
            INNER JOIN ancestors a ON c.id = a.parent_comment_id
        )
        -- Return all ancestors except the starting comment, in ID order (root first)
        SELECT * FROM ancestors WHERE id != {0}
        ORDER BY id";

    return await context.Comments
        .FromSqlRaw(sql, commentId)
        .AsNoTracking()
        .ToListAsync(ct);
}

Obtenir un sous-arbre entier avec profondeur

De plus, il faut un CTE récursif, mais cette fois nous repérons la profondeur à mesure que nous allons.

public async Task<List<CommentWithDepth>> GetDescendantsWithDepthAsync(
    int commentId,
    CancellationToken ct = default)
{
    // Similar to ancestors, but we go DOWN the tree instead of UP
    // We also track depth so we know how to indent in the UI

    var sql = @"
        WITH RECURSIVE descendants AS (
            -- Base case: start with our target comment at depth 0
            SELECT *, 0 as depth FROM comments WHERE id = {0}

            UNION ALL

            -- Recursive case: find children of each result, incrementing depth
            SELECT c.*, d.depth + 1
            FROM comments c
            INNER JOIN descendants d ON c.parent_comment_id = d.id
        )
        -- Return all descendants, ordered for display
        -- depth first, then by creation time within each level
        SELECT id, content, author, created_at, post_id, parent_comment_id, depth
        FROM descendants
        WHERE id != {0}
        ORDER BY depth, created_at";

    // Note: We need a special DTO to capture the depth column
    // EF Core's FromSqlRaw won't automatically map extra columns to entity properties
    return await context.Database
        .SqlQueryRaw<CommentWithDepth>(sql, commentId)
        .ToListAsync(ct);
}

// DTO to hold comment data plus computed depth
public class CommentWithDepth
{
    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 int Depth { get; set; }  // Computed by the CTE
}

Établir une structure d'arbres nichés pour l'UI

Souvent, ce dont vous avez besoin pour rendre une image est la structure d’un arbre, non une liste plane . Voici' comment construire efficacement un arbre

public async Task<List<CommentTreeNode>> GetCommentTreeAsync(int postId, CancellationToken ct = default)
{
    // STRATEGY:
    // 1. Load ALL comments for the post in a single query (fast, one round trip)
    // 2. Build the tree structure in memory (also fast, just pointer manipulation)

    // Step 1: Get all comments for this post
    var allComments = await context.Comments
        .AsNoTracking()
        .Where(c => c.PostId == postId)
        .OrderBy(c => c.CreatedAt)  // Consistent ordering
        .ToListAsync(ct);

    // Step 2: Create a lookup by parent ID
    // This gives us O(1) access to children of any comment
    var lookup = allComments.ToLookup(c => c.ParentCommentId);

    // Step 3: Build the tree starting from root comments (ParentCommentId = null)
    return BuildTree(lookup, null);
}

private List<CommentTreeNode> BuildTree(
    ILookup<int?, Comment> lookup,
    int? parentId)
{
    // Recursively build tree nodes
    // lookup[parentId] gives us all comments whose parent is 'parentId'
    return lookup[parentId]
        .Select(c => new CommentTreeNode
        {
            Comment = c,
            Children = BuildTree(lookup, c.Id)  // Recurse to get children
        })
        .ToList();
}

public class CommentTreeNode
{
    public Comment Comment { get; set; } = null!;
    public List<CommentTreeNode> Children { get; set; } = new();

    // Convenience property for UI
    public bool HasChildren => Children.Count > 0;
}

Supprimer un sous-arbre

La suppression requiert la recherche de tous les descendants d’abord:

public async Task DeleteSubtreeAsync(int commentId, CancellationToken ct = default)
{
    // We need to find and delete all descendants, then the comment itself
    // Using a CTE to get all IDs, then bulk delete

    var sql = @"
        WITH RECURSIVE subtree AS (
            SELECT id FROM comments WHERE id = {0}
            UNION ALL
            SELECT c.id FROM comments c
            INNER JOIN subtree s ON c.parent_comment_id = s.id
        )
        DELETE FROM comments WHERE id IN (SELECT id FROM subtree)";

    var deleted = await context.Database.ExecuteSqlRawAsync(sql, new object[] { commentId }, ct);

    logger.LogInformation("Deleted {Count} comments in subtree rooted at {CommentId}",
        deleted, commentId);
}

Déplacer un sous-arbre

C'est là où les listes d'agglomérations brillent vraiment - le déplacement est trivialM SK1

public async Task MoveSubtreeAsync(
    int commentId,
    int newParentId,
    CancellationToken ct = default)
{
    // In adjacency list, moving a subtree is just updating ONE row!
    // All descendants automatically move with their parent because
    // their ParentCommentId still points to their direct parent

    var comment = await context.Comments.FindAsync(new object[] { commentId }, ct);
    if (comment == null)
    {
        throw new InvalidOperationException($"Comment {commentId} not found");
    }

    // Prevent creating a cycle (moving a node under its own descendant)
    // This would create an infinite loop in our tree
    var ancestors = await GetAncestorsAsync(newParentId, ct);
    if (ancestors.Any(a => a.Id == commentId))
    {
        throw new InvalidOperationException("Cannot move a comment under its own descendant");
    }

    comment.ParentCommentId = newParentId;
    await context.SaveChangesAsync(ct);

    logger.LogInformation("Moved comment {CommentId} to new parent {NewParentId}",
        commentId, newParentId);
}

Visualisation du flux de demandes

sequenceDiagram
    participant App as Application
    participant EF as EF Core
    participant DB as PostgreSQL

    Note over App,DB: Getting Children (Simple - O(1))
    App->>EF: GetChildrenAsync(commentId)
    EF->>DB: SELECT * FROM comments WHERE parent_id = @id
    DB-->>EF: Results (indexed lookup)
    EF-->>App: List<Comment>

    Note over App,DB: Getting Ancestors (Recursive - O(d) where d=depth)
    App->>EF: GetAncestorsAsync(commentId)
    EF->>DB: WITH RECURSIVE ancestors AS (...)
    DB->>DB: Traverse parent_id chain recursively
    DB-->>EF: All ancestors
    EF-->>App: List<Comment>

Caractéristiques du rendement

Pros et Cons

Quand utiliser la liste de voisinage

Choisissez la liste de voisinage lorsque:

• Votre hiérarchie est peu étendue (moins de 5-6 niveaux
• Vous déplacez fréquemment les sous-arbres
• Vous voulez que les propriétés de navigation EF Core' fonctionnent naturellement
• Exigences simples lorsque les ETP récursives sont acceptables
• Insérer la performance est plus important que de lire la performance

Évitez la liste de voisinage lorsque:

• Vous avez des hiérarchies profondes (10+ niveaux
• Vous demandez fréquemment "tous les descendantsM SK1 ou "toux les ancêtres"
• La performance de lecture est essentielle
• Vous devez compter les descendants sans les charger.

Navigation des séries

• Partie 1: Aperçu
• Partie 1.1: Liste de voisinage (le présent articleM SK1
• Partie 1.2: Tableau de fermeture
• Partie 1.3: Voie matérialisée
• Partie 1.4: Ensembles en positionnement
• Partie 1.5: ltree