如何为实体框架 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 或REPEATABLE READ 隔离级别

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

之前有关于此的问题(here)，但它们完全无法解决根本原因:Entity Framework 迁移在 Serializable 隔离级别运行(如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 表不兼容.与 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.

使用显式迁移，并以类似

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

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