Microsoft.SqlServer.Types with Microsoft.Data.SqlClient

[Zyano]

Hey,

Has anyone managed to get Microsoft.SqlServer.Types working with the modified provider?

Specifically DbGeography. Reading data from the database works as expected correctly, but when I try to perform an update the program crashes with what appears to be a mapping issue between a Microsoft.SqlServer.Types.SqlGeography and MetaType.

Stack trace:

System.Data.Entity.Infrastructure.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.Entity.Core.UpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.Entity.Core.EntityCommandCompilationException: An error occurred while preparing the command definition. See the inner exception for details. ---> System.ArgumentException: No mapping exists from object type Microsoft.SqlServer.Types.SqlGeography to a known managed provider native type.
   at Microsoft.Data.SqlClient.MetaType.GetMetaTypeFromValue(Type dataType, Object value, Boolean inferLen, Boolean streamAllowed) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\src\Microsoft\Data\SqlClient\SqlEnums.cs:line 395
   at Microsoft.Data.SqlClient.MetaType.GetMetaTypeFromType(Type dataType) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\src\Microsoft\Data\SqlClient\SqlEnums.cs:line 287
   at Microsoft.Data.SqlClient.SqlParameter.GetMetaTypeOnly() in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlParameter.cs:line 1834
   at Microsoft.Data.SqlClient.SqlParameter.get_SqlDbType() in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlParameter.cs:line 573
   at System.Data.Entity.SqlServer.MicrosoftSqlProviderServices.CreateSqlParameter(String name, TypeUsage type, ParameterMode mode, Object value, Boolean preventTruncation, SqlVersion version) in /_/src/ErikEJ.EntityFramework.SqlServer/MicrosoftSqlProviderServices.cs:line 562
   at System.Data.Entity.SqlServer.SqlGen.DmlSqlGenerator.ExpressionTranslator.CreateParameter(Object value, TypeUsage type, String name) in /_/src/ErikEJ.EntityFramework.SqlServer/SqlGen/DmlSqlGenerator.cs:line 599
   at System.Data.Entity.SqlServer.SqlGen.DmlSqlGenerator.ExpressionTranslator.Visit(DbConstantExpression expression) in /_/src/ErikEJ.EntityFramework.SqlServer/SqlGen/DmlSqlGenerator.cs:line 693
   at System.Data.Entity.Core.Common.CommandTrees.DbConstantExpression.Accept(DbExpressionVisitor visitor)
   at System.Data.Entity.SqlServer.SqlGen.DmlSqlGenerator.GenerateUpdateSql(DbUpdateCommandTree tree, SqlGenerator sqlGenerator, List`1& parameters, Boolean generateReturningSql, Boolean upperCaseKeywords) in /_/src/ErikEJ.EntityFramework.SqlServer/SqlGen/DmlSqlGenerator.cs:line 68
   at System.Data.Entity.SqlServer.SqlGen.SqlGenerator.GenerateSql(DbCommandTree tree, SqlVersion sqlVersion, List`1& parameters, CommandType& commandType, HashSet`1& paramsToForceNonUnicode) in /_/src/ErikEJ.EntityFramework.SqlServer/SqlGen/SqlGenerator.cs:line 380
   at System.Data.Entity.SqlServer.MicrosoftSqlProviderServices.CreateCommand(DbProviderManifest providerManifest, DbCommandTree commandTree) in /_/src/ErikEJ.EntityFramework.SqlServer/MicrosoftSqlProviderServices.cs:line 280
   at System.Data.Entity.SqlServer.MicrosoftSqlProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree) in /_/src/ErikEJ.EntityFramework.SqlServer/MicrosoftSqlProviderServices.cs:line 209
   at System.Data.Entity.Core.Common.DbProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Core.Common.DbProviderServices.CreateCommandDefinition(DbCommandTree commandTree, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Core.Common.DbProviderServices.CreateCommand(DbCommandTree commandTree, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.CreateCommand(DbModificationCommandTree commandTree)
   --- End of inner exception stack trace ---
   at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.CreateCommand(DbModificationCommandTree commandTree)
   at System.Data.Entity.Core.Mapping.Update.Internal.DynamicUpdateCommand.CreateCommand(Dictionary`2 identifierValues)
   at System.Data.Entity.Core.Mapping.Update.Internal.DynamicUpdateCommand.Execute(Dictionary`2 identifierValues, List`1 generatedValues)
   at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update()
   --- End of inner exception stack trace ---
   at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update()
   at System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.<>c.<Update>b__21_0(UpdateTranslator ut)
   at System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.Update[T](T noChangesResult, Func`2 updateFunction)
   at System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.Update()
   at System.Data.Entity.Core.Objects.ObjectContext.<SaveChangesToStore>b__153_0()
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
   at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesToStore(SaveOptions options, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction)
   at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass148_0.<SaveChangesInternal>b__0()
   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation) in /_/src/ErikEJ.EntityFramework.SqlServer/DefaultSqlExecutionStrategy.cs:line 52
   at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesInternal(SaveOptions options, Boolean executeInExistingTransaction)
   at System.Data.Entity.Core.Objects.ObjectContext.SaveChanges(SaveOptions options)
   at System.Data.Entity.Internal.InternalContext.SaveChanges()
   --- End of inner exception stack trace ---
   at System.Data.Entity.Internal.InternalContext.SaveChanges()
   at System.Data.Entity.Internal.LazyInternalContext.SaveChanges()
   at System.Data.Entity.DbContext.SaveChanges()

Inner exception stack trace:

   at Microsoft.Data.SqlClient.MetaType.GetMetaTypeFromValue(Type dataType, Object value, Boolean inferLen, Boolean streamAllowed) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\src\Microsoft\Data\SqlClient\SqlEnums.cs:line 395
   at Microsoft.Data.SqlClient.MetaType.GetMetaTypeFromType(Type dataType) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\src\Microsoft\Data\SqlClient\SqlEnums.cs:line 287
   at Microsoft.Data.SqlClient.SqlParameter.GetMetaTypeOnly() in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlParameter.cs:line 1834
   at Microsoft.Data.SqlClient.SqlParameter.get_SqlDbType() in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlParameter.cs:line 573
   at System.Data.Entity.SqlServer.MicrosoftSqlProviderServices.CreateSqlParameter(String name, TypeUsage type, ParameterMode mode, Object value, Boolean preventTruncation, SqlVersion version) in /_/src/ErikEJ.EntityFramework.SqlServer/MicrosoftSqlProviderServices.cs:line 562
   at System.Data.Entity.SqlServer.SqlGen.DmlSqlGenerator.ExpressionTranslator.CreateParameter(Object value, TypeUsage type, String name) in /_/src/ErikEJ.EntityFramework.SqlServer/SqlGen/DmlSqlGenerator.cs:line 599
   at System.Data.Entity.SqlServer.SqlGen.DmlSqlGenerator.ExpressionTranslator.Visit(DbConstantExpression expression) in /_/src/ErikEJ.EntityFramework.SqlServer/SqlGen/DmlSqlGenerator.cs:line 693
   at System.Data.Entity.Core.Common.CommandTrees.DbConstantExpression.Accept(DbExpressionVisitor visitor)
   at System.Data.Entity.SqlServer.SqlGen.DmlSqlGenerator.GenerateUpdateSql(DbUpdateCommandTree tree, SqlGenerator sqlGenerator, List`1& parameters, Boolean generateReturningSql, Boolean upperCaseKeywords) in /_/src/ErikEJ.EntityFramework.SqlServer/SqlGen/DmlSqlGenerator.cs:line 68
   at System.Data.Entity.SqlServer.SqlGen.SqlGenerator.GenerateSql(DbCommandTree tree, SqlVersion sqlVersion, List`1& parameters, CommandType& commandType, HashSet`1& paramsToForceNonUnicode) in /_/src/ErikEJ.EntityFramework.SqlServer/SqlGen/SqlGenerator.cs:line 380
   at System.Data.Entity.SqlServer.MicrosoftSqlProviderServices.CreateCommand(DbProviderManifest providerManifest, DbCommandTree commandTree) in /_/src/ErikEJ.EntityFramework.SqlServer/MicrosoftSqlProviderServices.cs:line 280
   at System.Data.Entity.SqlServer.MicrosoftSqlProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree) in /_/src/ErikEJ.EntityFramework.SqlServer/MicrosoftSqlProviderServices.cs:line 209
   at System.Data.Entity.Core.Common.DbProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Core.Common.DbProviderServices.CreateCommandDefinition(DbCommandTree commandTree, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Core.Common.DbProviderServices.CreateCommand(DbCommandTree commandTree, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.CreateCommand(DbModificationCommandTree commandTree)

Message: No mapping exists from object type Microsoft.SqlServer.Types.SqlGeography to a known managed provider native type.

The EF model I'm trying to update looks like this.

using System;
using System.Data.Entity.Spatial;

namespace Model.General
{
    public class Institution 
    {        
    // Removed all other fields for visibility.
        public DbGeography Location { get; set; }
    }
}

The model project containing the DbContext and database models are being compiled as netstandard2.1 and net472 being run under Windows.

Any ideas are very welcome.

[ErikEJ]

Please share a complete repro project, and I will have a look.

[Zyano]

Sure I will look at making a basic implementation and create repo for it. I expect to have it ready tomorrow.

[Zyano]

I've created the repository with the code that resembles the setup we use without a lot of the boilerplate. url: https://github.com/Zyano/EF6-MSSQL

I hope it helps.

[Zyano]

I did a lot of digging into how EF and Microsoft.Data.SqlClient actually determines how to perform UDT mapping and it appears the problem is relatively simple.

The provider (Microsoft.Data.SqlClient) will perform a check for "user defined type" by calling.

SqlUdtInfo.TryGetFromType which checks the target type SqlGeography in this case and if no attribute of type Microsoft.Data.SqlClient.Server.SqlUserDefinedTypeAttribute is found then no mapping can be formed.

The issue comes from the fact that Microsoft.SqlServer.Types uses the attribute from Microsoft.SqlServer.Server.SqlUserDefinedTypeAttribute

This all seems to relate to the issue being discussed here https://github.com/dotnet/runtime/issues/66531 where the problem with bringing forward SqlServer types will require updates to the System.Data.SqlClient, the creation of a new package and the update of Microsoft.Data.SqlClient.

All in all the only fix I can see is using the approach that DotMorten did with creating a new version of Microsoft.SqlServer.Types.

[ErikEJ]

Wonder if it is possible to use the @dotMorten library like described here ?

[Zyano]

I decided to give that approach a try by forking dotMorten's repo updating the version to 15 for EF to load it. Then I ran into an issue where the provider expects a specific publicKey see SqlTypesAssemblyLoader:16.

So I forked your repo removing the constraint for a specific publicKey giving me a load (success!)

Next obstacle is hit shortly there after when setting up the spatialAssembly (see SqlTypesAssembly.SqlTypesAssembly problem being that dotMortens version doesn't implement all the functionality of Microsoft.SqlServer.Types.

I'm not entirely sure how fare down this path I should continue as it's becoming very clear that this will require a lot of custom assemblies to get this working under Microsoft.Data.SqlClient.

[ErikEJ]

Maybe the conculsion is that support for using this provider with DbGeography, DbGeometry and HierarchyId is not supported? @ajcvickers @bricelam ?

[ajcvickers]

We will look into it when doing official support for Microsoft.Sql.ServerClient in EF6, but it may end up not being supported until Microsoft.SqlServer.Types is officially supported on modern .NET.

[ErikEJ]

@ajcvickers Thanks, will add to the docs for now!

[dotMorten]

it may end up not being supported until Microsoft.SqlServer.Types is officially supported on modern .NET.

@ajcvickers are there plans for this finally so I ultimately can stop maintaining my version?

[ErikEJ]

@dotMorten Work is in progress, but dont hold your breath

[ErikEJ]

Updated know issues section in readme.md

[bricelam]

dont hold your breath

lol, I'm a bit more optimistic than that. After eight years of asking them for this, they actually started writing some code...

[Zyano]

Figured I would give a small update on how we ended up "solving" the issue and why we wanted to switch over in the first place.

We are trying to move towards .NET 6 and the newer .NET technologies in general. As part of that journey we noticed that System.Data.SqlClient doesn't support 'Always Encrypted' (a feature we are required to use). So in order to solve the problem the decision was made to upgrade from System.Data.SqlClient to Microsoft.Data.SqlClient.

As outlined in earlier comments. We went with ErikEJs modified provider to solve this issue which was working great until we hit these custom UDT types in the form of SqlGeography. I tried quite a few different approaches to solving it while maintaining the same data type without success. In the end we decided to convert all SqlGeography to varbinary fields because we don't actually use the functionality provided by SqlGeography when querying the database. Instead the application uses the longitude and latitude for plotting information on google maps and calculating route information.

This approach is not going to work for everyone, but hopefully it can give others that might run into this some ideas for how to handle it.

Thanks to all of you for taking to time to comment and help. I hope in the future the Microsoft.SqlServer.Types will have native support on never .NET technologies so migration of existing solutions will be a little easier :)

[ErikEJ]

As part of that journey we noticed that System.Data.SqlClient doesn't support 'Always Encrypted' (a feature we are required to use). So in order to solve the problem the decision was made to upgrade from System.Data.SqlClient to Microsoft.Data.SqlClient.

That is exactly why this library was created!

I also tried your repro and hit the same issue. I have updated the docs, and hoping for an improved M.D.S. provider in the future.

[ajcvickers]

@Zyano I realize you may have reasons not to use EF Core, but it has support for spatial types through integration with Net Topology Suite that work with SQL Server (and other databases) without the need for Microsoft.SqlServer.Types.

[Zyano]

@ajcvickers Sorry for the delay in response. We are actively working towards upgrading from .NET Framework and EF 6.4 to .NET 6 and EF Core. As part of that process all new projects would be built using .NET 6 requiring us to update to Microsoft.Data.SqlClient in order to meet the encryption requirement mentioned earlier.

At the end of the day this is all intended to be "temporary" while we upgrade our solutions to .NET 6 and EF Core. The process of getting there will however take sometime and having the ability to take one area at a time is essential to us.

Thanks again to all of you for taking the time to supply insights and help with the issue.

[ErikEJ]

@Zyano @dotMorten A Microsoft.SqlServer.Types package with .NET Standard 2.1 support is currently in preview. I have released an update to the ErikEJ.EntityFramework.SqlServer package that uses it with Microsoft.Data.SqlClient 5.0.0 - and initial smoke testing seems to indicate that it works with EF 6 (Classic) on .NET Core https://www.nuget.org/packages/ErikEJ.EntityFramework.SqlServer/6.6.0-rc1

[Zyano]

That's great news! I'll do some test with the package later this week and see how it works for us.

[dotMorten]

Not really that great. There's a runtimes folder in there indicating it still only supports Windows x86 and x64. No ARM64 support, nor ios, android, macos or linux support.

[ErikEJ]

@dotMorten Yes, I noticed that. Hopefully in the works?

[dotMorten]

Is there any indication they are doing that? I don't see it.

[dotMorten]

Also I don’t get why they aren’t targeting net6.0-windows instead. There’s no point to supporting netstandard2.1 if it’s windows only and not uwp

[ajcvickers]

There is really very little point in targeting .NET Standard 2.1 for any reason.

[ErikEJ]

@ajcvickers can you raise this with the team behind the Types package?

[ajcvickers]

@ErikEJ We will try again, but they have their own plans and goals, and we don't have much influence here.

[dotMorten]

There was a point in time where the spatial team did great community involvement and listened to the dev community but seems to have died out with Ed Katibah and Isaac Kunen leaving the team. Feels like they’re developing in a vacuum these days with no way to provide them direct community feedback.

2.2mio downloads of my crossplatform version of sqltypes should show there's a need. Sadly there's no way for my implementation to be a 100% full and identical implementation and it's just a stop gap until the team can port their code. It's just raw math in there. There shouldn't be a lot of reason why that native code can't be cross compiled.

[bricelam]

It still only supports Windows x86 and x64. No ARM64 support, nor ios, android, macos or linux support.

Hopefully in the works?

Don't hold your breath. 😉

[ErikEJ]

@bricelam LOL!

[bricelam]

It's just raw math in there. There shouldn't be a lot of reason why that native code can't be cross compiled.

💯