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 #12796, and spatial is covered by #1100.

As noted in #365 (comment), 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 Figure out testing for EF use of Microsoft.SqlServer.Types #10108 for some test code and issue Better LINQ support for Microsoft.SqlServer.Types #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.

[Ogglas]

@bricelam @vyrotek Official support would still be appreciated :)

[bricelam]

For sure. We're still waiting on the SQL Server team to release a version of Microsoft.SqlServer.Types that works cross-platform on .NET 6+. Last I heard, they had a plan and were actively working on it.

[IT-CASADO]

Any news about official support?

[bricelam]

This is finally unblocked. Recent previews of the Microsoft.SqlServer.Types package support modern .NET and its hiererchyid type works cross-platform.

[mojtabakaviani]

Thanks everyone! 👍

[marchy]

What is the Sqlite support for this on EF8?

Curious if it's worthwhile updating our code to support the new EF8 hierarchy ID abstraction over simply doing string checks for ancestors on a hierarchical-by-convention ID (ie: Ancestor1/Ancestor2/Node) as we currently do.

[ajcvickers]

@marchy SQLite does not natively support HierarchyId, and we don't plan to add anything to fake it in EF Core.

[roji]

@marchy assuming you're using SQLite as a fake database for testing (with the real database being SQL Server), this is a good example of the limitations of that testing approach. We recommend using e.g. testcontainers.net to effortlessly spin up a real, containerized SQL Server database in your tests, and test against that.