Efcore: Adding transaction wrapper to migration

Created on 15 Aug 2016 · 5Comments · Source: dotnet/efcore

It look like no built-in configuration exists to execute update-database transactionally (so that the updates must succeed or fail as a complete unit)

Is not it is a basic and useful functionality? thanks!

closed-question

Source

arielcloud

Most helpful comment

EF Core generates transaction for each individual migration - to check, just use Package Manager Console:

Update-Database ... -verbose

But you could get generated sql and run it with wrapping it in transaction manually.

Just an working concept - there is no additional logic to not execute empty sql script, running migrations using from & to migrations, and other corner cases:

using (var dbContext = new DbContext())
{
   var migrator = dbContext.GetService<IMigrator>();      

   // you could specify here fromMigration & toMigration instead of nulls
   var migrationSql = migrator.GenerateScript(null, null, true);

   //need this, because script contains "GO" statement for each migration that leads to execution error
   migrationSql = migrationSql.Replace("\r\nGO\r\n", "");

   //run all migrations in one transaction
   using (var transaction = dbContext.Database.BeginTransaction(IsolationLevel.ReadCommitted))
   {
       dbContext.Database.ExecuteSqlCommandAsync(migrationSql, cancellationToken);
       transaction.Commit();
   }
}

dkutetsky on 29 Jan 2019

👍6

All 5 comments

@arielcloud individual migrations are (when they can) wrapped into their own transactions and that is by design. Anyway, @bricelam and I talked about this and we believe that if you invoke migrations programmatically and you wrap the call into a transaction also created on the context programmatically it is possible that will give you want you are asking for, e.g.:

C# using(var context = new MyContext()) { using(var transaction = context.Database.BeginTransaction()) { context.Database.Migrate(); transaction.Commit(); } }

The other (potentially simpler option) is to obtain the migration script and then execute it inside a transaction.

divega on 16 Aug 2016

👍4

Before I opened this issue I searched it in google, and the only relevant result I saw was this one that cause me to think that no transaction exists by default, so maybe it'll be better to document this featuew somewhere

arielcloud on 16 Aug 2016

EF Core generates transaction for each individual migration - to check, just use Package Manager Console:

Update-Database ... -verbose

But you could get generated sql and run it with wrapping it in transaction manually.

Just an working concept - there is no additional logic to not execute empty sql script, running migrations using from & to migrations, and other corner cases:

using (var dbContext = new DbContext())
{
   var migrator = dbContext.GetService<IMigrator>();      

   // you could specify here fromMigration & toMigration instead of nulls
   var migrationSql = migrator.GenerateScript(null, null, true);

   //need this, because script contains "GO" statement for each migration that leads to execution error
   migrationSql = migrationSql.Replace("\r\nGO\r\n", "");

   //run all migrations in one transaction
   using (var transaction = dbContext.Database.BeginTransaction(IsolationLevel.ReadCommitted))
   {
       dbContext.Database.ExecuteSqlCommandAsync(migrationSql, cancellationToken);
       transaction.Commit();
   }
}

dkutetsky on 29 Jan 2019

👍6

@divega Why is this closed? I couldn't find any way to make dotnet ef database update run everything in a single transaction.