Reverse engineer SqlClient native types: SqlGeometry, SqlGeography, and HierarchyId

[ajcvickers]

See #1100 and #10110. Since these can now be mapped on .NET Framework, it would be good if the scaffolding tool could correctly reverse engineer them. However, note:

• They don't work with .NET Core
• The application must be setup to reference the appropriate assemblies--EF tooling will not do this

Therefore, likely the default behavior should be to not map these types, but warn mentioning an option that can be used to turn on this mapping.

[bricelam]

Design notes

When mapping the column types to CLR types, we can try and load the Microsoft.SqlServer.Types assembly. If the connection string contains Type System Version=SQL Server 2012, we'll try to load version 11.0.0.0; otherwise, we'll try version 10.0.0.0. (This is the SqlClient behavior.) If we can't load it, we won't map the columns.

This requires the user to add the appropriate reference and binding redirects before reverse engineering. Otherwise, these columns won't get scaffolded.

Obviously, the assembly will never load on .NET Core since one isn't available. The warning message should probably be different than on .NET Framework, since there is no way to map them. On .NET Framework, we can suggest installing/referencing the assembly if they want to map these columns.

[ErikEJ]

How will scaffolding know the .net variety? Is it not fw agnostic, basically dropping files in a folder?

[bricelam]

It knows the target framework of the project it's working with.

[bricelam]

But I see your point. Your tool is always running as .NET Framework. Maybe we can provide an internal flag for you to always disable it.

[ErikEJ]

Thanks, Brice! (An option to disable/enable would be great)

[dotMorten]

This does most of the creating and reading the types: https://github.com/dotMorten/Microsoft.SqlServer.Types
(note: No support for the spatial operations)

[ajcvickers]

Note this is about reverse engineering to native types. Compare this to #12083

[stevie6410]

Hi, I am trying to use the EF Core scaffolding tool to target a FileTable in SQL Serer. But it is not able to generate the entity type. Am I missing a package to support the reverse engineering of the HierarchyId type? Or is this just not supported in the scaffolding tool?

Error message from the dotnet ef dbcontext scaffold call.

Could not scaffold the primary key for 'dbo.DocumentStoreFiles'. The following columns in the primary key could not be scaffolded: path_locator. Unable to generate entity type for table 'dbo.DocumentStoreFiles'.
The 'path_locator' is of type HierarchyId.

[bricelam]

Not yet supported. This issue is tracking the work to enable it

[ajcvickers]

Closing old issue as this is no longer something we intend to implement. EF Core 5 will not support .NET Framework and there is no support for SQL Server native spatial types in .NET Core.

[GSPP]

@ajcvickers What is the recommended way for customers to be able to use spatial with EF at this point?

[ajcvickers]

@GSPP See the docs