Using SQL Server HierarchyId with Entity Framework Core

The HierarchyId data type was introduced with SQL Server 2008. This type is used to represent and manipulate hierarchical data. Hierarchical data contain the notion of parent/child but also the notion of order between elements having the same parent. Values ​​of this type are stored in a binary form, for example, 0x5AC0 or 0x58, but in practice, we use character strings of the type '/' for the root or '/1/' for a child of the root node, and so on:

It's common to use a column ParentId to represent a hierarchy. While this is easy to create, some operations on the hierarchy could be harder and sometimes slower to do compared to using a HierarchyId. In this post, we'll see how to manipulate a hierarchy using the HierarchyId data type with Entity Framework Core.

If you don't have a local SQL Server to test, you can start a new instance 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 you could use 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 the value of a descendant node that is greater than child1 and less than child2. If you don't need child ordering, you can use null for both children. This post explains with more details how to use children in 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

Comparison is in depth-first order: 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

You can use GetLevel() to determine the level of a node in the hierarchy or to filter the nodes to members of a specified level. The root of the hierarchy is 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 instance.

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 this is a descendant of a parent. You can use this method to get descendant items.

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

If you want to exclude the parent from the result, you can change the filter clause:

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 is used to move an item in the tree. This method returns a node whose path from the root is the path to newRoot, followed by the 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!