如何为Entity Framework CodeFirst迁移设置隔离级别 [英] How to set the isolation level for Entity Framework CodeFirst Migrations

问题描述

如果您针对SQL Server复制发布的表运行实体框架迁移（自动或显式），则会收到以下错误：

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

您只能在READ COMMITTED中指定READPAST锁或
REPEATABLE READ隔离级别

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

有关于此的问题之前（此处） ，但是它们完全无法解决潜在的原因：实体框架迁移运行在可序列化隔离级别（如SQL Server剖析器中所示） ）。

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

哪个是结构更改事务的安全选择，但它与发布的sql server表不兼容。与$ code> dbContext.SaveChanges（）事务中使用的默认READ COMMITED SNAPSHOT级别不同，我还没有找到一种方法来为代码中的迁移设置不同的隔离级别：

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 （为交易设置隔离级别的经典方式）似乎在 Database.Initialize（）

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

最近推出的 Database.BeginTransaction（isolationLevel）实际上尝试在开始新的事务之前初始化数据库，所以不能使用。

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

已知的解决方法

1. 生成所有迁移到SQL脚本。这是有效的，但基于代码的迁移是一个强大的工具，我不想错过。

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.

使用显式迁移，并启动每个 Up（）和 Down（）方法与

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

Sql （设置事务隔离级别读取提交）;

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

推荐答案

是否有助于创建您的onw Migrator？

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

这篇关于如何为Entity Framework CodeFirst迁移设置隔离级别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！