SqlGeography at ServiceStack.OrmLite在.Net内核中的应用

Question

我尝试将SqlGeography添加到我的模型中，当我调用create时，我得到了奇怪的错误。

首先，我添加了这个包：Microsoft.SqlServer.Types

然后，我像下面的例子一样创建我的模型：

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

然后调用CreateTableIfNotExists创建表

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

最后，我得到了一个错误：

System.TypeLoadException HResult=0x80131522 Message=Could没有从程序集'System.Data，Version=4.0.0.0，Culture=neutral，PublicKeyToken=b77a5c561934e089‘加载'Microsoft.SqlServer.Server.IBinarySerialize’类型。Source=System.Private.CoreLib 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)的ServiceStack.OrmLite.OrmLiteConfigExtensions.GetModelDefinition(Type ServiceStack.OrmLite.OrmLiteWriteCommandExtensions.CreateTable(IDbCommand，ServiceStack.OrmLite.OrmLiteWriteCommandExtensions.CreateTable(IDbCommand覆盖，在ServiceStack.OrmLite.OrmLiteExecFilter.ExecT at ServiceStack.OrmLite.OrmLiteSchemaApi.DropAndCreateTableT at GeoApi.AppHost.CheckDB(IDbConnectionFactory dbConnectionFactory)在E:\Projects\Geo\AppHost.cs:line 48 at GeoApi.AppHost.Configure(集装箱集装箱)在E:\Projects\Geo\AppHost.cs:line 40 at ServiceStack.ServiceStackHost.Init() at ServiceStack.NetCoreAppHostExtensions.UseServiceStack(IApplicationBuilder app，AppHostBase appHost在GeoApi.Startup.Configure(IApplicationBuilder App)( IHostingEnvironment env)在E:\Projects\Geo\Startup.cs:line 49中

在这个错误中，我意识到它在寻找.Net框架程序集( System.Data, Version=4.0.0.0 )，而不是.Net内核

Answer 1

定义了ServiceStack.OrmLite.SqlServer.Converters和Microsoft.SqlServer.Types的SqlGeography只适用于.NET v4.5和.NET v4.0，因此它需要最少的.NET v4.5才能运行，不能在.NET Core中使用。

Answer 2

.net核心2.2现在支持使用GeoSpatial数据- https://learn.microsoft.com/en-us/ef/core/modeling/spatial

我能够将自己的OrmLiteConverter建立在这里。的基础上

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);
    }
}

然后在您的AppHost文件中：

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

只要您在dotnet核心2.2上，并且引用了以下Nuget包，它就会工作：

• Microsoft.EntityFrameworkCore.SqlServer.NetTopologySuite
• ServiceStack.OrmLite.SqlServer.Core