# Modeling Hierarchical Data in EF Core

> Categories with subcategories, org charts, comment threads: hierarchies are everywhere, and the obvious self-referencing entity is easy to write and brutal to query recursively. Here is how to model adjacency lists in EF Core, load whole trees without N+1 queries, and when to reach for recursive CTEs or Postgres ltree instead.

Published: 2026-08-28. Author: Milan Jovanović.

Canonical: https://milanjovanovic.tech/blog/ef-core-hierarchical-data

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**](https://milanjovanovic.tech/blog/entity-relationships-ef-core) 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:

![Adjacency list tree where Electronics is a root with a null ParentId, branching into Computers and Phones, and Computers branching further into Laptops and Desktops](https://milanjovanovic.tech/blogs/articles/ef-core-hierarchical-data/adjacency-tree.png)


```csharp
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:

```csharp
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**](https://milanjovanovic.tech/blog/ef-core-cascade-delete).
- **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:

```csharp
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**](https://milanjovanovic.tech/blog/n-plus-one-query-ef-core).

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.**

```csharp
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:

```csharp
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`:

```csharp
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**](https://milanjovanovic.tech/blog/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:

```csharp
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:

```csharp
// 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.

## Frequently asked questions

### How do I model a self-referencing entity in EF Core?

Give the entity a nullable ParentId foreign key, a Parent navigation, and a Children collection, then configure them with HasOne(Parent).WithMany(Children).HasForeignKey(ParentId). Roots have a null ParentId.

### How do I load an entire tree in EF Core without N+1 queries?

Load all rows of the hierarchy (or the relevant subtree scope) in a single query with tracking or identity resolution enabled. EF Core relationship fixup automatically connects parents and children in memory, so you can walk the tree without extra queries.

### Why should I avoid cascade delete on self-referencing relationships?

SQL Server refuses cascade delete on self-referencing foreign keys because of cycle detection, so migrations fail or you must use restrict. Even where allowed, deleting a node silently deleting an entire subtree is rarely the behavior you want.

### What is a recursive CTE and when do I need it?

A recursive common table expression is SQL that walks a hierarchy inside the database, following parent-child links until no more rows match. Use it when you need a subtree, ancestor chain, or depth calculation on a large hierarchy without loading the whole table.

### When is PostgreSQL ltree better than an adjacency list?

ltree stores the full path of each node as a label chain and indexes it with GiST, so subtree and ancestor queries become simple indexed comparisons instead of recursion. It shines for deep hierarchies with frequent subtree reads and infrequent moves.