CODEWITHSUNDEEP
entity-frameworkentity-framework-core
Kyle
Kyle

Reputation: 33691

Entity Framework Core hierarchyid

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.

Upvotes: 11

Views: 10307

Answers (3)

Ogglas
Ogglas

Reputation: 69948

Update 2023-02-17:

HierarchyId is now a closed issue for EF Core 8.0.0-preview2. When 8.0.0 is released later this year it will be supported by the EF Core team.

https://github.com/dotnet/efcore/milestone/162?closed=1

https://github.com/dotnet/efcore/commit/a00a518c50da4d7d6be4b215c4fdab7ce5cf6449

https://github.com/efcore/EFCore.SqlServer.HierarchyId/issues/113#event-8549485427

Original:

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

Upvotes: 12

Kyle
Kyle

Reputation: 33691

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.

Upvotes: 2

Greg Gum
Greg Gum

Reputation: 37885

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

Upvotes: 6

Related Questions