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