Entity Framework Core hierarchyid

Question

I'm trying to use EF Core but I need to use the sql server datatype hierarchyid on one of my tables.

Is it possible to designate a field in my c# class to be of type hierarchyid?

Could I manually column EF creates to be type hierarchyid without breaking EF?

Edit: I have tried to do this:

public class Entity
{
    public int Id { get; set; }
    public string Name { get; set; }
    public SqlHierarchyId HierarchyId { get; set; }
}

As it's suggested that this is supported by EF core 2.1, however when i run add-migration I get this error:

The current CSharpHelper cannot scaffold literals of type 'Microsoft.SqlServer.Types.SqlHierarchyId'. Configure your services to use one that can.

Answer 1

For those looking for the Nuget Package which the SqlHierarchyId package is in, it's in Microsoft.SqlServer.Types.

Note that the package info says this package contains the DbGeography and DbGeometery types, but it also contains the SqlHierarchyId type.

Also note the class is SqlHierarchyId, not HierarchyId (like I was looking for).

Answer 2

If you are using Entity Framework Core 3.1 you should use NuGet EntityFrameworkCore.SqlServer.HierarchyId. Microsoft.SqlServer.Types is not fully supported by .NET Core.

https://www.nuget.org/packages/EntityFrameworkCore.SqlServer.HierarchyId

https://github.com/dotnet/efcore/issues/365#issuecomment-618688829

EntityFrameworkCore.SqlServer.HierarchyId is written by @bricelam that works on the Entity Framework team but a thing to note is that this NuGet is still an unsupported third-party package.

This is still your best bet I think given that it won't be implemented any time soon and perhaps never have a official support. Comment from 2020-04-07:

Basically, it doesn't seem prudent for our small team to implement, support, and maintain a HierarchyId library for .NET right now, so we're effectively blocked on implementing this.

https://github.com/dotnet/efcore/issues/365#issuecomment-610586190

There is also an issue filed in Microsoft SqlClient Data Provider for SQL Server to support SqlHierarchyId type for .NET Core. (Microsoft ADO.NET driver for SQL Server aka the Microsoft.Data.SqlClient GitHub repository)

https://github.com/dotnet/SqlClient/issues/322

Answer 3

There still isn't great native support for this, but as of 11/25/19 the best way I've found is to use the nuget package dotMorten.Microsoft.SqlServer.Types instead of Microsoft.SqlServer.Types since Microsoft.SqlServer.Types is not fully compatible with Core.

Here is a minimal working code sample with dotMorten to generate a tabele with hierarachyId

Note that the using is still Microsoft.SqlServer.Types, dotMortan uses the same namespace to make it a drop in replacement.

using Microsoft.EntityFrameworkCore;
using Microsoft.SqlServer.Types;

namespace ConsoleApp1
{
    public class Db : DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                optionsBuilder.UseSqlServer("Server=(localdb)\\mssqllocaldb;Database=Test;Trusted_Connection=True;MultipleActiveResultSets=true");
            }
        }
        public DbSet<Person> People { get; set; }
    }
    public class Person
    {
        public int Id { get; set; }
        public SqlHierarchyId HId { get; set; }
        public string Name { get; set; }
    }
}

now add-migrations and update-database will work and generate the table propertly, however, something like this:

var peopleDesdenctsOfFirstPerson = db.People.Where(x => x.HId.IsDescendantOf(db.People.First().HId).IsTrue); Still doesn't work and I haven't been able to find a work around for it.