Adding transaction wrapper to migration

[arielcloud]

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!

[divega]

@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.:

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.

[arielcloud]

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

[dkutetsky]

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();
   }
}

[Neme12]

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

[bricelam]

@Neme12 I think you're looking for #22616