Support spatial types in JSON columns

[ajcvickers]

Current exception:

Unhandled exception. System.ArgumentException: .NET number values such as positive and negative infinity cannot be written as valid JSON. To make it work when using 'JsonSerializer', consider specifying 'JsonNumberHandling.AllowNamedFloatingPointLiterals' (see https://docs.microsoft.com/dotnet/api/system.text.j
son.serialization.jsonnumberhandling).
   at System.Text.Json.ThrowHelper.ThrowArgumentException_ValueNotSupported()
   at System.Text.Json.Utf8JsonWriter.WriteNumberValue(Double value)
   at System.Text.Json.Serialization.Converters.DoubleConverter.Write(Utf8JsonWriter writer, Double value, JsonSerializerOptions options)
   at System.Text.Json.Serialization.JsonConverter`1.TryWrite(Utf8JsonWriter writer, T& value, JsonSerializerOptions options, WriteStack& state)
   at System.Text.Json.Serialization.Metadata.JsonPropertyInfo`1.GetMemberAndWriteJson(Object obj, WriteStack& state, Utf8JsonWriter writer)
   at System.Text.Json.Serialization.Converters.ObjectDefaultConverter`1.OnTryWrite(Utf8JsonWriter writer, T value, JsonSerializerOptions options, WriteStack& state)
   at System.Text.Json.Serialization.JsonConverter`1.TryWrite(Utf8JsonWriter writer, T& value, JsonSerializerOptions options, WriteStack& state)
   at System.Text.Json.Serialization.Metadata.JsonPropertyInfo`1.GetMemberAndWriteJson(Object obj, WriteStack& state, Utf8JsonWriter writer)
   at System.Text.Json.Serialization.Converters.ObjectDefaultConverter`1.OnTryWrite(Utf8JsonWriter writer, T value, JsonSerializerOptions options, WriteStack& state)
   at System.Text.Json.Serialization.JsonConverter`1.TryWrite(Utf8JsonWriter writer, T& value, JsonSerializerOptions options, WriteStack& state)
   at System.Text.Json.Serialization.Metadata.JsonPropertyInfo`1.GetMemberAndWriteJson(Object obj, WriteStack& state, Utf8JsonWriter writer)
   at System.Text.Json.Serialization.Converters.ObjectDefaultConverter`1.OnTryWrite(Utf8JsonWriter writer, T value, JsonSerializerOptions options, WriteStack& state)
   at System.Text.Json.Serialization.JsonConverter`1.TryWrite(Utf8JsonWriter writer, T& value, JsonSerializerOptions options, WriteStack& state)
   at System.Text.Json.Serialization.JsonConverter`1.WriteCore(Utf8JsonWriter writer, T& value, JsonSerializerOptions options, WriteStack& state)
   at System.Text.Json.Serialization.JsonConverter`1.WriteCoreAsObject(Utf8JsonWriter writer, Object value, JsonSerializerOptions options, WriteStack& state)
   at System.Text.Json.JsonSerializer.WriteCore[TValue](Utf8JsonWriter writer, TValue& value, JsonTypeInfo`1 jsonTypeInfo)
   at System.Text.Json.JsonSerializer.Serialize[TValue](Utf8JsonWriter writer, TValue value, JsonSerializerOptions options)
   at System.Text.Json.Nodes.JsonValueNotTrimmable`1.WriteTo(Utf8JsonWriter writer, JsonSerializerOptions options)
   at System.Text.Json.Serialization.Converters.JsonValueConverter.Write(Utf8JsonWriter writer, JsonValue value, JsonSerializerOptions options)
   at System.Text.Json.Serialization.Converters.JsonNodeConverter.Write(Utf8JsonWriter writer, JsonNode value, JsonSerializerOptions options)
   at System.Text.Json.Nodes.JsonObject.WriteTo(Utf8JsonWriter writer, JsonSerializerOptions options)
   at System.Text.Json.Serialization.Converters.JsonObjectConverter.Write(Utf8JsonWriter writer, JsonObject value, JsonSerializerOptions options)
   at System.Text.Json.Serialization.Converters.JsonNodeConverter.Write(Utf8JsonWriter writer, JsonNode value, JsonSerializerOptions options)
   at System.Text.Json.Nodes.JsonArray.WriteTo(Utf8JsonWriter writer, JsonSerializerOptions options)
   at System.Text.Json.Serialization.Converters.JsonArrayConverter.Write(Utf8JsonWriter writer, JsonArray value, JsonSerializerOptions options)
   at System.Text.Json.Serialization.Converters.JsonNodeConverter.Write(Utf8JsonWriter writer, JsonNode value, JsonSerializerOptions options)
   at System.Text.Json.Nodes.JsonObject.WriteTo(Utf8JsonWriter writer, JsonSerializerOptions options)
   at System.Text.Json.Nodes.JsonNode.ToJsonString(JsonSerializerOptions options)
   at Microsoft.EntityFrameworkCore.Update.ModificationCommand.GenerateColumnModifications()
   at Microsoft.EntityFrameworkCore.Update.ModificationCommand.<>c.<get_ColumnModifications>b__36_0(ModificationCommand command)
   at Microsoft.EntityFrameworkCore.Internal.NonCapturingLazyInitializer.EnsureInitialized[TParam,TValue](TValue& target, TParam param, Func`2 valueFactory)
   at Microsoft.EntityFrameworkCore.Update.ModificationCommand.get_ColumnModifications()
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.AddParameters(IReadOnlyModificationCommand modificationCommand)
   at Microsoft.EntityFrameworkCore.SqlServer.Update.Internal.SqlServerModificationCommandBatch.AddCommand(IReadOnlyModificationCommand modificationCommand)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.TryAddCommand(IReadOnlyModificationCommand modificationCommand)
   at Microsoft.EntityFrameworkCore.Update.Internal.CommandBatchPreparer.BatchCommands(IList`1 entries, IUpdateAdapter updateAdapter)+MoveNext()
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList`1 entriesToSave, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(StateManager stateManager, Boolean acceptAllChangesOnSuccess, 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.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
   at NewInEfCore7.BlogsContext.Seed() in C:\github\EntityFramework.Docs\samples\core\Miscellaneous\NewInEFCore7\BlogsContext.cs:line 357
   at NewInEfCore7.JsonColumnsSample.JsonColumnsTest[TContext]() in C:\github\EntityFramework.Docs\samples\core\Miscellaneous\NewInEFCore7\JsonColumnsSample.cs:line 19
   at NewInEfCore7.JsonColumnsSample.JsonColumnsTest[TContext]() in C:\github\EntityFramework.Docs\samples\core\Miscellaneous\NewInEFCore7\JsonColumnsSample.cs:line 82
   at Program.Main() in C:\github\EntityFramework.Docs\samples\core\Miscellaneous\NewInEFCore7\Program.cs:line 25
   at Program.<Main>()

[smitpatel]

This exception is about floating point numbers. Given this is exception, we should just enable the option in serializer we use. (After fixing this, the spatial type may run into other errors)

[ajcvickers]

@smitpatel I think it's a bogus exception message. I'll create a full repro so that you can investigate more if you wish.

[smitpatel]

Could be some random component of spatial type showing inf value and not serializing properly. I believe the same exception could also be hit with a double property and trying to save infinity (though not sure what is current behavior outside of JSON for that.)

[ajcvickers]

Below is a full repro. As suspected, the issue isn't really about number handling, but that the Point object cannot be serialized directly to JSON--see the code in the repro that attempts to do this outside of EF.

Fundamentally, I think type mappings need to have a way to configure the JSON serialization used. I think that's likely best tracked by another issue, but I'd like to keep this one specifically about spatial support.

public static class Your
{
    public static string ConnectionString = @"Data Source=(LocalDb)\MSSQLLocalDB;Database=AllTogetherNow";
}

public class Blog
{
    public int Id { get; set; }
    public Location Location { get; set; }
}

public class Location
{
    public Point Point { get; set; }
}

public class SomeDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(Your.ConnectionString, b => b.UseNetTopologySuite())
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();

    public DbSet<Blog> Blogs => Set<Blog>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Blog>().OwnsOne(
            e => e.Location, b =>
                {
                    b.ToJson();
                });
    }
}

public class Program
{
    public static void Main()
    {
        var point = new Point(115.7930, 37.2431) { SRID = 4326 };

        var options = new JsonSerializerOptions(JsonSerializerDefaults.General);
        options.NumberHandling |= JsonNumberHandling.AllowNamedFloatingPointLiterals;
        options.ReferenceHandler = ReferenceHandler.Preserve;

        var json = JsonSerializer.Serialize(point, options);
        
        using (var context = new SomeDbContext())
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            context.Add(new Blog { Location = new Location { Point = new Point(115.7930, 37.2431) { SRID = 4326 } } });

            context.SaveChanges();
        }
    }
}

Unhandled exception. System.ArgumentOutOfRangeException: Specified argument was out of the range of valid values. (Parameter 'X called on empty Point')
   at NetTopologySuite.Geometries.Point.get_X()
   at System.Text.Json.Serialization.Metadata.JsonPropertyInfo`1.GetMemberAndWriteJson(Object obj, WriteStack& state, Utf8JsonWriter writer)
   at System.Text.Json.Serialization.Converters.ObjectDefaultConverter`1.OnTryWrite(Utf8JsonWriter writer, T value, JsonSerializerOptions options, WriteStack& state)
   at System.Text.Json.Serialization.JsonConverter`1.TryWrite(Utf8JsonWriter writer, T& value, JsonSerializerOptions options, WriteStack& state)
   at System.Text.Json.Serialization.Metadata.JsonPropertyInfo`1.GetMemberAndWriteJson(Object obj, WriteStack& state, Utf8JsonWriter writer)
   at System.Text.Json.Serialization.Converters.ObjectDefaultConverter`1.OnTryWrite(Utf8JsonWriter writer, T value, JsonSerializerOptions options, WriteStack& state)
   at System.Text.Json.Serialization.JsonConverter`1.TryWrite(Utf8JsonWriter writer, T& value, JsonSerializerOptions options, WriteStack& state)
   at System.Text.Json.Serialization.Metadata.JsonPropertyInfo`1.GetMemberAndWriteJson(Object obj, WriteStack& state, Utf8JsonWriter writer)
   at System.Text.Json.Serialization.Converters.ObjectDefaultConverter`1.OnTryWrite(Utf8JsonWriter writer, T value, JsonSerializerOptions options, WriteStack& state)
   at System.Text.Json.Serialization.JsonConverter`1.TryWrite(Utf8JsonWriter writer, T& value, JsonSerializerOptions options, WriteStack& state)
   at System.Text.Json.Serialization.JsonConverter`1.WriteCore(Utf8JsonWriter writer, T& value, JsonSerializerOptions options, WriteStack& state)
   at System.Text.Json.JsonSerializer.WriteCore[TValue](Utf8JsonWriter writer, TValue& value, JsonTypeInfo`1 jsonTypeInfo)
   at System.Text.Json.JsonSerializer.WriteString[TValue](TValue& value, JsonTypeInfo`1 jsonTypeInfo)
   at System.Text.Json.JsonSerializer.Serialize[TValue](TValue value, JsonSerializerOptions options)
   at Program.Main() in C:\local\code\AllTogetherNow\Daily\Daily.cs:line 66

[ajcvickers]

/cc @bricelam

[ajcvickers]

Depends on #28835.

[bricelam]

This is a very similar problem to #17317 where we also want to inject the GeoJSON representation into the parent.

[mhosman]

This will be fixed for .NET 8? I will go back to Newtonsoft untill this is fixed. Thanks!

[ClevyrAndy]

Unhandled exception. System.ArgumentOutOfRangeException: Specified argument was out of the range of valid values. (Parameter 'X called on empty Point')
at NetTopologySuite.Geometries.Point.get_X()
at System.Text.Json.Serialization.Metadata.JsonPropertyInfo1.GetMemberAndWriteJson(Object obj, WriteStack& state, Utf8JsonWriter writer) at System.Text.Json.Serialization.Converters.ObjectDefaultConverter1.OnTryWrite(Utf8JsonWriter writer, T value, JsonSerializerOptions options, WriteStack& state)
at System.Text.Json.Serialization.JsonConverter1.TryWrite(Utf8JsonWriter writer, T& value, JsonSerializerOptions options, WriteStack& state) at System.Text.Json.Serialization.Metadata.JsonPropertyInfo1.GetMemberAndWriteJson(Object obj, WriteStack& state, Utf8JsonWriter writer)
at System.Text.Json.Serialization.Converters.ObjectDefaultConverter1.OnTryWrite(Utf8JsonWriter writer, T value, JsonSerializerOptions options, WriteStack& state) at System.Text.Json.Serialization.JsonConverter1.TryWrite(Utf8JsonWriter writer, T& value, JsonSerializerOptions options, WriteStack& state)
at System.Text.Json.Serialization.Metadata.JsonPropertyInfo1.GetMemberAndWriteJson(Object obj, WriteStack& state, Utf8JsonWriter writer) at System.Text.Json.Serialization.Converters.ObjectDefaultConverter1.OnTryWrite(Utf8JsonWriter writer, T value, JsonSerializerOptions options, WriteStack& state)
at System.Text.Json.Serialization.JsonConverter1.TryWrite(Utf8JsonWriter writer, T& value, JsonSerializerOptions options, WriteStack& state) at System.Text.Json.Serialization.JsonConverter1.WriteCore(Utf8JsonWriter writer, T& value, JsonSerializerOptions options, WriteStack& state)
at System.Text.Json.JsonSerializer.WriteCore[TValue](Utf8JsonWriter writer, TValue& value, JsonTypeInfo1 jsonTypeInfo) at System.Text.Json.JsonSerializer.WriteString[TValue](TValue& value, JsonTypeInfo1 jsonTypeInfo)
at System.Text.Json.JsonSerializer.Serialize[TValue](TValue value, JsonSerializerOptions options)
at Program.Main() in C:\local\code\AllTogetherNow\Daily\Daily.cs:line 66

I was getting this error too, turns out I didn't configure my serializer completely.

var settings = new JsonSerializerOptions() { NumberHandling = System.Text.Json.Serialization.JsonNumberHandling.AllowNamedFloatingPointLiterals, ReferenceHandler = System.Text.Json.Serialization.ReferenceHandler.Preserve, MaxDepth = 100, }; settings.Converters.Add(new NetTopologySuite.IO.Converters.GeoJsonConverterFactory());

Adding the GeoJsonConverterFactory was the key. Its in the package NetTopologySuite.IO.GeoJSON4STJ for those using System.Text.Json serializer and not the newtonsoft serializer. There is another package for newtonsoft users. Hope this helps someone out!

[bricelam]

📝 Design Meeting Note

SQL Server doesn't support GeoJSON. It would be more appropriate to embed the WKT (or WKB or GML) into the JSON column there so you can perform spatial operations in SQL, and interop with regular spatial columns.