Differences between SQL Server Hierarchyid and Entity Framework HierarchyId
NickName:HugoRune Ask DateTime:2019-12-12T18:08:35

Differences between SQL Server Hierarchyid and Entity Framework HierarchyId

I am seeing different behavior for the MS SQL Server Datatype hierarchyid, and the corresponding Entity Framework 6 class System.Data.Entity.Hierarchy.HierarchyId (from the assembly EntityFramework.6.3.0\lib\net45\EntityFramework.dll).
(I was unable to find the api doc for this type, but the source code seems to be identical to Microsoft.SqlServer.Types.SqlHierarchyId)

I have a HierarchyId, e.g. childA = '/1/1.1/'. Now I want to get the next HierarchyId on the same level. So I call childA.GetAncestor(1).GetDescendant(childA,null)

Sql server returns '/1/2/', but Entity Framework returns '/1/1.2/'.

So when I insert a node into the database via Entity Framework, it gets a different ID as when using stored SQL procedures or triggers, and when I later want to search for a node by its predicted ID, the search may fail.

Is there a way to get the SQL behaviour in EF, without a database roundtrip?

Example SQL Code:

Declare @ChildA hierarchyid
select @ChildA = '/1/1.1/'
select CAST (@ChildA.GetAncestor(1).GetDescendant(@ChildA,null) as varchar) as ChildB
-- RESULT: ChildB = '/1/2/'

Example C# Code:

var childA = new System.Data.Entity.Hierarchy.HierarchyId("/1/1.1/");
var childB = childA.GetAncestor(1).GetDescendant(childA,null);
Console.WriteLine(childB);
// RESULT: ChildB = "/1/1.2/"

Copyright Notice:Content Author:「HugoRune」,Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/59302122/differences-between-sql-server-hierarchyid-and-entity-framework-hierarchyid

More about “Differences between SQL Server Hierarchyid and Entity Framework HierarchyId” related questions

Differences between SQL Server Hierarchyid and Entity Framework HierarchyId

I am seeing different behavior for the MS SQL Server Datatype hierarchyid, and the corresponding Entity Framework 6 class System.Data.Entity.Hierarchy.HierarchyId (from the assembly EntityFramework...

Show Detail

HierarchyID in Entity Framework not working

We are using WCF Data Service based on an Entity Framework model for our application. In this we need to add the table with a column of type HierarchyId. When I add that table to the EDMX file, the

Show Detail

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

Show Detail

Sort by hierarchyid in SQL Server

I have a hierarchyid column defined on a table in SQL Server 2008 Let us say that in the first row, the hierarchyid is '/1/7/1/' Let us say that in the second row, the hierarchyid is '/1/10/1/' ...

Show Detail

Error with HierarchyId datatype with Entity Framework Core 6 & .NET 6

I am a beginner in programming. I created an ASP.NET Core 6 project using EF Core 6. When I try to reverse-engineer an existing SQL Server 2019 database using Scaffold-DbContext, all domain classes...

Show Detail

SQL 2008 HierarchyID support in NHibernate

Searched various NHibernate lists and haven't come up with a definitive answer. The SQL2008 dialect doesn't appear to have support for the HierarchyID data type - new date and time types only. Does

Show Detail

Entity Framework HierarchyId Workarounds

EF 5.0 I am working on a prototype to test hierarchyid and entity framework together. I have the following schema: Create Table dbo.Employee ( EmployeeId int identity not null, Name nvarcha...

Show Detail

Is there a practical way to use the hierarchyID datatype in entity framework 4?

As it stands now, the CLR UDTs including HierarchyID aren't supported in Entity Framework 4. HierarchyID.ToString() is useful, but breaks down once any item has 10+ siblings (the basic structure i...

Show Detail

Cannot find data type HIERARCHYID in Sql Server 2012

I have win7, sql server 2012 express, .net Framework 3.5 4.0 4.5. I would like to start tutorial about HierarchyId Data Type but when I try to execute this query, this message arrive : Column, par...

Show Detail

Working with hierarchyid in Entity Framework Core

I'm working with EF Core 3.1 and i use sql server datatype hierarchyid on one of my tables. When i scaffold the table into my project the 'hierarchyid' turns into Geometry type. I tried manually

Show Detail