EF Core 9 migration behaviour change causes an exception with surrounding user transaction

[DvdKhl]

We use multiple DbContexts within our application, which need to be either all fully migrated or none of them in case of an exception. To achieve this we have surrounded everything in a transaction during the migration.
With EF8 this worked well, but with EF9 we're getting the exception System.NotSupportedException: 'User transaction is not supported with a TransactionSuppressed migrations or a retrying execution strategy.'

We found the related(?) issue https://github.com/dotnet/efcore/issues/35096 but I can't apply the solution in it since it doesn't fail at a .Sql() call. And we're at least not changing any retrying strategy and I'm not sure how to disable it if it is enabled.

Minimal repo:

Add the Microsoft.EntityFrameworkCore.SqlServer package and notice that this code works in EF8 but throws the exception above in EF9.

using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Migrations;
using System.ComponentModel.DataAnnotations;

using var conn = new SqlConnection();
conn.ConnectionString = $"Data Source=localhost;Initial Catalog=Main;User ID=username;Password=userpassword;Trust Server Certificate=True";
await conn.OpenAsync();

using (var tran = conn.BeginTransaction()) {
  using (var aContext = new AContext()) {
    aContext.Database.SetDbConnection(conn);
    using var aCtxTran = await aContext.Database.UseTransactionAsync(tran);

    await aContext.Database.MigrateAsync();
  }

  using (var bContext = new BContext()) {
    bContext.Database.SetDbConnection(conn);
    using var bCtxTran = await bContext.Database.UseTransactionAsync(tran);

    await bContext.Database.MigrateAsync();
  }

  await tran.CommitAsync();
}

public class AContext : DbContext { public DbSet<A> AEntites { get; set; } = null!; protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) => optionsBuilder.UseSqlServer(); }
public class A { [Key] public int Id { get; set; } }
public class BContext : DbContext { public DbSet<B> BEntites { get; set; } = null!; protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) => optionsBuilder.UseSqlServer(); }
public class B { [Key] public int Id { get; set; } }

#region Migrations
#nullable disable

namespace MultiEFContextMigration.Migrations {
  /// <inheritdoc />
  public partial class AMigration : Migration {
    /// <inheritdoc />
    protected override void Up(MigrationBuilder migrationBuilder) {
      migrationBuilder.CreateTable(
        name: "AEntites",
        columns: table => new {
          Id = table.Column<int>(type: "int", nullable: false)
            .Annotation("SqlServer:Identity", "1, 1")
        },
        constraints: table => {
          table.PrimaryKey("PK_AEntites", x => x.Id);
        });
    }

    /// <inheritdoc />
    protected override void Down(MigrationBuilder migrationBuilder) {
      migrationBuilder.DropTable(
        name: "AEntites");
    }
  }
}

#nullable disable

namespace MultiEFContextMigration.Migrations {
  [DbContext(typeof(AContext))]
  [Migration("20241116225124_AMigration")]
  partial class AMigration {
    /// <inheritdoc />
    protected override void BuildTargetModel(ModelBuilder modelBuilder) {
#pragma warning disable 612, 618
      modelBuilder
        .HasAnnotation("ProductVersion", "9.0.0")
        .HasAnnotation("Relational:MaxIdentifierLength", 128);

      SqlServerModelBuilderExtensions.UseIdentityColumns(modelBuilder);

      modelBuilder.Entity("A", b => {
        b.Property<int>("Id")
          .ValueGeneratedOnAdd()
          .HasColumnType("int");

        SqlServerPropertyBuilderExtensions.UseIdentityColumn(b.Property<int>("Id"));

        b.HasKey("Id");

        b.ToTable("AEntites");
      });
#pragma warning restore 612, 618
    }
  }
}

#nullable disable

namespace MultiEFContextMigration.Migrations {
  [DbContext(typeof(AContext))]
  partial class AContextModelSnapshot : ModelSnapshot {
    protected override void BuildModel(ModelBuilder modelBuilder) {
#pragma warning disable 612, 618
      modelBuilder
        .HasAnnotation("ProductVersion", "9.0.0")
        .HasAnnotation("Relational:MaxIdentifierLength", 128);

      SqlServerModelBuilderExtensions.UseIdentityColumns(modelBuilder);

      modelBuilder.Entity("A", b => {
        b.Property<int>("Id")
          .ValueGeneratedOnAdd()
          .HasColumnType("int");

        SqlServerPropertyBuilderExtensions.UseIdentityColumn(b.Property<int>("Id"));

        b.HasKey("Id");

        b.ToTable("AEntites");
      });
#pragma warning restore 612, 618
    }
  }
}

#nullable disable

namespace MultiEFContextMigration.Migrations.B {
  /// <inheritdoc />
  public partial class BMigration : Migration {
    /// <inheritdoc />
    protected override void Up(MigrationBuilder migrationBuilder) {
      migrationBuilder.CreateTable(
        name: "BEntites",
        columns: table => new {
          Id = table.Column<int>(type: "int", nullable: false)
            .Annotation("SqlServer:Identity", "1, 1")
        },
        constraints: table => {
          table.PrimaryKey("PK_BEntites", x => x.Id);
        });
    }

    /// <inheritdoc />
    protected override void Down(MigrationBuilder migrationBuilder) {
      migrationBuilder.DropTable(
        name: "BEntites");
    }
  }
}

#nullable disable

namespace MultiEFContextMigration.Migrations.B {
  [DbContext(typeof(BContext))]
  [Migration("20241116225139_BMigration")]
  partial class BMigration {
    /// <inheritdoc />
    protected override void BuildTargetModel(ModelBuilder modelBuilder) {
#pragma warning disable 612, 618
      modelBuilder
        .HasAnnotation("ProductVersion", "9.0.0")
        .HasAnnotation("Relational:MaxIdentifierLength", 128);

      SqlServerModelBuilderExtensions.UseIdentityColumns(modelBuilder);

      modelBuilder.Entity("B", b => {
        b.Property<int>("Id")
          .ValueGeneratedOnAdd()
          .HasColumnType("int");

        SqlServerPropertyBuilderExtensions.UseIdentityColumn(b.Property<int>("Id"));

        b.HasKey("Id");

        b.ToTable("BEntites");
      });
#pragma warning restore 612, 618
    }
  }
}

#nullable disable

namespace MultiEFContextMigration.Migrations.B {
  [DbContext(typeof(BContext))]
  partial class BContextModelSnapshot : ModelSnapshot {
    protected override void BuildModel(ModelBuilder modelBuilder) {
#pragma warning disable 612, 618
      modelBuilder
        .HasAnnotation("ProductVersion", "9.0.0")
        .HasAnnotation("Relational:MaxIdentifierLength", 128);

      SqlServerModelBuilderExtensions.UseIdentityColumns(modelBuilder);

      modelBuilder.Entity("B", b => {
        b.Property<int>("Id")
          .ValueGeneratedOnAdd()
          .HasColumnType("int");

        SqlServerPropertyBuilderExtensions.UseIdentityColumn(b.Property<int>("Id"));

        b.HasKey("Id");

        b.ToTable("BEntites");
      });
#pragma warning restore 612, 618
    }
  }
}
#endregion

Stack traces:

Unhandled exception. System.NotSupportedException: User transaction is not supported with a TransactionSuppressed migrations or a retrying execution strategy.
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQueryAsync(IReadOnlyList`1 migrationCommands, IRelationalConnection connection, MigrationExecutionState executionState, Boolean commitTransaction, Nullable`1 isolationLevel, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Migrations.HistoryRepository.Microsoft.EntityFrameworkCore.Migrations.IHistoryRepository.CreateIfNotExistsAsync(CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.<>c.<<MigrateAsync>b__22_0>d.MoveNext()
--- End of stack trace from previous location ---
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.<>c.<<MigrateAsync>b__22_0>d.MoveNext()
--- End of stack trace from previous location ---
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.MigrateAsync(String targetMigration, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.MigrateAsync(String targetMigration, CancellationToken cancellationToken)
   at Program.<Main>$(String[] args) in E:\source\MultiEFContextMigration\MultiEFContextMigration\Program.cs:line 16
   at Program.<Main>(String[] args)

Include provider and version information

EF Core version: 9.0.0 Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: .NET 9.0 Operating system: Win11 23H2 (OS Build 22631.4317) IDE: Visual Studio 2022 17.12.0

[ajcvickers]

Note for team: this is assigned to @maumar because Migrations, but it looks directly related to @AndriySvyryd's work in 9. Should we assign issues like this to @AndriySvyryd?

[cincuranet]

We can assign both and later one will be unassigned.

BTW I think there's similar story with #35133.

[Appli4Ever]

Is this a bug or intended behavior of "Protection against concurrent migrations" or "Warn when multiple migration operations can't be run inside a transaction" in this article: https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-9.0/whatsnew#migrations?

I am getting the same exception when executing integration test. But I am not using multiple DbContext.

My exception occurs when executing test simultaneously and all of them migrate the database before executing.

[cincuranet]

@Appli4Ever Concurrent migrations and transactions are two different, unrelated, topics.

My exception occurs when executing test simultaneously and all of them migrate the database before executing.

What exception? Do you see migration lock being taken?

[Appli4Ever]

@cincuranet

Message:  System.NotSupportedException : User transaction is not supported with a TransactionSuppressed migrations or a retrying execution strategy.

Stack Trace: 

MigrationCommandExecutor.ExecuteNonQuery(IReadOnlyList`1 migrationCommands, IRelationalConnection connection, MigrationExecutionState executionState, Boolean commitTransaction, Nullable`1 isolationLevel)
IHistoryRepository.CreateIfNotExists()
<>c.<Migrate>b__20_0(DbContext _, Migrator migrator)
SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
Migrator.Migrate(String targetMigration)
SqlServerContext`1.InitContext(String connectionString, Boolean useTransaction, String key)
SqlServerContext`1.ctor(String name, String connectionString, Boolean useTransaction, Object constructorParameter, String migrationAssembly)
<>c__DisplayClass0_0`1.<AddContext>b__0(String name, String conn, Boolean useTransaction, Object constructParams)
TestContextFactory`1.Get(TestDbType dbType, String name, String conn, Boolean useTransaction, Object constructorParaemter)
DbContextBuilder`1.Build()

I have to look into it further, maybe this is unrelated but I am getting the same exception as @DvdKhl.

What exception? Do you see migration lock being taken?

Not seeing any migration locks. These should also not occur since im executing the migrations in a System.Threading.Lock:

private static readonly Lock Sync = new();

private static IDbContextTransaction sharedTransaction;
... 

lock (Sync)
{
    ctx.Database.UseTransaction(sharedTransaction.GetDbTransaction());

    ctx.Database.Migrate();

    ctx.Database.BeginTransaction(IsolationLevel.ReadCommitted);
}

(Executing the tests one-by-one works)

[AndriySvyryd]

One of the migrations contains an operation that cannot be executed in a transaction. It could be a custom operation like

migrationBuilder.Sql("CREATE DATABASE TransactionSuppressed;", suppressTransaction: true)

Or an operation that impacts a memory-optimized table.

In this case you cannot use an external transaction.

[Dreamescaper]

@AndriySvyryd I have only 3 auto-generated migrations, looking like this:

1

```csharp migrationBuilder.CreateTable( name: "EntityEvents", columns: table => new { EventId = table.Column(type: "uuid", nullable: false), CreatedDate = table.Column(type: "timestamp with time zone", nullable: false), EntityId = table.Column(type: "text", nullable: false), EventData = table.Column(type: "jsonb", nullable: false) }, constraints: table => { table.PrimaryKey("PK_EntityEvents", x => x.EventId); }); ```

2

```csharp migrationBuilder.CreateTable( name: "EntitySnapshots", columns: table => new { EntityId = table.Column(type: "text", nullable: false), DeliveryDate = table.Column(type: "date", nullable: false), LimitDate = table.Column(type: "date", nullable: false), Status = table.Column(type: "integer", nullable: false) }, constraints: table => { table.PrimaryKey("PK_EntitySnapshots", x => x.EntityId); }); migrationBuilder.CreateIndex( name: "IX_EntitySnapshots_DeliveryDate", table: "EntitySnapshots", column: "DeliveryDate"); migrationBuilder.CreateIndex( name: "IX_EntitySnapshots_LimitDate", table: "EntitySnapshots", column: "LimitDate"); migrationBuilder.CreateIndex( name: "IX_EntitySnapshots_Status", table: "EntitySnapshots", column: "Status"); ```

3

```csharp migrationBuilder.CreateIndex( name: "IX_EntityEvents_EntityId", table: "EntityEvents", column: "EntityId"); ```

Could you suggest which one of those causes this issue?