Seeded geometry is re-updated when scaffolding new migration [NetTopologySuite]

[angularsen]

Seeded geometry is re-updated when scaffolding new migration [NetTopologySuite]

Probably a configuration error, but I am not sure how to debug it and could not find any examples on seeding geometry.

The geometry is seeded as expected, but every time we scaffold a new migration the same identical values are re-updated. Would expect to only include these in a single migration unless the values change.

Seems relevant: #18729

Code first model:

    public class ApiDbContext : AppDbContext
    {
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<GeoArea>(area =>
            {
                area.ToTable("geo_areas", DbSchemeName);
                area.HasData(GeoAreaSeeder.GetGeoAreas());
            });
        }
    }

Geometry seed values:

    public static class GeoAreaSeeder
    {
        public static GeoArea[] GetGeoAreas()
        {
            var geometryFactory = CreateGeometryFactory(); // We reuse a lazy, static instance.
            return new[]
            {
                new GeoArea
                {
                    Id = new Guid("00000001-0000-0000-0000-000000000000"),
                    Name = "Trondheim",
                    Description = "Seeded.",
                    Type = GeoAreaType.City,
                    Polygon = CreateRectanglePolygon(geometryFactory,
                        new Coordinate(10.0358345802475, 63.2432650994015),
                        new Coordinate(10.5668781234632, 63.4730293396388))
                },
                // ...more
            };
        }

        private static GeometryFactory CreateGeometryFactory()
        {
            // Initialize default spatial data parameters: https://github.com/NetTopologySuite/NetTopologySuite/wiki/GettingStarted
            var services = new NtsGeometryServices(
                NetTopologySuite.Geometries.Implementation.CoordinateArraySequenceFactory.Instance,
                new PrecisionModel(1e4), // 4 decimal places (65.1234).
                srid: SridValues.Wgs84_4326, // SRID 4326 refers to WGS 84 coordinate system.
                GeometryOverlay.NG, // Use the next-gen overlay.
                new CoordinateEqualityComparer()); // Default comparer.

            NtsGeometryServices.Instance = services;
            return services.CreateGeometryFactory();
        }

       private static Polygon CreateRectanglePolygon(GeometryFactory geometryFactory, Coordinate min, Coordinate max)
        {
            // Must be a closed polygon and counter-clockwise.
            return geometryFactory.CreatePolygon(new[]
            {
                min,
                new Coordinate(max.X, min.Y),
                max,
                new Coordinate(min.X, max.Y),
                min, // Closed ring
            });
        }
    }

1st scaffolded migration:

            migrationBuilder.InsertData(
                schema: "api",
                table: "geo_areas",
                columns: new[] { "Id", "Description", "ImportNote", "ImportedTime", "Name", "Polygon", "Type" },
                values: new object[,]
                {
                    { new Guid("00000001-0000-0000-0000-000000000000"), "Seeded.", null, null, "Trondheim", (NetTopologySuite.Geometries.Polygon)new NetTopologySuite.IO.WKTReader().Read("SRID=4326;POLYGON ((10.03583 63.24327, 10.56688 63.24327, 10.56688 63.47303, 10.03583 63.47303, 10.03583 63.24327))"), "City" },
                     // ...multiple of these

2nd scaffolded migration:

            // ...multiple of these
            migrationBuilder.UpdateData(
                schema: "api",
                table: "geo_areas",
                keyColumn: "Id",
                keyValue: new Guid("00000001-0000-0000-0000-000000000000"),
                column: "Polygon",
                value: (NetTopologySuite.Geometries.Polygon)new NetTopologySuite.IO.WKTReader().Read("SRID=4326;POLYGON ((10.03583 63.24327, 10.56688 63.24327, 10.56688 63.47303, 10.03583 63.47303, 10.03583 63.24327))"));

1st scaffolded SQL:

IF NOT EXISTS(SELECT * FROM [api].[_migration_history] WHERE [MigrationId] = N'20211108173632_Add_GeoArea')
BEGIN
    IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'Id', N'Description', N'ImportNote', N'ImportedTime', N'Name', N'Polygon', N'Type') AND [object_id] = OBJECT_ID(N'[api].[geo_areas]'))
        SET IDENTITY_INSERT [api].[geo_areas] ON;
    EXEC(N'INSERT INTO [api].[geo_areas] ([Id], [Description], [ImportNote], [ImportedTime], [Name], [Polygon], [Type])
    VALUES (''00000001-0000-0000-0000-000000000000'', N''Seeded.'', NULL, NULL, N''Trondheim'', geography::Parse(''POLYGON ((10.0358 63.2433, 10.5669 63.2433, 10.5669 63.473, 10.0358 63.473, 10.0358 63.2433))''), ''City''),

2nd scaffolded SQL script:

IF NOT EXISTS(SELECT * FROM [api].[_migration_history] WHERE [MigrationId] = N'20220204134433_TestMigration')
BEGIN
    EXEC(N'UPDATE [api].[geo_areas] SET [Polygon] = geography::Parse(''POLYGON ((10.0358 63.2433, 10.5669 63.2433, 10.5669 63.473, 10.0358 63.473, 10.0358 63.2433))'')
    WHERE [Id] = ''00000001-0000-0000-0000-000000000000'';
    SELECT @@ROWCOUNT');
END;
GO

Include provider and version information

EF Core version: 6.0.0
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: 6.0
Operating system: Win 11, Mac
IDE: Rider 2021.3.3

[angularsen]

Obviously, after posting, I think I found the answer.

By looking at the difference in Up() and Down() of the 2nd migration, I spotted a difference in precision (4 vs 5 decimals). The Up() values were identical to the 1st migration's inserted data, so I'm assuming our snapshot has an outdated precision stored.

If that is correct, then fixing our snapshot should resolve our problem.
Will check more and report back.

[angularsen]

It seems related to configuring a non-default precision in NtsGeometryServices.Instance.

Testing different precisions

As in the example in the original post, we initialize NetTopologySuite as part of creating seed data.

// Initialize default spatial data parameters: https://github.com/NetTopologySuite/NetTopologySuite/wiki/GettingStarted
var services = new NtsGeometryServices(
    NetTopologySuite.Geometries.Implementation.CoordinateArraySequenceFactory.Instance,
    new PrecisionModel(1e4), // 4 decimal places (65.1234).
    srid: SridValues.Wgs84_4326, // SRID 4326 refers to WGS 84 coordinate system.
    GeometryOverlay.NG, // Use the next-gen overlay.
    new CoordinateEqualityComparer()); // Default comparer.

NtsGeometryServices.Instance = services;

Default precision of Floating ✅

By passing new PrecisionModel() to NtsGeometryServices we get full precision, as per the docs: Creates a PrecisionModel with a default precision of Floating.

Symptom 1: A second migration does NOT update old seed data. ✅
Symptom 2: Up() has more precision than Down() when migrating old data seeded with 1e4 precision, but has identical precision on the second empty migration. ✅

The only issue with this solution is that we would like to seed data with a given precision.

Examples of seed data in scaffolded migration scripts Up() and Down()

SRID=4326;POLYGON ((10.0358345802475 63.2432650994015, 10.5668781234632 63.2432650994015, 10.5668781234632 63.4730293396388, 10.0358345802475 63.4730293396388, 10.0358345802475 63.2432650994015))

SRID=4326;POLYGON ((10.03583 63.24327, 10.56688 63.24327, 10.56688 63.47303, 10.03583 63.47303, 10.03583 63.24327))

Precision with scale 1e4 ❌

If new PrecisionModel(1e4) is used, coordinates have 5 decimals in Up() and 3-4 decimals in Down().

Symptom 1: A second empty migration re-updates all old seed data. ❌
Symptom 2: Up() has more precision than Down(), even when the migration did not change the precision. ❌

Up() / Down()

SRID=4326;POLYGON ((10.03583 63.24327, 10.56688 63.24327, 10.56688 63.47303, 10.03583 63.47303, 10.03583 63.24327))

SRID=4326;POLYGON ((10.0358 63.2433, 10.5669 63.2433, 10.5669 63.473, 10.0358 63.473, 10.0358 63.2433))

Precision with scale 1e3 ❌

Similar to 1e4, but with 1 less precision as expected.

Symptom 1: A second empty migration re-updates all old seed data. ❌
Symptom 2: Up() has more precision than Down(), even when the migration did not change the precision. ❌

Up() / Down()

SRID=4326;POLYGON ((10.0358 63.2433, 10.5669 63.2433, 10.5669 63.473, 10.0358 63.473, 10.0358 63.2433))

SRID=4326;POLYGON ((10.036 63.243, 10.567 63.243, 10.567 63.473, 10.036 63.473, 10.036 63.243))

[ajcvickers]

@bricelam Looks like maybe the model snapshot should always use full precision.