CODEWITHSUNDEEP
asp.net-mvc-3entity-frameworkunique-key
xymyk
xymyk

Reputation: 41

Entity Framework ObjectContext.SaveChanges fails on unique key column update

Consider very simple database table:

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

Then add 2 rows:

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

Then do 2 tests.

OK:

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();

Fails (before test revert uk values to 1 and 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'

See that ObjectContext.SaveChanges() checks rows in the order of primary index.

Is there any way to force own order?

Upvotes: 1

Views: 2105

Answers (2)

xymyk
xymyk

Reputation: 41

Thanks to Slauma. I've found the solution. The key is to keep elements in several ObjectContext instances.

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

Upvotes: 0

Slauma
Slauma

Reputation: 177153

Unless you call SaveChanges() twice, no, there is no way to control the order of SQL statements Entity Framework will send to the database. You could wrap the multiple SaveChanges() calls into an outer transaction to ensure still a transactional behaviour for the whole operation:

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

Upvotes: 2

Related Questions