Entity Framework Core 3.1 with NetTopologySuite.Geometries.Point: SqlException: The supplied value is not a valid instance of data type geography

[Ogglas]

Saving a NetTopologySuite.Geometries.Point causes the exception:

SqlException: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 7 ("@p6"): The supplied value is not a valid instance of data type geography. Check the source data for invalid values. An example of an invalid value is data of numeric type with scale greater than precision.

According to all documentation it should be X for longitude and Y for latitude so I don't think that is a problem. I tried to reverse the coordinates just in case but I got the same error as you can see in the examples I have tried.

https://docs.microsoft.com/en-us/ef/core/modeling/spatial

Lat = Y Long = X

https://gis.stackexchange.com/a/68856/71364

I can't find out anything obvious that seems wrong. Optionsbuilder is set up, the table is created with Data Type geography that worked really well with DbGeography for Entity Framework 6.

var optionsBuilder = new DbContextOptionsBuilder<ApplicationDbContext>();
optionsBuilder.UseSqlServer("Server=(localdb)\\mssqllocaldb;Database=TestDb;Trusted_Connection=True;MultipleActiveResultSets=true", x => x.UseNetTopologySuite());
var db = new ApplicationDbContext(optionsBuilder.Options);

There is no specific cases to handle for a single Point either what I can see in documentation for SQL server.

https://docs.microsoft.com/en-us/ef/core/modeling/spatial#sql-server

The coordinates I'm saving is from Google Maps and therefore EPSG 3857 is used.

https://gis.stackexchange.com/questions/48949/epsg-3857-or-4326-for-googlemaps-openstreetmap-and-leaflet

Steps to reproduce

Model:

public class Facility
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    public NetTopologySuite.Geometries.Point Location { get; set; }
}

Test code:

var testFacility = new Facility();
testFacility.Location = new NetTopologySuite.Geometries.Point(13.003725d, 55.604870d) { SRID = 3857 };

//Other values tested with the same error error

//testFacility.Location = new NetTopologySuite.Geometries.Point(13.003725d, 55.604870d);

//testFacility.Location = new NetTopologySuite.Geometries.Point(55.604870d, 13.003725d);

//var geometryFactory = NtsGeometryServices.Instance.CreateGeometryFactory(srid: 3857);
//var currentLocation = geometryFactory.CreatePoint(new Coordinate(13.003725d, 55.604870d));
//testFacility.Location = currentLocation;

db.Facilities.Add(testFacility);
//Exception on Save
db.SaveChanges();

Further technical details

EF Core version: 3.1
Database provider: Microsoft.EntityFrameworkCore.SqlServer with Microsoft.EntityFrameworkCore.SqlServer.NetTopologySuite
Target framework: NET Core 3.1
Operating system: Windows 10 Enterprise
IDE: Visual Studio 2019 16.4.2

Following NuGets are at version 3.1:
Microsoft.AspNetCore.Identity.EntityFrameworkCore
Microsoft.EntityFrameworkCore.SqlServer
Microsoft.EntityFrameworkCore.Tools
Microsoft.EntityFrameworkCore.SqlServer.NetTopologySuite

[ajcvickers]

@Ogglas Please post a small, runnable project that reproduces what you are seeing so that we can investigate.

[tmzblue]

Hello, we recently came across this issue when we were testing our API with an Android Simulator (AVD). The virtual device has Googleplex's coordinates pre-configured and when our application tried to persist this point to db, the following exception arose:

Unhandled exception. Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner 
exception for details.
---> Microsoft.Data.SqlClient.SqlException (0x80131904): The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 2 ("@p1"): The supplied value is not a valid instance of data type geography. Check the source 
data for invalid values. An example of an invalid value is data of numeric type with scale greater than precision.
  at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) 
  at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
  at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, 
Boolean asyncClose)
  at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
  at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
  at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()
  at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
  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.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
  at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
  at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
  at System.Data.Common.DbCommand.ExecuteReader()
  at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)     
  at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
ClientConnectionId:e76a9e1a-e1ba-4969-bccb-03d623dad193
Error Number:8023,State:4,Class:16
  --- End of inner exception stack trace ---
  at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
  at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(IEnumerable`1 commandBatches, IRelationalConnection connection)
  at Microsoft.EntityFrameworkCore.Storage.RelationalDatabase.SaveChanges(IList`1 entries)
  at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IList`1 entriesToSave)
  at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(DbContext _, Boolean acceptAllChangesOnSuccess)
  at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
  at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
  at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
  at Microsoft.EntityFrameworkCore.DbContext.SaveChanges()

I have attached a very simple console program that reproduces the problem:
19416_ef_bug.zip

[bricelam]

cc @airbreather (Just FYI. I plan to dig into this and submit a PR to IO.SqlServerBytes if necessary.)

[Ogglas]

@ajcvickers @bricelam Do you still need a runnable project from me now that @tmzblue posted one?

[airbreather]

@tmzblue

When mapping to a geography column, new Point(x, y) expects a longitude value for x and a latitude value for y. new Point(37.4219983, -122.084) includes a latitude value out of range.

After swapping the two inputs, your program works on my machine.

---

@Ogglas

Check the sys.spatial_reference_systems system view on your database to see if EPSG:3857 is there.

On my home developer instance running 14.0.2027.2, this SRID is missing from that view, so when I try to insert a record with that SRID, I see the same error message that you report. Changing to one of the SRIDs found in that view, the error goes away.

[tmzblue]

@airbreather thanks for the explanation, it solved our problem. Thanks a lot.

[Ogglas]

@airbreather Thank you very much, worked flawlessly.

Wrote an answer about this on SO as well:

https://stackoverflow.com/a/59667168/3850405

Long story short is Google Maps API uses EPSG 3857 but Google Maps Web application uses EPSG 4326. A point from Google Maps Web Application should therefore be created and saved like this:

var testFacility = new Facility();
testFacility.Location = new NetTopologySuite.Geometries.Point(13.003725d, 55.604870d) { SRID = 4326 };
db.Facilities.Add(testFacility);
db.SaveChanges();

https://developers.google.com/maps/documentation/javascript/markers
https://www.google.com/maps/@55.604933,13.003662,14z

A bit tricky to project EPSG 4326 coordinates to EPSG 3857 coordinate system but ProjNet4GeoAPI worked really well.

I have verified that it works here:

http://epsg.io/transform#s_srs=4326&t_srs=3857&x=13.003725&y=55.604870

Example conversion:

var x = 13.003725d;
var y = 55.604870d;

var epsg3857ProjectedCoordinateSystem = ProjNet.CoordinateSystems.ProjectedCoordinateSystem.WebMercator;
var epsg4326GeographicCoordinateSystem = ProjNet.CoordinateSystems.GeographicCoordinateSystem.WGS84;

var coordinateTransformationFactory = new ProjNet.CoordinateSystems.Transformations.CoordinateTransformationFactory();
var coordinateTransformation = coordinateTransformationFactory.CreateFromCoordinateSystems(epsg4326GeographicCoordinateSystem, epsg3857ProjectedCoordinateSystem);

var epsg4326Coordinate = new GeoAPI.Geometries.Coordinate(x, y);

var epsg3857Coordinate = coordinateTransformation.MathTransform.Transform(epsg4326Coordinate);

Complete example program:

To get it running:

• Install NuGets
  • Following NuGets are at version 3.1:
    • Microsoft.EntityFrameworkCore
    • Microsoft.EntityFrameworkCore.SqlServer
    • Microsoft.EntityFrameworkCore.Tools
    • Microsoft.EntityFrameworkCore.SqlServer.NetTopologySuite
  • ProjNET4GeoAPI
• Add-Migration InitialCreate
• Update-Database

Code:

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Design;
using NetTopologySuite;
using NetTopologySuite.Geometries;
using ProjNet.CoordinateSystems;
using ProjNet.CoordinateSystems.Transformations;
using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace TestConsoleAppEFGeo
{
    public class ApplicationDbContextFactory : IDesignTimeDbContextFactory<ApplicationDbContext>
    {
        public ApplicationDbContext CreateDbContext(string[] args)
        {
            var optionsBuilder = new DbContextOptionsBuilder<ApplicationDbContext>();
            optionsBuilder.UseSqlServer("Server=(localdb)\\mssqllocaldb;Database=TestApp;Trusted_Connection=True;MultipleActiveResultSets=true",
                x => x.UseNetTopologySuite());

            return new ApplicationDbContext(optionsBuilder.Options);
        }
    }

    public class ApplicationDbContext : DbContext
    {
        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
            : base(options)
        {
        }

        public virtual DbSet<Facility> Facilities { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
        }
    }

    public class Facility
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        public int Id { get; set; }

        public NetTopologySuite.Geometries.Point Location { get; set; }
    }

    class Program
    {
        static void Main(string[] args)
        {
            var applicationDbContextFactory = new ApplicationDbContextFactory();
            var db = applicationDbContextFactory.CreateDbContext(null);

            var x = 13.003725d;
            var y = 55.604870d;
            var srid = 4326;

            if (!db.Facilities.AnyAsync(x => x.Id == 1).Result)
            {
                var testFacility = new Facility();
                var geometryFactory = NtsGeometryServices.Instance.CreateGeometryFactory(srid);
                var currentLocation = geometryFactory.CreatePoint(new NetTopologySuite.Geometries.Coordinate(x, y));
                testFacility.Id = 1;
                testFacility.Location = currentLocation;

                var testFacility2 = new Facility();
                testFacility2.Id = 2;
                testFacility2.Location = new Point(x, y) { SRID = srid };
                db.Facilities.Add(testFacility);
                db.Facilities.Add(testFacility2);

                //Will throw an exception
                //var testFacility3 = new Facility();
                //testFacility3.Id = 3;
                //testFacility3.Location = new Point(1447568.0454157612d, 7480155.2276327936d) { SRID = 3857 };
                //db.Facilities.Add(testFacility3);

                db.SaveChanges();
            }

            var facility1 = db.Facilities.FirstAsync(x => x.Id == 1).Result;
            var facility2 = db.Facilities.FirstAsync(x => x.Id == 2).Result;

            if(facility1.Location == facility2.Location)
            {
                Console.WriteLine("facility1.Location is equal to facility2.Location");
            }
            else
            {
                Console.WriteLine("facility1.Location is NOT equal to facility2.Location");
            }

            //Test conversion
            //Show coordinate: http://epsg.io/map#srs=4326&x=13.003725&y=55.604870&z=14&layer=streets
            //Conversion: http://epsg.io/transform#s_srs=4326&t_srs=3857&x=13.0037250&y=55.6048700
            //Google Maps - https://www.google.se/maps shows EPSG:4326 when viewing a location
            //https://epsg.io/3857 - Google Maps API is EPSG:3857 however
            //Example: https://developers.google.com/maps/documentation/javascript/markers

            var epsg3857ProjectedCoordinateSystem = ProjectedCoordinateSystem.WebMercator;
            var epsg4326GeographicCoordinateSystem = GeographicCoordinateSystem.WGS84;

            var coordinateTransformationFactory = new CoordinateTransformationFactory();
            var coordinateTransformation = coordinateTransformationFactory.CreateFromCoordinateSystems(epsg4326GeographicCoordinateSystem, epsg3857ProjectedCoordinateSystem);

            var epsg4326Coordinate = new GeoAPI.Geometries.Coordinate(facility1.Location.Coordinate.X, facility1.Location.Coordinate.Y);

            var epsg3857Coordinate = coordinateTransformation.MathTransform.Transform(epsg4326Coordinate);

        }
    }
}

[ahmedalejo]

@airbreather thanks
The SRID was the problem in my case. should suggest an edit on efcore docs.