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