Modeling Hierarchical Data in EF Core

Model hierarchical data in EF Core with an adjacency list: a nullable ParentId foreign key, a Parent navigation, and a Children collection on the same entity. Load the rows of the tree in one query and relationship fixup wires the object graph for you. When the hierarchy outgrows that, push the recursion into the database with a recursive CTE, or store materialized paths with PostgreSQL ltree.

Product categories nest. Org charts nest. Comment threads, folder structures, chart-of-accounts, all of them are trees, and sooner or later you have to persist one in a relational database.

The natural model in EF Core, a self-referencing entity with a ParentId, takes five minutes to write. Then you try to load a subtree, or delete a node, or find every descendant of "Electronics", and discover that the write model and the read patterns are at war.

The adjacency list works well when its self-referencing relationship and read strategy are explicit. The two escalation paths, recursive CTEs and PostgreSQL ltree, become useful when the tree gets large.

The Adjacency List

An adjacency list stores a tree in one table by giving every row a foreign key that points at its parent. Roots have a null parent:

public class Category
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;

    public int? ParentId { get; set; }
    public Category? Parent { get; set; }
    public List<Category> Children { get; set; } = [];
}

The configuration deserves care, especially the delete behavior:

public class CategoryConfiguration : IEntityTypeConfiguration<Category>
{
    public void Configure(EntityTypeBuilder<Category> builder)
    {
        builder.HasOne(c => c.Parent)
            .WithMany(c => c.Children)
            .HasForeignKey(c => c.ParentId)
            .OnDelete(DeleteBehavior.Restrict);

        builder.HasIndex(c => c.ParentId);
    }
}

Two deliberate choices:

• DeleteBehavior.Restrict. SQL Server rejects cascade on self-referencing foreign keys outright (cycle detection), and even on PostgreSQL, where it works, cascading a node delete into silently vaporizing a subtree is rarely what the business meant. Make subtree deletion an explicit operation. The broader reasoning is in cascade delete in EF Core.
• An index on ParentId. Every children lookup filters on it. Skipping this index is the most common hierarchy performance bug I see.

How Do You Load a Whole Tree Without N+1 Queries?

One level is easy:

var roots = await context.Categories
    .Where(c => c.ParentId == null)
    .Include(c => c.Children)
    .ToListAsync();

The trap is trying to go deeper with chained includes (.Include(c => c.Children).ThenInclude(c => c.Children) and so on). That hardcodes a maximum depth, and each level multiplies the join. The recursive-lazy-loading variant is worse: walking Children with lazy loading fires one query per node, the classic shape from the N+1 query problem.

For hierarchies of reasonable size (navigation menus, category trees, org units, anything up to a few thousand rows), the right move is almost embarrassingly simple: load the whole set in one query and let relationship fixup build the tree.

var all = await context.Categories.ToListAsync();

var roots = all.Where(c => c.ParentId is null).ToList();

When EF Core materializes the rows, identity resolution connects every Parent and Children navigation automatically. One SELECT, and roots is a fully wired object graph you can recurse in memory. This works with tracking queries, and with AsNoTrackingWithIdentityResolution if you do not need tracking. Plain AsNoTracking skips identity resolution, so the rows materialize as disconnected objects and the navigations stay unwired.

Scope the query if the table holds many independent trees:

var all = await context.Categories
    .Where(c => c.TreeId == treeId)
    .ToListAsync();

Recursive CTEs: Push the Recursion into the Database

When the hierarchy has hundreds of thousands of rows and you need one subtree, loading everything stops being cute. Relational databases walk hierarchies natively with recursive CTEs, and EF Core consumes them cleanly through SqlQuery or FromSqlRaw:

var subtree = await context.Database
    .SqlQuery<CategoryRow>($"""
        WITH RECURSIVE subtree AS (
            SELECT "Id", "Name", "ParentId"
            FROM "Categories"
            WHERE "Id" = {rootId}

            UNION ALL

            SELECT c."Id", c."Name", c."ParentId"
            FROM "Categories" c
            JOIN subtree s ON c."ParentId" = s."Id"
        )
        SELECT "Id", "Name", "ParentId" FROM subtree
        """)
    .ToListAsync();

(That is PostgreSQL syntax; SQL Server drops the RECURSIVE keyword and brackets the identifiers. One SQL Server catch: SqlQuery composes over your SQL as a subquery, and T-SQL does not allow a CTE inside one, so use FromSql on a mapped entity there instead.)

The same pattern answers the other classic questions: the ancestor chain of a node (flip the join direction), the depth of each node (carry a level + 1 column through the recursion), and "does moving node X under node Y create a cycle" (check whether Y appears in X's descendants).

This is one of the places where dropping to SQL is not a failure of the ORM, it is using each tool for what it is good at. I covered the mechanics and parameterization safety in EF Core raw SQL queries.

PostgreSQL ltree: Materialized Paths with an Index

If your workload is read-heavy on subtrees, PostgreSQL's ltree extension changes the data structure itself. Each node stores its full path as a chain of labels (electronics.computers.laptops), and a GiST index makes subtree and ancestor queries indexed operations instead of recursion.

Npgsql maps the LTree type directly:

public class Category
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public LTree Path { get; set; }
}

// in OnModelCreating
modelBuilder.HasPostgresExtension("ltree");

modelBuilder.Entity<Category>()
    .HasIndex(c => c.Path)
    .HasMethod("gist");

Subtree and ancestor queries become one-liners that translate to indexed operators:

// all descendants of electronics.computers
var descendants = await context.Categories
    .Where(c => c.Path.IsDescendantOf(new LTree("electronics.computers")))
    .ToListAsync();

// ancestors of a node
var ancestors = await context.Categories
    .Where(c => new LTree("electronics.computers.laptops").IsDescendantOf(c.Path))
    .ToListAsync();

The cost shows up on writes: moving a subtree means rewriting the Path of every descendant, and the application owns path maintenance (nothing enforces that electronics.computers exists just because a path mentions it). Many teams run ltree alongside a ParentId, treating the path as a derived, indexed acceleration structure. If you are Postgres-curious about features like this, PostgreSQL vs SQL Server for .NET developers covers more of what you gain.

SQL Server's counterpart is hierarchyid, supported in EF Core 8+ via Microsoft.EntityFrameworkCore.SqlServer.HierarchyId, with similar tradeoffs.

Choosing a Hierarchy Strategy

• Small tree, read whole or nearly whole (menus, categories, org units under ~5k rows): adjacency list, load-all plus fixup. Simplest code, one query, done.
• Large tree, occasional subtree or ancestor queries: adjacency list plus recursive CTEs for the heavy questions.
• Large tree, subtree queries on the hot path, rare moves: ltree (or hierarchyid) with a GiST index, likely alongside the adjacency columns.
• Comment threads and event-like data that only append: adjacency list; threads are shallow and append-only, recursion rarely hurts.

Summary

The adjacency list is the right default: it is normalized, enforces referential integrity, and EF Core's relationship fixup gives you an underrated superpower, load the rows in one query and get a fully connected tree for free. Configure Restrict on the self-reference, index ParentId, and never chain ThenInclude to fake recursion.

When the tree outgrows load-everything, do not fight LINQ into recursion it cannot express. Recursive CTEs answer subtree, ancestor, and depth questions inside the database, and Postgres ltree turns subtree reads into indexed lookups when they dominate your workload.

Pick the structure by the queries you run, not by the shape of the data. Trees are all shaped the same; workloads are not.