Regression from 2.2.6, Microsoft Sql Server, NetTopologySuite, Operand type clash: Exception: "geography is incompatible with geometry"

[yahorsi]

Support for Spatial data in Microsoft Sql Server is partially broken in all versions starting from 2.2.6. Currently EF in version starting from 3.0 is just unable to add table records containing spatial column (happens if spatial column is not null).

Here is project to demo the problem. https://github.com/yahorsi/Ef31SqlServerSpatialBug Projects contains both database to create the database and .NET code to demo the bug. Works just fine if you use 2.2.6 and failing in 3.0, 3.1, 3.1.1

Steps to reproduce

Create Microsoft Sql Database database using following script:

CREATE DATABASE Ef31SqlServerSpatialBug
GO

USE Ef31SqlServerSpatialBug
GO

CREATE TABLE FooTable
(
    [Id] INT PRIMARY KEY,
    [Location] [geometry] NULL
)
GO

And corresponding C# code

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

using Microsoft.Data.SqlClient;

using Microsoft.EntityFrameworkCore;
using NetTopologySuite.Geometries;

namespace Ef31SqlServerSpatialBug
{
    [Table("FooTable")]
    public class FooTable
    {
        [Key]
        public int Id { get; set; }

        public Point Location { get; set; }
    }

    public class DatabaseContext : DbContext
    {
        public DbSet<FooTable> FooTable { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            var connectionString = "Server=.;Database=Ef31SqlServerSpatialBug;Trusted_Connection=True;";
            var connection = new SqlConnection(connectionString);
            optionsBuilder.UseSqlServer(connection, x => x.UseNetTopologySuite());
            base.OnConfiguring(optionsBuilder);
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                var context = new DatabaseContext();

                var fooTableEntry = new FooTable
                {
                    Id = 1,
                    Location = new Point(1, 1) { SRID = 4326 }
                };

                context.FooTable.Add(fooTableEntry);
                context.SaveChanges();

                Console.WriteLine("Hello World!");
            }
            catch(Exception e)
            {
                Console.WriteLine(e);
            }
        }
    }
}

Exception

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
 ---> Microsoft.Data.SqlClient.SqlException (0x80131904): Operand type clash: geography is incompatible with geometry
   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:4efef0f2-a736-4f04-8d17-1538ab5c9ceb
Error Number:206,State:2,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()
   at Ef31SqlServerSpatialBug.Program.Main(String[] args) in C:\Dev\Ef31SqlServerSpatialBug\Program.cs:line 49

Further technical details

EF Core version: 3.0, 3.1, 3.1.1 Database provider: Microsoft Sql Server Target framework: 3.1 Operating system: Windows 10 IDE: Visual Studio 2019 16.4.2

[ajcvickers]

@yahorsi If your database has geometry types (as opposed to the more common geography types) then you need to tell EF this in the mapping. See https://docs.microsoft.com/en-us/ef/core/modeling/spatial#sql-server

[yahorsi]

@yahorsi If your database has geometry types (as opposed to the more common geography types) then you need to tell EF this in the mapping. See https://docs.microsoft.com/en-us/ef/core/modeling/spatial#sql-server

It might be that I didn't understand something, but the code I have posted and created repository for works in for the 2.2 and breaks in 3.0 and higher? We're using Point type, not sure if it is geometry or geography, in the Sql Server Database database it is declared as [geometry]

Are there any breaking changes in the spatial types support?

[ajcvickers]

@yahorsi It's possible this worked for 2.x because we changed slightly the way parameters are sent to the database in 3.0. It worked in 2.x by accident.

/cc @bricelam

[yahorsi]

@yahorsi It's possible this worked for 2.x because we changed slightly the way parameters are sent to the database in 3.0. It worked in 2.x by accident.

/cc @bricelam

In the article you provided there is no any sample on how to "you need to tell EF this in the mapping". Poin class in the article is used without any annotations.

[Table("Cities", Schema = "Application"))]
class City
{
    public int CityID { get; set; }
   public string CityName { get; set; }
   public Point Location { get; set; }
}

PS If it was working just by mistake and it does not now it is still breaking change so it at least worth documenting

[ajcvickers]

@yahorsi From the article:

Geography or geometry By default, spatial properties are mapped to geography columns in SQL Server. To use geometry, configure the column type in your model.

Agreed that this is a breaking change.

[yahorsi]

@yahorsi From the article:

I just meant in the article there is no sample on how to do that

[yahorsi]

@yahorsi If your database has geometry types (as opposed to the more common geography types) then you need to tell EF this in the mapping. See https://docs.microsoft.com/en-us/ef/core/modeling/spatial#sql-server

Just to clarify, do you mean we should use something like?

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<FooTable>(entity =>
            {
                entity.Property(e => e.Location).HasColumnType("geometry");
            });
        }

[ajcvickers]

@yahorsi Yes, that or use ColumnAttribute.