Use SqlGeography at ServiceStack.OrmLite in .Net Core

Question

Use SqlGeography at ServiceStack.OrmLite in .Net Core

I try to add SqlGeography to my model and when I call create table I got weird error.

First I add this package: Microsoft.SqlServer.Types

Then I create my model like example at below:

public class Locations 
{
   public int Id { get; set; }
   public string Name { get; set; }
   public SqlGeography Location { get; set; }
}

Then call CreateTableIfNotExists to create table

private void CheckDB(IDbConnectionFactory dbConnectionFactory)
{
    using (var db = dbConnectionFactory.Open())
    {
        db.CreateTableIfNotExists<Models.Entities.DbIpEntity>();
    }
}

And at end I got this error:

System.TypeLoadException HResult=0x80131522 Message=Could not load type 'Microsoft.SqlServer.Server.IBinarySerialize' from assembly 'System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'. Source=System.Private.CoreLib
StackTrace: at System.Signature.GetSignature(Void* pCorSig, Int32 cCorSig, RuntimeFieldHandleInternal fieldHandle, IRuntimeMethodInfo methodHandle, RuntimeType declaringType) at System.Reflection.RuntimeMethodInfo.FetchNonReturnParameters() at System.Reflection.RuntimeMethodInfo.GetParametersNoCopy() at System.Reflection.RuntimePropertyInfo.GetIndexParametersNoCopy() at System.Reflection.RuntimePropertyInfo.GetIndexParameters() at ServiceStack.OrmLite.OrmLiteConfigExtensions.GetModelDefinition(Type modelType) at ServiceStack.OrmLite.OrmLiteWriteCommandExtensions.CreateTable(IDbCommand dbCmd, Boolean overwrite, Type modelType) at ServiceStack.OrmLite.OrmLiteExecFilter.Exec[T](IDbConnection dbConn, Func`2 filter) at ServiceStack.OrmLite.OrmLiteSchemaApi.DropAndCreateTable[T](IDbConnection dbConn) at GeoApi.AppHost.CheckDB(IDbConnectionFactory dbConnectionFactory) in E:\Projects\Geo\AppHost.cs:line 48 at GeoApi.AppHost.Configure(Container container) in E:\Projects\Geo\AppHost.cs:line 40 at ServiceStack.ServiceStackHost.Init() at ServiceStack.NetCoreAppHostExtensions.UseServiceStack(IApplicationBuilder app, AppHostBase appHost) at GeoApi.Startup.Configure(IApplicationBuilder app, IHostingEnvironment env) in E:\Projects\Geo\Startup.cs:line 49

In this error I realized it's looking for .Net Framework assembly ( System.Data, Version=4.0.0.0 ) not .Net Core

c#

.net-core

servicestack

ormlite-servicestack

asked on Stack Overflow Mar 25, 2018 by

Omid Mafakher

2 Answers

The ServiceStack.OrmLite.SqlServer.Converters and Microsoft.SqlServer.Types where SqlGeography is defined is only available for .NET v4.5 and .NET v4.0 respectively so it requires a minimum of .NET v4.5 to run and can't be used in .NET Core.

answered on Stack Overflow Mar 25, 2018 by

mythz

.net core 2.2 now supports working with GeoSpatial data - https://docs.microsoft.com/en-us/ef/core/modeling/spatial

I was able to knock together my own OrmLiteConverter based around the SqlServerGeographyTypeConverter here.

public class SqlServerIPointTypeConverter : OrmLiteConverter
{
    public override string ColumnDefinition => "geography";
    public override DbType DbType => DbType.Object;

    public override string ToQuotedString(Type fieldType, object value)
    {
        if (fieldType != typeof(IPoint)) return base.ToQuotedString(fieldType, value);

        string str = null;
        if (value != null)
        {
            var geo = (IPoint) value;
            str = geo.ToString();
        }

        str = (str == null) ? "null" : $"'{str}'";
        return $"CAST({str} AS {ColumnDefinition})";
    }

    public override void InitDbParam(IDbDataParameter p, Type fieldType)
    {
        if (fieldType == typeof(IPoint))
        {
            var sqlParam = (SqlParameter)p;
            sqlParam.IsNullable = fieldType.IsNullableType();
            sqlParam.SqlDbType = SqlDbType.Udt;
            sqlParam.UdtTypeName = ColumnDefinition;
        }

        base.InitDbParam(p, fieldType);
    }

    public override object FromDbValue(Type fieldType, object value)
    {
        switch (value)
        {
            case null:
            case DBNull _:
                return new Point(0, 0);
            case IPoint point:
                return point;
            case string _:
                return Parse(value.ToString());
            default:
                return base.FromDbValue(fieldType, value);
        }
    }

    public override object ToDbValue(Type fieldType, object value)
    {
        switch (value)
        {
            case null:
            case DBNull _:
                return new Point(0, 0);
            case IPoint _:
                return value;
            case string str:
                return Parse(str);
            default:
                return base.ToDbValue(fieldType, value);
        }
    }

    private static Point Parse(string rawPoint)
    {
        var split = rawPoint.Replace("POINT (", string.Empty)
            .Replace(")", string.Empty)
            .Trim()
            .Split(new[] { " " }, StringSplitOptions.RemoveEmptyEntries);

        var longitude = Convert.ToDouble(split[0]);
        var latitude = Convert.ToDouble(split[1]);

        return new Point(longitude, latitude);
    }
}

Then in your AppHost file:

SqlServerDialect.Provider.RegisterConverter<Point>(new SqlServerIPointTypeConverter());

As long as you are on dotnet core 2.2, and have the following Nuget packages referenced, it should work:

answered on Stack Overflow Feb 10, 2019 by

JMK • edited Feb 10, 2019 by

JMK

User contributions licensed under CC BY-SA 3.0