EF Code First and MSSQL Replication. Important!

PROBLEM:

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

RESOLUTION:

Create your own Migrator:

using System.Data.Entity.Migrations;
using System.Data.Entity.Migrations.Sql;
using System.Data.Entity.Migrations.Model;
using System.Data.Entity.SqlServer;
using System.Collections.Generic;

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

https://technet.microsoft.com/en-us/library/ms151196.aspx

https://habrahabr.ru/post/130320/