3

I'm trying to insert a 'geography' datatype in SQL Server using Dapper 1.50.2 with ASP.NET Core 2.1.

I've read on several threads that it should be accepted by default since 1.32, yet I receive an exception when trying to insert the data type.

Note: I'm using a non .NET CORE data type in my entity. Microsoft.SqlServer.Types: 14.0.1016.290 since I couldn't find a good .NET core compatible geography datatype. (something with EF Core)

Entity:

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

Insert method (standard):

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

Insert method (specialized):

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

I've also tried the Dynamic parameters approach with dapper (from the first post) but I'm unsure how to apply them together with different parameters

Exception

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

UPDATE

I've solved this through a work-around with this piece of code. Getting the syntax right proved difficult.

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;
}
Mathieu
  • 144
  • 12

0 Answers0