Using SQL Server HierarchyId with Entity Framework Core

The HierarchyId data type was introduced in SQL Server 2008. It represents hierarchical data, capturing both parent/child relationships and the ordering of sibling nodes. Values of this type are stored in binary form, for example 0x5AC0 or 0x58, but in practice you work with string notation such as '/' for the root or '/1/' for a child of the root node, and so on:

A ParentId column is a common way to model hierarchies. While straightforward to set up, certain operations can be harder and slower compared to using HierarchyId. In this post, we'll see how to work with hierarchical data using the HierarchyId data type with Entity Framework Core.

If you don't have a local SQL Server instance, you can start one using Docker:

docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=yourStrong(!)Password" -p 1433:1433 -d mcr.microsoft.com/mssql/server:2019-latest

#Configuring the project

To use Entity Framework Core and the HierarchyId data type, you need to add the following NuGet packages to your project:

• Microsoft.EntityFrameworkCore.SqlServer
• EntityFrameworkCore.SqlServer.HierarchyId

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>nercoreapp3.1</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="3.1.7" />
    <PackageReference Include="EntityFrameworkCore.SqlServer.HierarchyId" Version="1.1.0" />
  </ItemGroup>

</Project>

#Creating the model

Let's create a basic model with a HierarchyId:

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using Microsoft.EntityFrameworkCore;

public class CompanyDbContext : DbContext
{
    public DbSet<Employee> Employees { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
        var connectionString = "Server=localhost;Database=DemoHierarchyId;User Id=sa;Password=yourStrong(!)Password;";
        options.UseSqlServer(connectionString, conf =>
        {
            conf.UseHierarchyId();
        });
    }
}

public class Employee
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int Id { get; set; }
    public string Name { get; set; }
    public HierarchyId HierarchyId { get; set; }
    public HierarchyId OldHierarchyId { get; set; }
}

You can create the database using EnsureCreatedAsync or EF Core migrations:

using var dbContext = new CompanyDbContext();
await dbContext.Database.EnsureCreatedAsync();
Console.WriteLine("db created!");

using var dbContext = new CompanyDbContext();
dbContext.Employees.AddRange(
    new Employee { Id = 1, Name = "John Doe", HierarchyId = HierarchyId.Parse("/") },
    new Employee { Id = 2, Name = "Lidia Brewer", HierarchyId = HierarchyId.Parse("/1/") },
    new Employee { Id = 3, Name = "Hannah Wicks", HierarchyId = HierarchyId.Parse("/2/") },
    new Employee { Id = 4, Name = "Sheridan Perkins", HierarchyId = HierarchyId.Parse("/1/1/") },
    new Employee { Id = 5, Name = "Zakaria Bailey", HierarchyId = HierarchyId.Parse("/1/2/") },
    new Employee { Id = 6, Name = "Albert Woodward", HierarchyId = HierarchyId.Parse("/1/2/1/") },
    new Employee { Id = 7, Name = "Arron Mcdaniel", HierarchyId = HierarchyId.Parse("/1/2/2/") }
    );
await dbContext.SaveChangesAsync();

#Insert a child

You can create a new HierarchyId using GetDescendant(child1, child2). This method returns a descendant node whose value is between child1 and child2. If ordering among siblings is not required, pass null for both arguments. This post explains in more detail how to use GetDescendant.

using var dbContext = new CompanyDbContext();
var manager = await dbContext.Employees.FindAsync(1); // '/'
dbContext.Employees.Add(
    new Employee
    {
        Id = 8,
        Name = "new",
        HierarchyId = manager.HierarchyId.GetDescendant(null, null),
    });
await dbContext.SaveChangesAsync();

#Order by HierarchyId

Ordering is depth-first: given two HierarchyId values a and b, a < b means a comes before b in a depth-first traversal of the tree.

using var dbContext = new CompanyDbContext();
var result = await dbContext.Employees
        .OrderBy(employee => employee.HierarchyId)
        .ToListAsync();

#Get depth level

Use GetLevel() to determine a node's depth in the hierarchy or to filter nodes at a specific level. The root is at level 0.

using var dbContext = new CompanyDbContext();
var employee = await dbContext.Employees.FindAsync(6);

Console.WriteLine(employee.HierarchyId + " Level: " + employee.HierarchyId.GetLevel());
// /1/2/1/ Level: 3

You can also query based on the level:

using var dbContext = new CompanyDbContext();
var employeesOfLevel2 = await dbContext.Employees
        .Where(e => e.HierarchyId.GetLevel() == 2)
        .ToListAsync();

#Get Parent / ancestors

The GetAncestor(n) method returns a HierarchyId representing the n^th ancestor of the current node.

using var dbContext = new CompanyDbContext();
var employee = await dbContext.Employees.FindAsync(2);

var manager = await dbContext.Employees
        .FirstOrDefaultAsync(e => e.HierarchyId == employee.HierarchyId.GetAncestor(1));

You can also query all ancestors at once:

using var dbContext = new CompanyDbContext();
var employee = await dbContext.Employees.FindAsync(6);

var manager = await dbContext.Employees
    .Where(e => employee.HierarchyId.IsDescendantOf(e.HierarchyId))
    .ToListAsync();

#Get children

Using the previous methods, you can query the direct children of a node:

using var dbContext = new CompanyDbContext();
var manager = await dbContext.Employees.FindAsync(2);
var employees = await dbContext.Employees
    .Where(employee => employee.HierarchyId.GetAncestor(1) == manager.HierarchyId)
    .ToListAsync();

#Get descendants

The IsDescendantOf method returns true if the node is a descendant of the given parent. Use it to retrieve all descendants.

using var dbContext = new CompanyDbContext();
var manager = await dbContext.Employees.FindAsync(2);

// Parent is considered its own descendant which means that this query returns the manager (Id = 2)
var result = await dbContext.Employees
        .Where(employee => employee.HierarchyId.IsDescendantOf(manager.HierarchyId))
        .ToListAsync();

To exclude the parent from the results, add an additional filter:

using var dbContext = new CompanyDbContext();
var manager = await dbContext.Employees.FindAsync(2);

// Parent is considered its own descendant which means that this query returns the manager (Id = 2)
var result = await dbContext.Employees
        .Where(employee => employee.HierarchyId.IsDescendantOf(manager.HierarchyId)
                        && employee != manager)
        .ToListAsync();

#Change parent / Move sub-hierarchy

The GetReparentedValue(oldRoot, newRoot) method moves a node within the tree. It returns a new HierarchyId whose path is the path to newRoot followed by the relative path from oldRoot.

using var dbContext = new CompanyDbContext();
var oldManager = await dbContext.Employees.FindAsync(2);
var newManager = await dbContext.Employees.FindAsync(3);

var managees = await dbContext.Employees
        .Where(e => e.HierarchyId != oldManager.HierarchyId
                 && e.HierarchyId.IsDescendantOf(oldManager.HierarchyId))
        .ToListAsync();

foreach (var employee in managees)
{
    employee.OldHierarchyId = employee.HierarchyId;
    employee.HierarchyId = employee.HierarchyId.GetReparentedValue(oldManager.HierarchyId, newManager.HierarchyId);
}

await dbContext.SaveChangesAsync();

Do you have a question or a suggestion about this post? Contact me!