Reverse Engineer - Hierarchy ID

[anthonymachniak]

Hi Erik,

While not specifically an issue, is there a plan to have the reverse engineer tool map HierarchyId columns to the EntityFrameworkCore.SqlServer.HierarchyId class? The last post I saw was from a few months ago, and it was mentioned it was supported, but it still isn't mapping on my system. My version of the HierarchyId library may be too old to utilize that fix though. We plan on upgrading everything to .NET 6 within the next year, so if it's just a matter of being on an old version, I can work with that.

https://github.com/efcore/EFCore.SqlServer.HierarchyId/issues/52

Further technical details

EF Core version: 3.1.14 EntityFrameworkCore.SqlServer.HierarchyId version: 1.2.0 CS Project version: .NET Standard 2.0.3 EF Core Power Tools version: 2.5.877 Database engine: SQL Server 2019 Visual Studio version: Visual Studio 2019 Enterprise 16.11.7

Thank you for all of your hard work with this!

[ErikEJ]

Should be supported for 3.1 in the latest daily build - Remember to enable the advanced option.

[anthonymachniak]

Thank you! I see the new option there and ran it, but it is still mapping to the geometry data type with the daily build (v 2.5.893).

[ErikEJ]

??? What is mapping to geometry??

[anthonymachniak]

We have a table that has a sys.HierarchyId (sql server 2019) type that is getting reverse-engineered as the NetTypologies geometry type.

[ajcvickers]

@anthonymachniak Could be related to this: https://github.com/efcore/EFCore.SqlServer.HierarchyId/issues/86

Make sure you have the most recent patch version of the package.

[ErikEJ]

@ajcvickers I am using the latest, has it been updated for 3.1 ?

Hmm... Sounds buggy - can you share a CREATE TABLE statement so I can investigate.

What happens with the current release from marketplace?

[ajcvickers]

Missed that this is for 3.1. Don't know, in that case/

[anthonymachniak]

The create isn't anything fancy, just create [table_name] [hierarchyid] not null.

We are on the latest version supported by .NET Standard 2.0 since we're transitioning from Framework to Core. If the issue is that my version of HierarchyId is old, I'll just look forward to this working in a later version once we finish our upgrade and I appreciate you looking into it!

[ErikEJ]

@anthonymachniak Could you do a test run with EF Core 6 selected?

[anthonymachniak]

Sure. I did a test upgrade from .NET Standard to .NET 6, updated the nuget packages to the latest version, and it seems like HierarchyId still got mapped with the geometry object type.

This ran with EntityFrameworkCore.SqlServer.HierarchyId v3.0.1, and Microsoft.EFCore libraries v6.0.1. I just noticed that doing that update added the GeoAPI.Core package (probably some EF dependency), but that might be having an impact too.

[ErikEJ]

Just FYI, EF Core Power Tools does not use your project or its references.

[anthonymachniak]

Interesting, so is it all based on how the DB table is setup?

[ErikEJ]

And the options you choose in Power Tools, yes.

[ErikEJ]

I am unable to repro - (you never supplied a proper repro...):

CREATE TABLE Specials
(
    Id INT PRIMARY KEY NOT NULL,
    Test1 hierarchyid NOT NULL,
    Test2 geometry NOT NULL,
    Test3 geography NOT NULL
)

with the latest daily (build 894) using EF Core 6 and the advancd option "Map spatial types & hierarchyid" selected, I get this class generated:

using System;
using System.Collections.Generic;
using Microsoft.EntityFrameworkCore;
using NetTopologySuite.Geometries;

namespace ConsoleApp.Models
{
    public partial class Special
    {
        public int Id { get; set; }
        public HierarchyId Test1 { get; set; }
        public Geometry Test2 { get; set; }
        public Geometry Test3 { get; set; }
    }
}

[anthonymachniak]

Thanks for all of your effort with this, and sorry I dropped off for a few months. One thing I noticed that is different about our test cases are that the HierarchyID on my table is the primary key. When I have just the "use hierarchy ID" setting selected, it maps it correctly, but then our other tables that have geometry types are converting to the HierarchyID. When I have both spatial types & hierarchy ID enabled, it's picking geometry for the PK HierarchyID. I hope that helps!

[ErikEJ]

@anthonymachniak Please provide a repro!

[anthonymachniak]

Sample Table: CREATE TABLE dbo.POWER_TOOLS ( POWER_TOOLS_HIERARCHY_ID HIERARCHYID NOT NULL PRIMARY KEY, NAME VARCHAR(500) NULL, )

Run power tools (with "use spatial types" & "use hierarchy ID" checked)

// This file has been auto generated by EF Core Power Tools. using System; using System.Collections.Generic; using NetTopologySuite.Geometries;

namespace QVF.Data.EFCore.SQL.Models { public partial class POWER_TOOLS { public Geometry POWER_TOOLS_HIERARCHY_ID { get; set; } public string NAME { get; set; } } }

[ErikEJ]

Which EF Core version?

[anthonymachniak]

EF Core: 3.1.14 EFCore.SqlServer.HierarchyId: 1.2.0 GeoAPI.Core: 1.7.5

[ErikEJ]

as metioned above, this might be a bug - what if you select to use EF Core 6?

[anthonymachniak]

It's not an exact replica since we have to maintain our current version for the time being, but when I created a test project with .NET 6 and using EF Core 6.0.3 it still mapped the HierarchyID as a geometry type.

[ErikEJ]

OK, so please please share the test project, and I will have a closer look.

[anthonymachniak]

I think I got it. There's the EF Core version you have in the nuget package, and then the EF Core version you select when declaring the connection in the Power Tools. I had updated the test project packages to use EF Core 6.0.3, but the Power Tools was still running with EF Core 3 selected (I copied/pasted the Power Tools config file when doing the test). When I selected EF Core 5 or EF Core 6, the Hierarchy ID got mapped correctly, but when it ran with EF Core 3 being selected (even though EF Core 6 is what was in the project reference), I got the same error.

[ErikEJ]

OK, as noted above this is most likely an issue that has been fixed in the .NET 5 and 6 versions of the packages.