How to set the isolation level for Entity Framework CodeFirst Migrations

Question

If you run an entity framework migration (either automatic or explicit) against tables published for SQL Server replication you get the following error:

You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels

There have been questions about this before (here), but they completely fail to address the underlying cause: Entity Framework migration is run at the Serializable isolation level (as clearly shown in the SQL Server profiler).

Which is a safe choice for a structure-changing transaction, but it simply isn't compatible with published sql server tables. Unlike the default READ COMMITED SNAPSHOT level used in the dbContext.SaveChanges() transactions, I haven't yet found a way to actually set a different isolation level for migrations in the code:

• TransactionScope (the classic way to set isolation level for transactions) seems to be ignored during Database.Initialize()

• The recently introduced Database.BeginTransaction(isolationLevel) actually attempts to initialize the database before starting the new transaction, so can't be used.

Known Workarounds

1. Generate all migrations to SQL script. This works, but code-based migrations are a powerful instrument I wouldn't like to miss out on.

2. Use explicit migrations, and start each Up() and Down() method with something like

   Sql("set transaction isolation level read committed");

This works, but is inconvenient and error-prone as developers typically don't work with a replicated database..

Answer 1

Would it help to create your onw Migrator?

internal sealed class Configuration : DbMigrationsConfiguration<SupplierEntities>
{
  public Configuration()
  {
    SetSqlGenerator("System.Data.SqlClient", new SqlMigrator());
  }

  private class SqlMigrator : SqlServerMigrationSqlGenerator
  {
    public override IEnumerable<MigrationStatement> Generate(
      IEnumerable<MigrationOperation> migrationOperations, string providerManifestToken)
    {
      yield return new MigrationStatement { Sql = "set transaction isolation level read committed" };
      foreach (var statement in base.Generate(migrationOperations, providerManifestToken))
        yield return statement;
    }
  }
}

Answer 2

You can write and execute SQL in your migration code:

Before SQL Server 2012 use dynamic SQL:

public override void Up()
{
     Sql("DECLARE @SQL NVARCHAR(4000) = 'ALTER DATABASE '+ DB_NAME() +' SET ALLOW_SNAPSHOT_ISOLATION ON' ; EXEC sp_executeSql @SQL;", true);
}

For SQL Server 2012 or later:

public override void Up()
{
     Sql("ALTER DATABASE CURRENT SET ALLOW_SNAPSHOT_ISOLATION ON",true);
}

Change "ALLOW_SNAPSHOT_ISOLATION" to your isolation level.