Microsoft.SqlServer.Types not supported in .net standard 2.0

[jeffreyabecker]

So I just spent two days trying to Nhibernate.Spatial to run under a .net standard 2.0 app. There were several issues but the primary one has to do with the serialization of sql geometry/geography types. It just doesn't work because some of the underlying serializers aren't in the .net standard 2.0 version of System.Data. Nothing I did was able to get them to reliably load the full-framework version of system.data.

If I can work out a way to reliably read and write sql server geo-types without the dependence on the Microsoft.SqlServer.Types library would that be an acceptable solution?

[jeffreyabecker]

Additionally: when using the .net standard 2.0 version of System.Data.SqlClient, selecting back geo-type fields results in an exception:
System.PlatformNotSupportedException : Type Udt is not supported on this platform.

[jeffreyabecker]

Looks like NetToplogySuite is addressing this:
NetTopologySuite/NetTopologySuite#110

[dotMorten]

Also: https://github.com/dotMorten/Microsoft.SqlServer.Types

[andrerav]

I've been noodling around trying to get NHibernate.Spatial.MySQL working with the new MySQL.Data 8.0.11 driver in a .Net Core project. I got fluent mapping and schema generation working without much trouble. However, one snag I have run into is a change in the GeoAPI IGeometry interface from 1.7.4 to 1.7.5 (still in preview):

// 1.7.4
public interface IGeometry : ICloneable, IComparable, IComparable<IGeometry>, IEquatable<IGeometry>

// 1.7.5-pre020
public interface IGeometry : ICloneable, IComparable, IComparable<IGeometry>

The difference being IEquatable<IGeometry> missing from the latter, triggering this exception when trying to create a session factory:

2018-07-05 22:49:13,546 FATAL - Could not instantiate LinqToHqlGeneratorsRegistry
System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.ArgumentException: Method 'Boolean Equals(GeoAPI.Geometries.IGeometry)' declared on type 'System.IEquatable`1[GeoAPI.Geometries.IGeometry]' cannot be called with instance of type 'GeoAPI.Geometries.IGeometry'
   at System.Linq.Expressions.Expression.ValidateCallInstanceType(Type instanceType, MethodInfo method)
   at System.Linq.Expressions.Expression.ValidateStaticOrInstanceMethod(Expression instance, MethodInfo method)
   at System.Linq.Expressions.Expression.ValidateMethodAndGetParameters(Expression instance, MethodInfo method)
   at System.Linq.Expressions.Expression.Call(Expression instance, MethodInfo method, Expression arg0)
   at System.Linq.Expressions.Expression.Call(Expression instance, MethodInfo method, IEnumerable`1 arguments)
   at NHibernate.Spatial.Linq.Functions.RelationsGenerator..ctor()
   at NHibernate.Spatial.Linq.Functions.SpatialLinqToHqlGeneratorsRegistry..ctor()
   --- End of inner exception stack trace ---
   at System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean& canBeCached, RuntimeMethodHandleInternal& ctor)
   at System.RuntimeType.CreateInstanceSlow(Boolean publicOnly, Boolean skipCheckThis, Boolean fillCache, StackCrawlMark& stackMark)
   at System.Activator.CreateInstance(Type type, Boolean nonPublic)
   at System.Activator.CreateInstance(Type type)
   at NHibernate.Linq.Functions.LinqToHqlGeneratorsRegistryFactory.CreateGeneratorsRegistry(IDictionary`2 properties)

I did a quick run with the portability analyzer, and it seems NHibernate.Spatial should be fairly straightforward to port to .Net Core. The NHibernate and NetTopologySuite teams already did the heavy lifting for us.

So according to Microsoft, the way to go about porting a project to .Net Core is to create new .Net Core projects and move the code files to the new projects. So I am considering giving this a go and try to bring atleast NH.Spatial.MySQL and NH.Spatial.PostGIS over to .Net Core. I haven't looked into @dotMorten's implementation in detail yet, but that may even help us getting NH.Spatial.MsSql ported as well. What do you guys think?

[dotMorten]

I'm curious why you're porting to .NET Core instead of .NET Standard? (Since that'll hit more platforms, including .NET Core)

[peetw]

I also ran into the IEquatable<IGeometry> error when I attempted to upgrade GeoAPI in one of our projects. I didn't have enough time to look into it then, so I just rolled back the update.

I've created a new issue for supporting .NET Standard (see #96).

[MuhKuh7]

Has anyone looked into this issue since last year? I am currently trying to port an application from .net framework to .net core running on Linux and I am trying to figure out how to proceed.

[peetw]

@MuhKuh7 We're still waiting for Microsoft to release a .NET Standard (or .NET Core on all platforms) compatible version of Microsoft.SqlServer.Types - you can follow the issue here: dotnet/SqlClient#30

For anyone targeting .NET Core on Windows only, we could use the workaround suggested by @dotMorten here, but that wouldn't help in your case anyway since you are targeting .NET Core on Linux.

[MuhKuh7]

@peetw I was just trying to replace Microsoft.SqlServer.Types with the version from @dotMorten, but the implementation of the Populate method is missing. Does it even make sense to try to use this as a replacement or do you know of someone who has already tried that? If it would be only that method the implementation would probably be not that hard, but I just started to look into this so I do not have the full picture yet ;)

Otherwise I will need to look into replacing the database server with PostreSQL using PostGIS. Might be less work.

It is strange that Microsoft pushes Linux everywhere and even provides their SQL Server Docker images based on Linux, but on the other hand the support for essential libraries seems to be low priority :(

[CoffeeCodes]

I haven't looked into this really, but I stumbled across this info: dotnet/core#2273 (comment)
It looks like EF had the same issue for their Core version and dropped MSSQL Server Types and replaced it withNetTopologySuite.IO.SqlServerBytes (https://github.com/NetTopologySuite/NetTopologySuite.IO.SqlServerBytes). If the de-/serialization of the geometries is the only issue, this could be an option, also since NHibernate makes use of NTS already.

[MuhKuh7]

@CoffeeCodes, this looks promising!
I just tried a very simple first implementation and got most of the unit test working in my branch https://github.com/MuhKuh7/NHibernate.Spatial/tree/nts-sqlserverbytes

It is far from complete. Currently geography types are ignored. And I am stuck with the user defined type. I used a simple binary, but this prevents the use of functions and stops with "Cannot call methods on binary".

Maybe someone else has more experience with SQL Server and would like to follow up on that idea?

[peetw]

@MuhKuh7 I've created a pre-release of NHibernate.Spatial.MsSql that replaces Microsoft.SqlServer.Types with NetTopologySuite.IO.SqlServerBytes and targets .NET Standard 2.0. Would you be able to test the new version (5.2.1-pre001 on NuGet), preferably on Linux, and see whether it works for you?

[MuhKuh7]

@peetw, thx! I will try it asap.

[peetw]

@MuhKuh7 did you get a chance to test the pre-release package?

[MuhKuh7]

@peetw, I am on vacation, but can test next week.

[MuhKuh7]

@peetw, totally forgot to answer here. The pre-release version works perfectly since several weeks in our application running .net core 2.2 on Debian Stretch

[peetw]

No worries, thanks for getting back to us - good to hear it's working for you! I'm away for the next two weeks, but I'll try and get a full release out once I return (need to add tests and Linux CI builds first though)

[mikemcdougall]

Anyone else having issues saving large polygon geometries?

[peetw]

@mikemcdougall what issues are you having exactly? Do you have a test case you can share?

[mikemcdougall]

@peetw
I found the issue I was having with large geometries.
The problem is in MsSqlGeometryType.cs in the Set(DbCommand cmd, object value, int index, ISessionImplementor session) function.
I changed to
parameter.SqlDbType = SqlDbType.VarBinary;
parameter.Size = ((byte[]) value).Length;

the SqlDBType.Binary has a max of 8000 bytes. So the value being passed in the parameter was being cut off. Sql server was sending back invalid geometry error. Seems to be working with the above changes.

Would you like me to create PR?

[peetw]

@mikemcdougall Great, nice work! I'll try and take a look at the PR ASAP.

[peetw]

@mikemcdougall I've created a new issue for the large geometry problem: #114

Closing this issue as the main work in migrating the MsSql dialect to .NET Standard has been completed.