Document how to avoid issues with SQLite table rebuild in migrations

[berhir]

We are using EF Core code first with an SQLite database. Recently I made a change to a model, added a migration, applied it, and everything looked good. But after some time, I noticed that our application doesn't behave as expected and I found some errors in the logs.
After some investigation it turned out that the migration rebuilt the table in the SQLite DB. And all the customizations that we added in previous migrations were lost. All triggers were dropped and a foreign key constraint I removed in a previous migration was added back.

The problematic migration contained a single AlterColumn statement and it was not obvious that this would have such a big impact. To remove the unwanted foreign key constraint, I had to add a new migration and write all the table rebuild code manually.

I was really surprised that the table rebuild happened magically in the background without any notice.
To make it more transparent and easier to customize, I suggest adding all the code for the table rebuild to the migration.
It would make it clear what will happen when the migration gets applied, and it would allow us to customize the migration as needed.

[ajcvickers]

/cc @bricelam

[bricelam]

maybe you can add a comment there why this is not possible/planned to implement and if there are workarounds for triggers, thanks

Table rebuilds area an implementation detail of the SQLite provider. The scaffolded migration represents the high-level operations that need to happen to bring the database schema up to date with the domain model. It's up to the provider to determine how to apply these operations.

Many DDL operations are not supported by SQLite, so we made a compromise to use table rebuilds to apply several of the operations. While this is extremely convenient for most applications, the compromise is that it assumes your domain/EF model fully represents the database schema. As you've found out, anything that is not captured in the model like triggers or removed foreign key constraints will be lost.

The decision to perform a table rebuild is made while generating the SQL for a migration. This happens significantly later than when the migration is scaffolded. This enables users to hand-write operations in a migration using the higher-level migration builder APIs and not have to worry about how they will be applied.

We very strongly encourage everyone to review the generated SQL of their migrations. This can help them catch subtle issues like this earlier. The correct fix in this case would be to re-add the triggers and re-remove the foreign key in a subsequent migration.

I think the real fix you would want is for us to implement dotnet/efcore#10770 and dotnet/efcore#15854 so we could use this additional information when rebuilding the table.

dotnet/efcore#329 (comment) shows how to perform a table rebuild manually inside a migration.

[berhir]

Thank you for the detailed explanation.
As migrations are provider agnostic anyway, I still think it would be nice to have the possibility to scaffold the table rebuild statements.

The problem with the suggested fix to "re-remove the foreign key in a subsequent migration" is that removing the foreign key requires another table rebuild in SQLite.
But I see now that the MigrationBuilder has a DropForeignKey operation that should do the table rebuild automatically, I guess.

The main issue is that it's quite easy to forget about it. We are using Laraue.EfCoreTriggers to manage our triggers. Is there a way to automatically detect if a migration will require a table rebuild so that the triggers can be re-added in the migration?

[bricelam]

Is there a way to automatically detect if a migration will require a table rebuild

Hmm, it's not very pretty, but you could generate the SQL and check for tables starting with "ef_temp_".

using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Migrations;
using Microsoft.EntityFrameworkCore.Storage;

using var context = new MyDbContext();
var migrationsAssembly = context.GetService<IMigrationsAssembly>();
var activeProvider = context.GetService<IDatabaseProvider>().Name;
var modelRuntimeInitializer = context.GetService<IModelRuntimeInitializer>();
var migrationsSqlGenerator = context.GetService<IMigrationsSqlGenerator>();

var lastMigration = migrationsAssembly.CreateMigration(migrationsAssembly.Migrations.Last().Value, activeProvider);

var commandList = migrationsSqlGenerator.Generate(
    lastMigration.UpOperations,
    modelRuntimeInitializer.Initialize(lastMigration.TargetModel));

var containsRebuild = commandList.Any(c => c.CommandText.Contains("ef_temp_"));