DEV Community

Aatif G.
Aatif G.

Posted on

HierarchyId in SQL Server and EF Core 8 - Modeling Hierarchical Data

#sqlserver #efcore8 #dotnet #csharp

Hierarchical data is everywhere—from product categories and organizational charts to nested comments and file systems. SQL Server introduced the HierarchyId data type in SQL Server 2008, offering a compact and efficient way to represent tree structures. Now, with Entity Framework Core 8 in .NET 8, developers can use HierarchyId natively in their C# models.

Let’s explore how this works and why it matters.

🧠 What Is HierarchyId?

HierarchyId is a system-defined SQL Server data type that encodes a node's position in a hierarchy. It stores the path from the root to the node using a compact binary format, enabling fast traversal, sorting, and querying.

Example Paths:

  • / → Root
  • /1/ → First child of root
  • /1/3/ → Third child of the first child

🧱 SQL Server Capabilities

  • Depth-first ordering: Nodes are stored and compared in depth-first order.
  • Efficient indexing: Children of a node are stored close together.
  • Built-in methods:
    • GetAncestor()
    • GetDescendant()
    • IsDescendantOf()
    • GetLevel()

These methods allow you to query and manipulate tree structures without recursive joins.

🧬 EF Core 8 Integration

.NET 8 brings native support for HierarchyId in EF Core 8, eliminating the need for manual conversions or raw SQL.

✅ Example Model: 

public class Category
{
    public int Id { get; set; }
    public HierarchyId Path { get; set; }
}

modelBuilder.Entity<Category>()
    .Property(c => c.Path)
    .HasColumnType("hierarchyid");
Enter fullscreen mode Exit fullscreen mode

✅ Querying Descendants: 

var descendants = context.Categories
    .Where(c => c.Path.IsDescendantOf(parent.Path))
    .ToList();
Enter fullscreen mode Exit fullscreen mode

EF Core translates this into SQL using the native IsDescendantOf() method.

🧭 Use Cases

  • Product categories with nested subcategories
  • Organizational charts with departments and roles
  • Menu structures for dynamic navigation
  • Multi-tenant config trees with scoped overrides

🛠️ Tips for Using HierarchyId

  • Use GetDescendant() to generate new child paths
  • Index the HierarchyId column for fast traversal
  • Avoid deep nesting beyond 892 bytes (SQL Server limit)
  • Use depth-first sorting for hierarchical display

🧾 Final Thoughts

With SQL Server’s mature HierarchyId and EF Core 8’s native support, modeling hierarchical data is now clean, efficient, and fully integrated into .NET 8. Whether you're building admin panels, category trees, or nested workflows, this feature unlocks powerful new patterns for scalable design.

🔍 Want More on .NET 8?

HierarchyId is just one piece of the puzzle. .NET 8 brings major upgrades across ASP.NET Core, EF Core, and the runtime—like native AOT compilation, Blazor unification, complex types, primitive collections, and powerful new C# 12 syntax.

Explore detailed .NET 8 breakdown here

Top comments (0)