Entity Framework Core 5.0 with NetTopologySuite.Geometries.Polygon throws 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.

[tiksn]

I have same issue as here but with SQL Server.

I am passing Polygon property as NULL but in query I see this @p2='0x00000000010C9D94826842394940000000D05D863E40'

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 3 ("@p2"): 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.

I have a Bookmark entity with Boundaries property of type Polygon. In some cases, it will not be provided so should be null.

    public class Bookmark
    {
        public Guid ID { get; set; }

        public string Name { get; set; }

        public Polygon Boundaries { get; set; }

        public Point Center { get; set; }

        public int Zoom { get; set; }

        public bool Permanent { get; set; }
    }

Creating and saving as

var entity = new Bookmark
{
    ID = Guid.NewGuid(),
    Name = name,
    Boundaries = boundaries, // passed as null
    Center = center,
    Zoom = zoom,
    Permanent = false
};

await this.mainDataContext.Bookmarks.AddAsync(entity, cancellationToken);

after calling

context.SaveChangesAsync(cancellationToken);

this is getting logged on console

fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (4ms) [Parameters=[@p0='cc41a4b4-a408-40b3-9ff5-36625b64f1eb', @p1='' (DbType = Object), @p2='0x00000000010C9D94826842394940000000D05D863E40' (Size = 22) (DbType = Object), @p3='Khreschatyk subway station, Kyiv' (Size = 450), @p4='False', @p5='15'], CommandType='Text', Co
mmandTimeout='30']
      SET NOCOUNT ON;
      INSERT INTO [Bookmarks] ([ID], [Boundaries], [Center], [Name], [Permanent], [Zoom])
      VALUES (@p0, @p1, @p2, @p3, @p4, @p5);

with and exception

fail: Microsoft.EntityFrameworkCore.Update[10000]
      An exception occurred in the database while saving changes for context type 'XXXXXXXX.Data.MainDataContext'.
      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 3 ("@p2"): 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.

However following query work fine (when queried manually directly against SQL Server Express

INSERT INTO Bookmarks (ID, Name, Boundaries, Center, Permanent, Zoom)
VALUES (N'5f55b938-c231-4876-87ce-64f3a3deb295', N'Some Name', null, null, 0, 10);

Why is EF Core passing @p2='0x00000000010C9D94826842394940000000D05D863E40' instead of NULL?

EF Core version: 5.0.5
Database provider: Microsoft.EntityFrameworkCore.SqlServer.NetTopologySuite 5.0.5
Target framework: .NET 5.0
Operating system: Windows 10
IDE: Rider 2021.1

P.S. I tried to pass empty Polygon but that results in another exception complaining that Ring has fewer than 4 points, so orientation cannot be determined.

[ajcvickers]

/cc @roji

[roji]

@tiksn for the null case, I couldn't reproduce your problem - inserting a parameterized null NetTopologySuite Polygon seems to work fine in the code sample below. Can you please double-check your code, and if the problem persists, submit a runnable repro (or tweak the sample below) so that it shows the problem?

However, for the empty polygon case I can confirm I see the bug, this seems to be tracked by NetTopologySuite/NetTopologySuite.IO.SqlServerBytes#14 on the NetTopologySuite (and it indeed seems to be similar/identical to NetTopologySuite/NetTopologySuite.IO.PostGis#14 on the PostgreSQL side).

Attempted repro for null case

#nullable disable

await using var ctx = new BlogContext();
await ctx.Database.EnsureDeletedAsync();
await ctx.Database.EnsureCreatedAsync();

Polygon boundaries = null;
Point center = null;

var entity = new Bookmark
{
    ID = Guid.NewGuid(),
    Name = "foo",
    Boundaries = boundaries, // passed as null
    Center = center,
    Zoom = 0,
    Permanent = false
};

await ctx.Bookmarks.AddAsync(entity);
await ctx.SaveChangesAsync();

public class BlogContext : DbContext
{
    public DbSet<Bookmark> Bookmarks { get; set; }

    static ILoggerFactory ContextLoggerFactory
        => LoggerFactory.Create(b => b.AddConsole().AddFilter("", LogLevel.Information));

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(@"...", o => o.UseNetTopologySuite())
            .EnableSensitiveDataLogging()
            .UseLoggerFactory(ContextLoggerFactory);
}

public class Bookmark
{
    public Guid ID { get; set; }
    public string Name { get; set; }
    public Polygon Boundaries { get; set; }
    public Point Center { get; set; }
    public int Zoom { get; set; }
    public bool Permanent { get; set; }
}

After running this, the Boundaries column in the database properly contains NULL.

[tiksn]

@roji thanks for reply and code sample

Your code sample works on my side as well.

      Executed DbCommand (37ms) [Parameters=[@p0='e24f28ae-3a40-45bc-8679-d5767972b164', @p1='' (DbType = Object), @p2='' (DbType = Object), @p3='foo' (Size = 4000), @p4='False', @p5='0'], CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      INSERT INTO [Bookmarks] ([ID], [Boundaries], [Center], [Name], [Permanent], [Zoom])
      VALUES (@p0, @p1, @p2, @p3, @p4, @p5);

Even from log we can see that value is empty.

Turns out that SRID = 4326 was missing for Center. Because my recent changes were only about Boundaries that confused me.

Sorry for misleading bug report and thanks for quick and detailed help.