实体框架 ObjectContext.SaveChanges 在唯一键列更新时失败

Question

考虑非常简单的数据库表：

CREATE TABLE UkTest(
  id int NOT NULL,
  uk int NOT NULL
)
Primary Key on id
Unique Key on uk

然后添加 2 行：

INSERT INTO UkTest (id,uk) VALUES(1,1);
INSERT INTO UkTest (id,uk) VALUES(2,2);

然后进行 2 个测试。

正常：

var db =  new Database1Entities();
var element1 = db.UkTest.FirstOrDefault(e => e.id == 1);
var element2 = db.UkTest.FirstOrDefault(e => e.id == 2);

element1.uk = 0;
element2.uk = 1;  // overrides previous element1.uk value
var count = db.SaveChanges();

失败（在测试将 uk 值恢复为 1 和 2 之前！）：

var db =  new Database1Entities();
var element1 = db.UkTest.FirstOrDefault(e => e.id == 1);
var element2 = db.UkTest.FirstOrDefault(e => e.id == 2);

element2.uk = 0;
element1.uk = 2;  // overrides previous element2.uk value
var count = db.SaveChanges();
// Cannot insert duplicate key row in object 'dbo.UkTest' with unique index 'UK_UkTest'

请参阅 ObjectContext.SaveChanges() 按主索引的顺序检查行。

有什么办法强制自己下单吗？

Answer 1

除非您调用 SaveChanges() 两次，否则无法控制实体框架发送到数据库的 SQL 语句的顺序。您可以将多个 SaveChanges() 调用包装到外部事务中，以确保整个操作仍然是事务行为：

using (var scope = new TransactionScope())
{
    using (var db = new Database1Entities())
    {
        var element1 = db.UkTest.FirstOrDefault(e => e.id == 1);
        var element2 = db.UkTest.FirstOrDefault(e => e.id == 2);

        element2.uk = 0;
        db.SaveChanges();

        element1.uk = 2;
        db.SaveChanges();
    }
    scope.Complete();
}

Answer 2

感谢斯劳玛。我找到了解决方案。关键是将元素保留在多个 ObjectContext 实例中。

public class SavingElementsWithTransactionInOwnOrder
{
    public void SaveElements ()
    {
        var db = new Database1Entities();
        var element1 = db.UkTest.FirstOrDefault(e => e.id == 1);
        element1.db = db;

        db = new Database1Entities();
        var element2 = db.UkTest.FirstOrDefault(e => e.id == 2);
        element2.db = db;

        element2.uk = 0;
        element1.uk = 2;

        var scope = new TransactionScope();
        try{
            element2.db.SaveChanges();
            element1.db.SaveChanges();
            scope.Complete();
        }
        finally{
            scope.Dispose();
        }
    }
}

public partial class UkTest
{
    public Database1Entities db { get; set; }
}