Bug: Error when trying to insert to Geography column using IPropertyHandler

[Simon-Carr]

Bug Description

Error when trying to insert to Geography column using IPropertyHandler.

1. I have verified that the lat/lng being passed are correct and valid.
2. The Set method in the property handler is hit when debugging and returns a valid looking SqlGeography object.
3. The Get method in the property handler works fine.
4. I am able to use lat/lng manually by inserting the values into float columns and then updating the Geography column from those directly:

UPDATE leads.lead
SET geolocation=geography::Point(lat, lng, 4326)
WHERE LeadId=146893;

Exception Message:

System.InvalidCastException: Failed to convert parameter value from a SqlGeography to a String.
 ---> System.InvalidCastException: Object must implement IConvertible.
   at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
   at Microsoft.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming)
   --- End of inner exception stack trace ---
   at Microsoft.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming)
   at Microsoft.Data.SqlClient.SqlParameter.GetCoercedValue()
   at Microsoft.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc)
   at Microsoft.Data.SqlClient.SqlCommand.BuildParamList(TdsParser parser, SqlParameterCollection parameters, Boolean includeReturnValue)
   at Microsoft.Data.SqlClient.SqlCommand.BuildExecuteSql(CommandBehavior behavior, String commandText, SqlParameterCollection parameters, _SqlRPC& rpc)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
   at Microsoft.Data.SqlClient.SqlCommand.BeginExecuteReaderInternal(CommandBehavior behavior, AsyncCallback callback, Object stateObject, Int32 timeout, Boolean inRetry, Boolean asyncWrite)
   at Microsoft.Data.SqlClient.SqlCommand.<>c.<InternalExecuteReaderAsync>b__201_0(AsyncCallback callback, Object stateObject)
   at System.Threading.Tasks.TaskFactory`1.FromAsyncImpl(Func`3 beginMethod, Func`2 endFunction, Action`1 endAction, Object state, TaskCreationOptions creationOptions)
   at System.Threading.Tasks.TaskFactory`1.FromAsync(Func`3 beginMethod, Func`2 endMethod, Object state)
   at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
--- End of stack trace from previous location ---
   at RepoDb.DbConnectionExtension.InsertAsyncInternalBase[TEntity,TResult](IDbConnection connection, String tableName, TEntity entity, IEnumerable`1 fields, String hints, Nullable`1 commandTimeout, String traceKey, IDbTransaction transaction, ITrace trace, IStatementBuilder statementBuilder, CancellationToken cancellationToken)
   at Tvd.Leads.Infrastructure.Repositories.Leads.SaveLeadCommandHandler.Handle(SaveLeadCommand request, CancellationToken cancellationToken) in C:\Projects\Tvd.Leads\Tvd.Leads.Infrastructure\Repositories\Leads\SaveLead.cs:line 28
   at Tvd.Leads.Infrastructure.Repositories.Leads.SaveLeadCommandHandler.Handle(SaveLeadCommand request, CancellationToken cancellationToken) in C:\Projects\Tvd.Leads\Tvd.Leads.Infrastructure\Repositories\Leads\SaveLead.cs:line 55
   at Tvd.Leads.Infrastructure.Repositories.Leads.SaveLeadCommandHandler.Handle(SaveLeadCommand request, CancellationToken cancellationToken) in C:\Projects\Tvd.Leads\Tvd.Leads.Infrastructure\Repositories\Leads\SaveLead.cs:line 55
   at Tvd.Leads.Application.LeadServiceBase.SaveLeadAndHandleLeadEvents(Lead lead, CancellationToken ct) in C:\Projects\Tvd.Leads\Tvd.Leads.Application\LeadServiceBase.cs:line 19
   at Tvd.Leads.Application.CreateLeadService.Create(CreateLeadModel model, CancellationToken ct) in C:\Projects\Tvd.Leads\Tvd.Leads.Application\CreateLeadService.cs:line 48
   at Tvd.Leads.Api.Controllers.LeadController.Create(CreateLeadModel model) in C:\Projects\Tvd.Leads\Tvd.Leads.Api\Controllers\LeadController.cs:line 16
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(ActionContext actionContext, IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Logged|12_1(ControllerActionInvoker invoker)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeInnerFilterAsync>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|25_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Filters.MiddlewareFilterBuilder.<>c.<<BuildPipeline>b__8_0>d.MoveNext()
--- End of stack trace from previous location ---
   at Tvd.Leads.Api.Infrastructure.ApiKeyAuthorisation.Invoke(HttpContext context) in C:\Projects\Tvd.Leads\Tvd.Leads.Api\Infrastructure\ApiKeyAuthorisation.cs:line 28
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddlewareImpl.Invoke(HttpContext context)

Schema and Model:

It's just a single Geography column, other columns are unrelated,

The insert looks like this:

 await cnn.InsertAsync(dto,
                cancellationToken: cancellationToken, transaction: transaction);

the dto is rather long, but the relevant column looks like this:

 [PropertyHandler(typeof(GeographyPropertyHandler))]
    public CoordinatesDto Geolocation { get; set; }

And this is the handler

public class GeographyPropertyHandler : IPropertyHandler<SqlGeography, CoordinatesDto>
{
    public CoordinatesDto Get(SqlGeography input, PropertyHandlerGetOptions options)
    {
        return new CoordinatesDto
        {
            Longitude = (decimal)input.Long.Value,
            Latitude = (decimal)input.Lat.Value
        };
    }

    public SqlGeography Set(CoordinatesDto input, PropertyHandlerSetOptions options)
    {
        return SqlGeography.Point((double)input.Latitude, (double)input.Longitude, 4326);
    }
}

Library Version:

RepoDb v1.13.1 and RepoDb.SqlServer v1.13.1