c# - 不能将 SqlGeography 用作 Dapper 和 ASP.NET 核心的参数

Question

我正在尝试使用 Dapper 1.50.2 和 ASP.NET Core 2.1 在 SQL Server 中插入“地理”数据类型。

我已经阅读了几个线程，从 1.32 开始默认应该接受它，但是在尝试插入数据类型时收到异常。

注意：我在我的实体中使用了非 .NET CORE数据类型。Microsoft.SqlServer.Types: 14.0.1016.290因为我找不到一个好的 .NET 核心兼容的地理数据类型。（带有 EF Core 的东西）

实体：

public class Address : Entity{
    /* .. */
    public SqlGeography SpatialLocation { get; set; }
    /* .. */
}

插入方法（标准）：

public virtual TEntity Insert(TEntity entity){
    if (string.IsNullOrEmpty(entity.CreationUser)){
        entity.CreationUser = "UNKNOWN";
    }

    if (entity.EndDate == default(DateTime)){
        entity.EndDate = DateTime.MaxValue;
    }

    return (TEntity) DapperExtensionsProxy.Insert(entity);
}

插入方法（专门）：

public override Address Insert(Address entity){
    if (entity == null){
        throw new ArgumentNullException(nameof(entity));
    }

    var sql = $"INSERT INTO [dbo].[Address]"
                  + "([CreationDate]"
                  + ",[StartDate]"
                  + ",[UpdateDate]"
                  + ",[EndDate]"
                  + ",[CreationUser]"
                  + ",[UpdateUser]"
                  + ",[CityId]"
                  + ",[Street]"
                  + ",[Street2]"
                  + ",[SpatialLocation]"
                  + ",[Flags])"
              + "VALUES"
                  + "(@creationDate"
                  + ",@startDate"
                  + ",@endDate"
                  + ",@creationUser"
                  + ",@cityId"
                  + ",@street"
                  + ",@street2"
                  + ",@spatial"
                  + ",@flags);";        

    DapperExtensionsProxy.Execute(sql, new
    {
        creationDate = entity.CreationDate,
        startDate = entity.StartDate,
        endDate = entity.EndDate,
        creationUser = entity.CreationUser,
        cityId = entity.CityId,
        street = entity.Street,
        street2 = entity.Street2,
        flags = entity.Flags,
        spatial = entity.SpatialLocation
    });

    /* should get ID back, check with SELECT SCOPE_IDENTITY() */

    return entity;
}

我也尝试过使用 dapper 的动态参数方法（来自第一篇文章），但我不确定如何将它们与不同的参数一起应用

例外

The member spatial of type Microsoft.SqlServer.Types.SqlGeography cannot be 
used as a parameter value

更新

我已经通过使用这段代码的解决方法解决了这个问题。获得正确的语法证明是困难的。

public override Address Insert(Address entity){
    if (entity == null){
        throw new ArgumentNullException(nameof(entity));
    }

    var sql = $"INSERT INTO [dbo].[Address]"
              + "([CreationDate]"
              + ",[StartDate]"
              + ",[EndDate]"
              + ",[CreationUser]"
              + ",[CityId]"
              + ",[Street]"
              + ",[Street2]"
              + ",[SpatialLocation]"
              + ",[Flags])"
              + "VALUES"
              + "(@creationDate"
              + ",@startDate"
              + ",@endDate"
              + ",@creationUser"
              + ",@cityId"
              + ",@street"
              + ",@street2"
              + ",@spatial "
              + ",@flags); "
              + "SELECT SCOPE_IDENTITY()";

    string lat = entity.SpatialLocation.Lat.Value.ToString(CultureInfo.InvariantCulture);
    string longitude = entity.SpatialLocation.Long.Value.ToString(CultureInfo.InvariantCulture);
    entity.Id = DapperExtensionsProxy.ExecuteScalar<int>(sql,new{
                                                             creationDate = entity.CreationDate,
                                                             startDate = entity.StartDate,
                                                             endDate = entity.EndDate,
                                                             creationUser = entity.CreationUser,
                                                             cityId = entity.CityId,
                                                             street = entity.Street,
                                                             street2 = entity.Street2,
                                                             flags = entity.Flags,
                                                             spatial = $"POINT({lat} {longitude} 4326)"
                                                         });
    return entity;
}

标签： c#sql-serverasp.net-core.net-coredapper