Microsoft.EntityFrameworkCore.Sqlite.NetTopologySuite error during migration

[Abdragiz]

File a bug

Using Microsoft.EntityFrameworkCore.Sqlite.NetTopologySuite provider, "dotnet ef database update" command throws exception with message "SQLite Error 1: 'table ef_temp_SomeBuildings has no column named Location' when mapping of class that contains spatial type changed.

Steps to reproduce

using System;
using System.Collections.Generic;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Design;
using NetTopologySuite.Geometries;

namespace SQLiteMigrationError
{
    internal class Program
    {
        public class SomeCity
        {
            public Guid Id { get; set; }
            public ICollection<SomeBuilding> SomeBuildings { get; set; }
        }

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

        public class SomeDbContext : DbContext
        {
            public DbSet<SomeCity> SomeCities { get; set; }
            public DbSet<SomeBuilding> SomeBuildings { get; set; }

            public SomeDbContext(DbContextOptions<SomeDbContext> options) : base(options)
            {
            }

            protected override void OnModelCreating(ModelBuilder modelBuilder)
            {
                base.OnModelCreating(modelBuilder);
                modelBuilder.Entity<SomeCity>(mb =>
                {
                    mb.HasKey(x => x.Id);
                    mb.OwnsMany(x => x.SomeBuildings, nb =>
                    {
                        nb.WithOwner().HasForeignKey("SomeCityId");
                        nb.Property<int>("Id");
                        nb.HasKey("Id");
                        nb.Property(x => x.Location).HasColumnType("POINT");
                    });
                });
            }
        }

        public class SomeDbContextFactory : IDesignTimeDbContextFactory<SomeDbContext>
        {
            public SomeDbContext CreateDbContext(string[] args)
            {
                return CreateSomeDbContext();
            }
        }

        private static SomeDbContext CreateSomeDbContext()
        {
            var optionsBuilder = new DbContextOptionsBuilder<SomeDbContext>();
            optionsBuilder.UseSqlite("Data Source=D:\\test.db",
                sqliteDbContextOptionsBuilder => sqliteDbContextOptionsBuilder.UseNetTopologySuite());
            return new SomeDbContext(optionsBuilder.Options);
        }

        private static void Main(string[] args)
        {
            var context = CreateSomeDbContext();
            var someCity = new SomeCity
            {
                Id = Guid.NewGuid(),
                SomeBuildings = new List<SomeBuilding>
                {
                    new()
                    {
                        Location = new Point(0, 0)
                    },
                    new()
                    {
                        Location = new Point(1, 1)
                    }
                }
            };
            context.SomeCities.Add(someCity);
            context.SaveChanges();
        }
    }
}

1. Create migrations
2. Update database
3. Run app
4. Change "SomeCityId" foreign key to "SomeCityIdChanged"
5. Create migrations
6. Try to update database

Include verbose output

D:\Programming\Projects\SQLiteMigrationError\SQLiteMigrationError>dotnet ef database update --verbose
Using project 'D:\Programming\Projects\SQLiteMigrationError\SQLiteMigrationError\SQLiteMigrationError.csproj'.
Using startup project 'D:\Programming\Projects\SQLiteMigrationError\SQLiteMigrationError\SQLiteMigrationError.csproj'.
Writing 'D:\Programming\Projects\SQLiteMigrationError\SQLiteMigrationError\obj\SQLiteMigrationError.csproj.EntityFrameworkCore.targets'...
dotnet msbuild /target:GetEFProjectMetadata /property:EFProjectMetadataFile=C:\Users\nadar\AppData\Local\Temp\tmpA509.tmp /verbosity:quiet /nologo D:\Progr
amming\Projects\SQLiteMigrationError\SQLiteMigrationError\SQLiteMigrationError.csproj
Writing 'D:\Programming\Projects\SQLiteMigrationError\SQLiteMigrationError\obj\SQLiteMigrationError.csproj.EntityFrameworkCore.targets'...
dotnet msbuild /target:GetEFProjectMetadata /property:EFProjectMetadataFile=C:\Users\nadar\AppData\Local\Temp\tmpA818.tmp /verbosity:quiet /nologo D:\Progr
amming\Projects\SQLiteMigrationError\SQLiteMigrationError\SQLiteMigrationError.csproj
Build started...
dotnet build D:\Programming\Projects\SQLiteMigrationError\SQLiteMigrationError\SQLiteMigrationError.csproj /verbosity:quiet /nologo

Сборка успешно завершена.
    Предупреждений: 0
    Ошибок: 0

Прошло времени 00:00:01.47
Build succeeded.
dotnet exec --depsfile D:\Programming\Projects\SQLiteMigrationError\SQLiteMigrationError\bin\Debug\net5.0\SQLiteMigrationError.deps.json --additionalprobin
gpath C:\Users\nadar\.nuget\packages --additionalprobingpath "C:\Program Files\dotnet\sdk\NuGetFallbackFolder" --runtimeconfig D:\Programming\Projects\SQLi
teMigrationError\SQLiteMigrationError\bin\Debug\net5.0\SQLiteMigrationError.runtimeconfig.json C:\Users\nadar\.dotnet\tools\.store\dotnet-ef\5.0.7\dotnet-e
f\5.0.7\tools\netcoreapp3.1\any\tools\netcoreapp2.0\any\ef.dll database update --assembly D:\Programming\Projects\SQLiteMigrationError\SQLiteMigrationError
\bin\Debug\net5.0\SQLiteMigrationError.dll --startup-assembly D:\Programming\Projects\SQLiteMigrationError\SQLiteMigrationError\bin\Debug\net5.0\SQLiteMigr
ationError.dll --project-dir D:\Programming\Projects\SQLiteMigrationError\SQLiteMigrationError\ --language C# --working-dir D:\Programming\Projects\SQLiteM
igrationError\SQLiteMigrationError --verbose --root-namespace SQLiteMigrationError
Using assembly 'SQLiteMigrationError'.
Using startup assembly 'SQLiteMigrationError'.
Using application base 'D:\Programming\Projects\SQLiteMigrationError\SQLiteMigrationError\bin\Debug\net5.0'.
Using working directory 'D:\Programming\Projects\SQLiteMigrationError\SQLiteMigrationError'.
Using root namespace 'SQLiteMigrationError'.
Using project directory 'D:\Programming\Projects\SQLiteMigrationError\SQLiteMigrationError\'.
Remaining arguments: .
Finding DbContext classes...
Finding IDesignTimeDbContextFactory implementations...
Found IDesignTimeDbContextFactory implementation 'SomeDbContextFactory'.
Found DbContext 'SomeDbContext'.
Finding application service provider in assembly 'SQLiteMigrationError'...
Finding Microsoft.Extensions.Hosting service provider...
No static method 'CreateHostBuilder(string[])' was found on class 'Program'.
No application service provider was found.
Finding DbContext classes in the project...
Using DbContext factory 'SomeDbContextFactory'.
Using context 'SomeDbContext'.
Finding design-time services for provider 'Microsoft.EntityFrameworkCore.Sqlite'...
Using design-time services from provider 'Microsoft.EntityFrameworkCore.Sqlite'.
Finding design-time services referenced by assembly 'SQLiteMigrationError'...
Finding design-time services referenced by assembly 'SQLiteMigrationError'...
Using design-time services from assembly 'Microsoft.EntityFrameworkCore.Sqlite.NetTopologySuite'.
Finding IDesignTimeServices implementations in assembly 'SQLiteMigrationError'...
No design-time services were found.
Migrating using database 'main' on server 'D:\test.db'.
Opening connection to database 'main' on server 'D:\test.db'.
Opened connection to database 'main' on server 'D:\test.db'.
Creating DbCommand for 'ExecuteScalar'.
Created DbCommand for 'ExecuteScalar' (6ms).
Opening connection to database 'main' on server 'D:\test.db'.
Opened connection to database 'main' on server 'D:\test.db'.
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT COUNT(*) FROM "sqlite_master" WHERE "name" = '__EFMigrationsHistory' AND "type" = 'table';
Executed DbCommand (12ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT COUNT(*) FROM "sqlite_master" WHERE "name" = '__EFMigrationsHistory' AND "type" = 'table';
Closing connection to database 'main' on server 'D:\test.db'.
Closed connection to database 'main' on server 'D:\test.db'.
Opening connection to database 'main' on server 'D:\test.db'.
Opened connection to database 'main' on server 'D:\test.db'.
Creating DbCommand for 'ExecuteScalar'.
Created DbCommand for 'ExecuteScalar' (0ms).
Opening connection to database 'main' on server 'D:\test.db'.
Opened connection to database 'main' on server 'D:\test.db'.
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT COUNT(*) FROM "sqlite_master" WHERE "name" = '__EFMigrationsHistory' AND "type" = 'table';
Executed DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT COUNT(*) FROM "sqlite_master" WHERE "name" = '__EFMigrationsHistory' AND "type" = 'table';
Closing connection to database 'main' on server 'D:\test.db'.
Closed connection to database 'main' on server 'D:\test.db'.
Creating DbCommand for 'ExecuteReader'.
Created DbCommand for 'ExecuteReader' (1ms).
Opening connection to database 'main' on server 'D:\test.db'.
Opened connection to database 'main' on server 'D:\test.db'.
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT "MigrationId", "ProductVersion"
FROM "__EFMigrationsHistory"
ORDER BY "MigrationId";
Executed DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT "MigrationId", "ProductVersion"
FROM "__EFMigrationsHistory"
ORDER BY "MigrationId";
A data reader was disposed.
Closing connection to database 'main' on server 'D:\test.db'.
Closed connection to database 'main' on server 'D:\test.db'.
Applying migration '20210617150907_SomeCityIdChanged'.
Opening connection to database 'main' on server 'D:\test.db'.
Opened connection to database 'main' on server 'D:\test.db'.
Beginning transaction with isolation level 'Unspecified'.
Began transaction with isolation level 'Serializable'.
Creating DbCommand for 'ExecuteNonQuery'.
Created DbCommand for 'ExecuteNonQuery' (1ms).
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
ALTER TABLE "SomeBuildings" RENAME COLUMN "SomeCityId" TO "SomeCityIdChanged";
Executed DbCommand (9ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
ALTER TABLE "SomeBuildings" RENAME COLUMN "SomeCityId" TO "SomeCityIdChanged";
Creating DbCommand for 'ExecuteNonQuery'.
Created DbCommand for 'ExecuteNonQuery' (1ms).
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
DROP INDEX "IX_SomeBuildings_SomeCityId";
Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
DROP INDEX "IX_SomeBuildings_SomeCityId";
Creating DbCommand for 'ExecuteNonQuery'.
Created DbCommand for 'ExecuteNonQuery' (0ms).
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE INDEX "IX_SomeBuildings_SomeCityIdChanged" ON "SomeBuildings" ("SomeCityIdChanged");
Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE INDEX "IX_SomeBuildings_SomeCityIdChanged" ON "SomeBuildings" ("SomeCityIdChanged");
Creating DbCommand for 'ExecuteNonQuery'.
Created DbCommand for 'ExecuteNonQuery' (1ms).
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE "ef_temp_SomeBuildings" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_SomeBuildings" PRIMARY KEY AUTOINCREMENT,
    "SomeCityIdChanged" TEXT NOT NULL,
    CONSTRAINT "FK_SomeBuildings_SomeCities_SomeCityIdChanged" FOREIGN KEY ("SomeCityIdChanged") REFERENCES "SomeCities" ("Id") ON DELETE CASCADE
);
SELECT AddGeometryColumn(NULL, 'Location', 0, 'POINT', -1, 0);
AddGeometryColumn() error: argument 1 [table_name] is not of the String type
Executed DbCommand (9ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE "ef_temp_SomeBuildings" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_SomeBuildings" PRIMARY KEY AUTOINCREMENT,
    "SomeCityIdChanged" TEXT NOT NULL,
    CONSTRAINT "FK_SomeBuildings_SomeCities_SomeCityIdChanged" FOREIGN KEY ("SomeCityIdChanged") REFERENCES "SomeCities" ("Id") ON DELETE CASCADE
);
SELECT AddGeometryColumn(NULL, 'Location', 0, 'POINT', -1, 0);
Creating DbCommand for 'ExecuteNonQuery'.
Created DbCommand for 'ExecuteNonQuery' (0ms).
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
INSERT INTO "ef_temp_SomeBuildings" ("Id", "Location", "SomeCityIdChanged")
SELECT "Id", "Location", "SomeCityIdChanged"
FROM "SomeBuildings";
Failed executing DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
INSERT INTO "ef_temp_SomeBuildings" ("Id", "Location", "SomeCityIdChanged")
SELECT "Id", "Location", "SomeCityIdChanged"
FROM "SomeBuildings";
Disposing transaction.
Closing connection to database 'main' on server 'D:\test.db'.
Closed connection to database 'main' on server 'D:\test.db'.
'SomeDbContext' disposed.
Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'table ef_temp_SomeBuildings has no column named Location'.
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteCommand.PrepareAndEnumerateStatements(Stopwatch timer)+MoveNext()
   at Microsoft.Data.Sqlite.SqliteCommand.GetStatements(Stopwatch timer)+MoveNext()
   at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection con
nection)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String connectionString, String contextType
)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
SQLite Error 1: 'table ef_temp_SomeBuildings has no column named Location'.

D:\Programming\Projects\SQLiteMigrationError\SQLiteMigrationError>

Include provider and version information

Database provider: "Microsoft.EntityFrameworkCore.Sqlite.NetTopologySuite" Version="5.0.5"
Target framework: .NET 5.0
Operating system: Windows 10
IDE: Jetbrains Rider

[d79ima]

not sure why you guys are punting this issue, it seems to me like a pretty critical one. Are there any decent workarounds?

[bricelam]

Hmm, it looks like we're just forgetting to set the table here:

efcore/src/EFCore.Sqlite.Core/Migrations/SqliteMigrationsSqlGenerator.cs

Lines 313 to 327 in c146f7a

	var addColumnOperation = new AddColumnOperation
	{
	Name = column.Name,
	ColumnType = column.StoreType,
	IsNullable = column.IsNullable,
	DefaultValue = rebuildContext.AddColumnsDeferred.TryGetValue(column.Name, out var originalOperation)
	&& !originalOperation.IsNullable
	? originalOperation.DefaultValue
	: defaultValue,
	DefaultValueSql = column.DefaultValueSql,
	ComputedColumnSql = column.ComputedColumnSql,
	IsStored = column.IsStored,
	Comment = column.Comment,
	Collation = column.Collation
	};

If you want to add Table = createTableOperation.Name and write a test, I'm happy to work with you on a PR.

[d79ima]

thanks @bricelam, i only have visual studio 2019. So can i clone from a v5.017 tag and put tests in there? In other words, would you be able to work the fixes into a v5.018 release or is v15 dead at this point?

[bricelam]

EF Core 5.0 is out of support. We won't be releasing any more patches for it.

[d79ima]

@bricelam I created a fork and pushed your suggested fix along with a unit test here: https://github.com/d79ima/efcore_issue25116
Please have a look. Would be good to get this at least into EF Core 6.

[bricelam]

@d79ima Looks great! Feel free to submit a PR. (Never mind, looks like you already did.)