Efcore: Inserting multiple models with IPoint values semi-randomly throws DbUpdateExceptions

[corstian]

When inserting multiple datapoints after each other one half seems to succeed while the other half seems to throw a DbUpdateException for no apparent reason.

{Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SqlClient.SqlException: A .NET Framework error occurred during execution of user-defined routine or aggregate "geography": 
System.FormatException: One of the identified items was in an invalid format.
System.FormatException: 
.
The statement has been terminated.
   at System.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__122_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot)
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteAsync(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
   --- End of inner exception stack trace ---
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(DbContext _, ValueTuple`2 parameters, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IReadOnlyList`1 entriesToSave, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
   at SkyHop.Hosting.Services.OurAirportsImportService.ImportAirports() in C:\Users\corstian\source\repos\SkyHop\SkyHop.Hosting\Services\OurAirportsImportService.cs:line 127}
    Data: {System.Collections.ListDictionaryInternal}
    Entries: Count = 0
    HResult: -2146233088
    HelpLink: null
    InnerException: {System.Data.SqlClient.SqlException (0x80131904): A .NET Framework error occurred during execution of user-defined routine or aggregate "geography": 
System.FormatException: One of the identified items was in an invalid format.
System.FormatException: 
.
The statement has been terminated.
   at System.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__122_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot)
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteAsync(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
ClientConnectionId:4325024c-a305-404a-b8bd-2e21dd7bbc45
Error Number:6522,State:1,Class:16}
    Message: "An error occurred while updating the entries. See the inner exception for details."
    Source: "Microsoft.EntityFrameworkCore.Relational"
    StackTrace: "   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)\r\n   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(DbContext _, ValueTuple`2 parameters, CancellationToken cancellationToken)\r\n   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)\r\n   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IReadOnlyList`1 entriesToSave, CancellationToken cancellationToken)\r\n   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)\r\n   at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)\r\n   at SkyHo
p.Hosting.Services.OurAirportsImportService.ImportAirports() in C:\\Users\\corstian\\source\\repos\\SkyHop\\SkyHop.Hosting\\Services\\OurAirportsImportService.cs:line 127"
    TargetSite: {Void MoveNext()}

See this repository (https://github.com/corstian/efcore-geobug-repro) for a bug reproduction. Please don't mind some data preparation logic in there.

I've got this code working before with EF6, so I highly doubt the data is incorrect.

Further technical details

EF Core version: 2.2.0-rtm-35544
Database Provider: Microsoft.EntityFrameworkCore.SqlServer

[ajcvickers]

@corstian This is the same root cause as #13757. That is, Coordinate takes X first, then Y. But X means longitude and Y means latitude, which is super confusing because it's natural to pass latitude first. So to fix, switch around the lat/lon args when creating the Coordinate.

[corstian]

Update: As NTS uses long/lat for x/y instead of lat/long, this is irrelevant.

Oh wait I remember something like this also being the case with EF6. Is this going to change in future versions? Especially since you're supplying x and y values in the Coordinate object you have to provide to the factory.

[ajcvickers]

@corstian It is unlikely to change fundamentally. However, we may look at adding sugar either through contribution to NTS or in our code to improve the API experience.

[corstian]

Yeah I'd appreciate it very much if the method signature would be something like double longitude, double latitude instead of double x, double y. That'd already be an extremely helpful not-so-breaking change which would solve a lot of confusion in the future.

Ofcourse I realize Coordinate objects do not have to be tied to a global lat/long projection, but in combination with 4326 it would be extremely helpful to make a clear distinction between latitude and longitude input.

While we're at it, the GeoAPI Point object does x/y as lat/long, right?