SQL Server: Support hierarchyid

[mojtabakaviani]

We are using this issue to cover specifically end-to-end support for hierarchyid. SQL Server UDTs are covered by https://github.com/aspnet/EntityFrameworkCore/issues/12796, and spatial is covered by https://github.com/aspnet/EntityFrameworkCore/issues/1100.

As noted in https://github.com/aspnet/EntityFrameworkCore/issues/365#issuecomment-338293994, we already support mapping a property of this type where the type is available.

Here is what is still missing before we can say we support hierarchyid:

• Currently the type is only available in .NET Framework (mostly because Microsoft.SqlServer.Types hasn't been ported)
• Presumably we should support usage of members of the type, functions, etc. in queries

Original issue

sql server special data types add in EF5 and later but hierarchyid and user defined types not implemented yet.please work on hierarchyid and user defined types that use in real website and enterprise.

Note by @rowanmiller: Also relevant - [C# Feature Request] Hierarchy Data Type #16193

[rowanmiller]

This is definitely a scenario we care about in EF Core. We are going to support general type conversion (i.e. you can specify how to convert types from the database to/from CLR types). We'll also have some mechanism for being able to query using database specific operations (somewhat akin to SqlFunctions from past versions of EF).

I'm moving this to Backlog given we may not get everything I mentioned working in the initial RTM of EF Core.

[excalidev]

Have you someting planned yet to implement hierarchyid in EF Core? Looking forward for your thoughts on this topic, thanks.

[vRITHNER]

Hey Rowan, just to add my 2 ct, hope you'll support HierarchyId asap because it's really a great way to manage hierarchy and other manual ways demand so much effort in code. Also, it could be great to support natively the GROUP BY xxxx WITH ROLLUP to allow to calculate par ex. Totals per group/subgroup/etc... witch make a huge save code effort + offcourse performance improvement Txs -Vince

[tomagnew]

Please add native support for hierarchyID to EF Core so we can avoid work-arounds (like sprocs which can defeat the effectiveness of a good ORM) to use this nice feature of Sql Server 2008 and greater. IMHO, hierarchyId is the most efficient way to work with hierarchical data which helps reduce the impedance between normalized table data and nested data.

[leus]

Don't know if this is proper protocol, but I'd like hierarchyid gets some love too - too many times in my career I had to create tables with "parent_id" and stupidly complex recursive queries just to draw a tree (and just now I'm doing it again)

[nicbourgoin]

Any guidance on where I should start looking for to try and implement this? I definitly need hierarchyid from Microsoft.SqlServer.Types to be supported.

[nteague22]

HierarchyId might be work tackling sooner than later, if for no other reason than doing the Reference<>() definitions and Collection<>() definitions in the IModel. Parenting chains within a table could greatly make steps to reduce the separation I have had with using the outstanding features of the system, while juggling the inability to do extensive business and enterprise processing

[weitzhandler]

Any news? See this issue too.

[activebiz]

Any update on this ?

[ravetroll]

I'd like the upvote this too. #365 was opened in 2014 and its now 2017. Hierarchical models are commonplace and the SQL hierarchy type does greatly improve performance on deep tree requests over the iterative parent/child approach.

[activebiz]

[Rant warning :)] I think the whole EF stuff is too much to bring small gain on writing efficient code. I have been using EF since 4x days but it always have issues with either lack of support for certain types, performance etc. Its great ORM (dont get me wrong) and might be ok for certain projects but I think that no Full-ORM is a match for good old ADO.NET. I have recently backed out from EF for reason mentioned above and ofcourse the performance. I think using full flege ORM will always have issues becouse its in some sense EF is making decistions for you. For e.g. creating queries etc. If I have to see SQL Profiler after writing my Linq-EF queries everytime then its defeating the purpose of writing efficient code fast.

I think EF should offer another version which is Mirco ORM such as Dapper. This will elimiates lot of these issues with not supproting this type and that type and writing/profiling queries etc.

@ravetroll . I would look at Dapper or PetaPoco as another alternative to EF. Dapper does support HierarcyId if you are using Full .NET framework. (its sort of .NET core limitations of not supporing hierarcyId for .NET Core project).

Sorry to be so much negative about this but I have waited for EF to bring HierarchyId for so long. (even 6x doesnt support it out of box , you have to use external package for that). Just shaing my pain.

[weitzhandler]

@activebiz I've been upgrading along with EF until it came to EFCore. And I'll upgrade to core as soon as complex-types and TPT are implemented. I do agree with you that it could be nice to see some portions of EF are extract to independent libs, such as change-tracking etc., but EF is still advancing and it only gets better over time.

[joshcomley]

Does anybody have any examples of HierarchyId on EF Core?

[activebiz]

@joshcomley It doesnt support simply becouse dotnet core dont support. So dont hold your breath! If you want you can use EF6 and there is a nuget package which you have to use.

https://www.nuget.org/packages/EntityFrameworkWithHierarchyId/

[joshcomley]

@activebiz isn't is a case of building in the support into EF Core? I've checked out that package and it should be possible to port the feature to EF Core, with the right know-how

[activebiz]

@joshcomley Its possible if dotnet core support HierarchyId which it doesnt at the moment.

[ajcvickers]

Just an FYI to this thread that mapping of HierarchyId (as well as SqlGeography and SqlGeometry) is now supported (and will be in 2.1), but with some significant limitations:

• There is no attempt by EF Core to use the semantics of the HierarchyId for mapping or anything else. It is just an opaque object as far as EF is concerned.
• It only works when running on .NET Framework. It will not work on .NET Core, since SqlClient for .NET Core does not support these types.
• Query support is limited:
  • LINQ queries that don't use the semantics of the SQL types should work.
  • Queries that need to use the type semantics (e.g. calling functions on the HierarchyId object) can be done with FromSql, but not LINQ
  • See issue #10108 for some test code and issue #10109 for some ideas to make LINQ work better

[olmobrutall]

I've just made a pull request adding support for SqlHierarchyId in dotMorten's Microsoft.SqlServer.Types repository.

When merged, the repository will have a .Net Standard implementation of all the important types in Microsoft.SqlServer.Types:

• SqlHierarchyId
• SqlGeometry
• SqlGeography

Maybe could be an possibility for EntityFrameworkCore and/or SQL Server team?

[bricelam]

@olmobrutall It should be trivial to create a package like our NetTopologySuite ones that allow these types to be mapped and translated.

One of the reasons we didn't take this approach is because we wanted to enable using the same CLR types (the NetTopologySite spatial types) across multiple providers (e.g. SQL Server, SQLite and PostgreSQL). Encouraging everyone to use the SQL Server types, or only implementing spatial support on SQL Server seemed a bit ...well Microsoft circa 1998. 😉

[bricelam]

I looked on NuGet.org for popular type that exposed this functionality on .NET Standard (similar to how NTS enabled us to implement spatial), but couldn't find one. I think we can consider ourselves blocked on this until one emerges or the official Microsoft.SqlServer.Types package is updated to target .NET Standard.

Technically speaking, we (or someone else) could create a package that only works on .NET Framework (or uses the unofficial package), but this doesn't align with our strategy for EF Core.

[ErikEJ]

@dotmorten ?

[dotMorten]

I have a SQL hiarchy implementation that matches the SQL types here: https://github.com/dotMorten/Microsoft.SqlServer.Types/tree/master/src/Microsoft.SqlServer.Types/SqlHierarchy

[bricelam]

Have you published a NuGet package? If so I’ll throw something together during my moonlit hours.

[dotMorten]

Sorry I've been procrastinating. I'll do it tonight

[bricelam]

Looks like @olmobrutall published a package. I'm starting something in bricelam/EFCore.SqlServer.HierarchyId

[dotMorten]

It was published without consent and because I was procrastinating 😁 I'll have a proper one up with source linking and correctly code signed.

[bricelam]

Wow these types are awful. The operator overloads return SqlBoolean forcing you to write LINQ like this:

var root = from t in db.Things
           where (t.HierarchyId == SqlHierarchyId.GetRoot()).Value
           select t;

We probably need a more .NET-idiomatic wrapper type to make the EF Core experience acceptable.

[dotMorten]

These are an exact match on the sqltypes API. Take it up with Microsoft. It's an API geared towards working with SQL server.

[olmobrutall]

Also the IsNull is quite odd, the internal HierarchyId doesn’t have this oddities but then you need a complex translation to SQL. Like this is just a matter of blindly write every method or property with SqlMethodAttribute

[dotMorten]

This is all just how UDT types work in SQL. Not sure what the point of ranting against that is, unless you hope to change how SQL Server works?

[olmobrutall]

Exactly

[dotMorten]

Fixed several bugs and inconsistencies with the official Sql Types library and uploaded to NuGet: https://www.nuget.org/packages/dotMorten.Microsoft.SqlServer.Types/1.0.0

[bricelam]

No ranting here—just remembering why we had to introduce DbGeography in EF6.

I’m also disappointed that fixing this issue isn’t as trivial as I first thought it would be.

[bricelam]

I suspect all of the awkwardness stems from the three-valued logic of SQL (True/False/Unknown)

[dotMorten]

Now I can't pretend I know much about EF, but I don't understand the big surprise about this. It's not really any different than working with all the built-in SqlTypes. Wouldn't you just do it all the same way?

[dotMorten]

FYI OMD of the library here: https://sharpgis.net/omds/dotMorten.Microsoft.SqlServer.Types.html And here's a list of the API's the official package has, that no .NET Standard implementation is provided for (yet): https://sharpgis.net/omds/dotMorten.Microsoft.SqlServer.Types-missing.html

[jmounce]

I'm having trouble using SqlHierarchyId in an EFCore project.

Hey, @dotMorten - I've seen your posts and tried your lib. https://github.com/dotMorten/Microsoft.SqlServer.Types For writes to the DB and DB generation, it seems to work fine.

But when I read, I get this error: FileLoadException: Could not load file or assembly 'Microsoft.SqlServer.Types, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040) System.Reflection.RuntimeAssembly.nLoad(AssemblyName fileName, string codeBase, RuntimeAssembly locationHint, ref StackCrawlMark stackMark, IntPtr pPrivHostBinder, bool throwOnFileNotFound, IntPtr ptrLoadContextBinder) System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, RuntimeAssembly reqAssembly, ref StackCrawlMark stackMark, IntPtr pPrivHostBinder, bool throwOnFileNotFound, IntPtr ptrLoadContextBinder) System.Reflection.Assembly.Load(AssemblyName assemblyRef) System.Data.SqlClient.SqlConnection.ResolveTypeAssembly(AssemblyName asmRef, bool throwOnError) System.Data.SqlClient.SqlConnection+<>c__DisplayClass128_0.<CheckGetExtendedUDTInfo>b__0(AssemblyName asmRef) System.TypeNameParser.ResolveAssembly(string asmName, Func<AssemblyName, Assembly> assemblyResolver, bool throwOnError, ref StackCrawlMark stackMark) System.TypeNameParser.ConstructType(Func<AssemblyName, Assembly> assemblyResolver, Func<Assembly, string, bool, Type> typeResolver, bool throwOnError, bool ignoreCase, ref StackCrawlMark stackMark) System.TypeNameParser.GetType(string typeName, Func<AssemblyName, Assembly> assemblyResolver, Func<Assembly, string, bool, Type> typeResolver, bool throwOnError, bool ignoreCase, ref StackCrawlMark stackMark) System.Type.GetType(string typeName, Func<AssemblyName, Assembly> assemblyResolver, Func<Assembly, string, bool, Type> typeResolver, bool throwOnError) System.Data.SqlClient.SqlConnection.CheckGetExtendedUDTInfo(SqlMetaDataPriv metaData, bool fThrow) System.Data.SqlClient.SqlDataReader.GetValueFromSqlBufferInternal(SqlBuffer data, _SqlMetaData metaData) System.Data.SqlClient.SqlDataReader.GetFieldValueFromSqlBufferInternal<T>(SqlBuffer data, _SqlMetaData metaData) System.Data.SqlClient.SqlDataReader.GetFieldValueInternal<T>(int i) System.Data.SqlClient.SqlDataReader.GetFieldValue<T>(int i) lambda_method(Closure , DbDataReader )

I know that Microsoft.SqlServer.Types doesn't support .NET Standard so your lib looked attractive, but why is the runtime looking for a specific version of this assembly?

I see in the EF6 code that it is specific: https://github.com/aspnet/EntityFramework6/blob/master/src/EntityFramework.SqlServer/SqlTypesAssemblyLoader.cs#L16

but I don't see similar, specific code in EF Core. Any idea what is going on here or how to fix this? (other than MS porting to standard - which isn't open source...??)

[olmobrutall]

Hi @jmounce,

Check https://github.com/dotMorten/Microsoft.SqlServer.Types/blob/master/src/Microsoft.SqlServer.Types.Tests/DBTests.cs

And search for reader.GetFieldValue<SqlHierarchyId>, maybe it helps.

[dotMorten]

I actually just made a fix for that last night by extracting / faking the strong name key from the original assembly. I haven't updated the nuget yet but you can use the approach mentioned above until then. Details wrt the issue was described here: https://github.com/dotMorten/Microsoft.SqlServer.Types/issues/1

[jmounce]

I compiled and used your v1.1 but it didn't work for me. Same error. I have to find out if it is just me or not. Your tests that @olmobrutall referred me to don't use EF, so that doesn't tell me much. I will have to modify your tests and see if it also fails there.

[olmobrutall]

There is not support for SqlHierarchyId in EF Core yet. Whether they will implement it based on the version that I contributed to dotMorten repo, or wait for an official port, is something EF devs have to answer.

About base ADO.Net support, I'm also getting your exception now ...strange. Looks like GetFieldValue<T>(ordinal) is just (T)GetValue(ordinal), so the type resolution is on ADO.Net. I've been bitten many times by Assembly resolution conflicts in Microsoft.SqlServer.Types before (in .Net Framework) so I've changed the implementation now to instantiate the object myself (with Activator.CreateInstance), cast it to IBinarySerializable and read the cell Stream.

I've solved it like this in my ORM: https://github.com/signumsoftware/framework/commit/1cf75072b5e823528bcddc931e6cf637aa1ce6ea, maybe is useful for a future EF implementation.

[dotMorten]

@jmounce I'm now finding that my signature key hack doesn't work on .NET Framework (it works on .NET Core and Mono). I think @bricelam has a way to hack it more, but I couldn't get it to work.

If you're running on .NET Framework though, you could just use the official package where these types are already supported.

[jmounce]

@dotMorten I'm running on .NET Core 2.1. It's strange. I don't always get this error for all projects. For example, if I add EF support to your test project, it works fine. However, for any WebApi Core projects I create, I get the error. It must be a dependency in ASP.NET Core->EF Core. I just can't determine the cause yet.

[dotMorten]

Note: Some pretty serious bugs were reported in the SqlTypes nuget over the weekend and I fixed them all and updated to v1.1.0 and unlisted 1.0.0 (apart from some minor issues, none of these were hierarchy specific though, but they could create data corruption for the geometry types)

[kyletraynoreliant]

I'm confused - in some places its stated that EF Core is supporting sqlhierarchyid and some places says it does not.

What's the current status on this?

I'm using EF core 2.2 preview 3 and I get:

The type mapping for 'SqlHierarchyId' has not implemented code literal generation.

Is this supported or still being worked on? It's a major hindrance for me.

Is there a current block or next step that needs to be completed? I'd love to contribute.

[bricelam]

I started working on something in bricelam/EFCore.SqlServer.HierarchyId, but had to put it aside for while. I think the only part still missing is adding a type mapping similar to SqlServerGeometryTypeMapping. Feel free to pick up where I left off.

[babak-f]

Until we get some sort of support for hierarchy ID in EF Core, would it be possible to introduce it as a string property in code-first, and map it to a hierarchyid column in the database? (as a potential workaround)

[dotMorten]

@babak-f my library has hierarchy id support : https://github.com/dotMorten/Microsoft.SqlServer.Types

[aljones]

I added a type mapping for HierarchyId based on @bricelam helpful pointer here. I'm unfamiliar with these APIs so I may have missed something. Got some tests passing at least.

[huoyaoyuan]

@aljones Happy to see some "just work" solutions. Your implementation works with 2.2 but not 3.0, since IDbContextOptionsExtensionWithDebugInfo has changed. Creating migrations with dotnet ef works, but running that migration results in NRE.Adding [Column(TypeName = "hierarchyid")] makes it work! Querying data with relationship works.

I think we should wait Microsoft.Data.SqlClient to be opened, then use it for further official support.