Applying migration fails with error

Question

Doing step 6 (dotnet ef database update --context PizzaContext) under Change the model and update the database schema on this page results in output ending with the following error. I have no idea how to proceed.

System.InvalidOperationException: An error was generated for warning 'Microsoft.EntityFrameworkCore.Migrations.NonTransactionalMigrationOperationWarning': The migration operation 'PRAGMA foreign_keys = 0;
' from migration 'ModelRevisions' cannot be executed in a transaction. If the app is terminated or an unrecoverable error occurs while this operation is being executed then the migration will be left in a partially applied state and would need to be reverted manually before it 
can be applied again. Create a separate migration that contains just this operation. This exception can be suppressed or logged by passing event ID 'RelationalEventId.NonTransactionalMigrationOperationWarning' to the 'ConfigureWarnings' method in 'DbContext.OnConfiguring' or 'AddDbContext'.
   at Microsoft.EntityFrameworkCore.Diagnostics.EventDefinition`2.Log[TLoggerCategory](IDiagnosticsLogger`1 logger, TParam1 arg1, TParam2 arg2)
   at Microsoft.EntityFrameworkCore.Diagnostics.RelationalLoggerExtensions.NonTransactionalMigrationOperationWarning(IDiagnosticsLogger`1 diagnostics, IMigrator migrator, Migration migration, MigrationCommand command)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.<>c__DisplayClass24_2.b__2()
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.MigrateImplementation(DbContext context, String targetMigration, MigrationExecutionState state, Boolean useTransaction)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.<>c.b__20_1(DbContext c, ValueTuple`4 s)
   at Microsoft.EntityFrameworkCore.Storage.NonRetryingExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   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)
An error was generated for warning 'Microsoft.EntityFrameworkCore.Migrations.NonTransactionalMigrationOperationWarning': The migration operation 'PRAGMA foreign_keys = 0;
' from migration 'ModelRevisions' cannot be executed in a transaction. If the app is terminated or an unrecoverable error occurs while this operation is being executed then the migration will be left in a partially applied state and would need to be reverted manually before it 
can be applied again. Create a separate migration that contains just this operation. This exception can be suppressed or logged by passing event ID 'RelationalEventId.NonTransactionalMigrationOperationWarning' to the 'ConfigureWarnings' method in 'DbContext.OnConfiguring' or 'AddDbContext'.

Answer

Hi geoff-m,

The error you encountered occurs because SQLite does not support executing certain operations, like PRAGMA foreign_keys = 0, within a transaction. EF Core, by default, wraps all migration steps in a transaction to ensure consistency, but SQLite requires some operations to run outside of a transaction.

Here are the steps to resolve the issue:

Option 1: Disable Transactions for this Migration:

To allow the migration to succeed, you can modify the generated migration file so that it doesn’t use transactions for this specific operation. Follow these steps:

1.Open the migration file (e.g., ModelRevisions.cs) located in the Migrations folder.

2.In the migration class (likely named ModelRevisions), override the Sql method by adding the following code:

protected override void Up(MigrationBuilder migrationBuilder) 
{
    // Disable foreign key constraints temporarily
    migrationBuilder.Sql("PRAGMA foreign_keys = OFF;", suppressTransaction: true);
    // Add your migration logic here
    migrationBuilder.CreateTable(
        name: "PizzaTopping",
        columns: table => new
        {
            PizzasId = table.Column(type: "INTEGER", nullable: false),
            ToppingsId = table.Column(type: "INTEGER", nullable: false)
        },
        constraints: table =>
        {
            table.PrimaryKey("PK_PizzaTopping", x => new { x.PizzasId, x.ToppingsId });
        }
    );
    // Re-enable foreign key constraints
    migrationBuilder.Sql("PRAGMA foreign_keys = ON;", suppressTransaction: true);
}

This code disables foreign key constraints temporarily, allowing the migration to run successfully without being wrapped in a transaction.

Option 2: Suppress the Warning

If you prefer to leave the migration as-is and simply ignore the warning, you can modify the PizzaContext class to suppress the warning.

1.Open the PizzaContext.cs file in the Data folder.

2.In the OnConfiguring method (or in AddDbContext), add the following configuration:

optionsBuilder.ConfigureWarnings(warnings =>
    warnings.Ignore(RelationalEventId.NonTransactionalMigrationOperationWarning));

Please feel free to contact us if you have any additional questions.

If you have found the answer provided to be helpful, please click on the "Accept answer/Upvote" button so that it is useful for other members in the Microsoft Q&A community.

Thank you.

Share via

Applying migration fails with error

1 answer

Your answer