Entity Framework 6 change tracker issuing statements in the wrong order

Question

Problem description

I have a method that syncs changes to graphs of entities to the database by comparing the received entities with the corresponding database rows and then updates the ChangeTracker with each of the objects correct State.

This generally works well, however when there are unique indexes, it all falls apart since the order of statements becomes important.

In this example i first want to remove the row DeleteUpdateOrders where Unique: "A" and then modify another row have Unique: "A". If the DELETE is performed before the UPDATE this would work, however the statements are executed the other way around.

Is there a way to gain control of the order Entity Framework 6 issues the statements?

Database script (SQL Server):

CREATE DATABASE ChangeTracker
GO

USE ChangeTracker

CREATE TABLE DeleteUpdateOrders
(
    DeleteUpdateOrderId INT NOT NULL PRIMARY KEY,
    [Unique] CHAR NOT NULL
)

CREATE UNIQUE INDEX IX_DeleteUpdateOrders_Unique 
ON DeleteUpdateOrders ([Unique])

INSERT INTO DeleteUpdateOrders VALUES (1, 'A')
INSERT INTO DeleteUpdateOrders VALUES (2, 'B')

Minimal repro:

public void Main()
{
    var context = new TestContext();

    context.Database.Log = Console.Write;

    var databaseRows = context.DeleteUpdateOrders.AsNoTracking().ToArray();

    // Delete the row with Unqiue = "A"
    var rowToDelete = databaseRows.Single(r => r.DeleteUpdateOrderId == 1); 
    context.Entry(rowToDelete).State = EntityState.Deleted;

    // Get the existing row with Unique = "B" and set Unique = "A"
    var rowToModify = databaseRows.Single(r => r.DeleteUpdateOrderId == 2); 
    rowToModify.Unique = "A";
    context.Entry(rowToModify).State = EntityState.Modified;

    // context.ChangeTracker at this stage contains:
    // Modified { DeleteUpdateOrderId: 2, Unique: "A" }
    // Deleted  { DeleteUpdateOrderId: 1, Unique: "A" }

    // Console output indicates that the UPDATE was executed before
    // the DELETE statement, and the innermost SqlException thrown on 
    // context.SaveChanges is:
    //
    // Cannot insert duplicate key row in object 'dbo.DeleteUpdateOrders' with 
    // unique index 'IX_DeleteUpdateOrders_Unique'.The duplicate key value is (A).
    context.SaveChanges();
}

public class TestContext : DbContext
{
    public IDbSet<DeleteUpdateOrder> DeleteUpdateOrders { get; set; }

    public TestContext() : 
        base("Server=.; Database=ChangeTracker; Integrated Security=SSPI")
    {
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        Database.SetInitializer<TestContext>(null);

        // Informing EF6 about the index does not seem to make a difference ...
        modelBuilder
            .Entity<DeleteUpdateOrder>()
            .HasIndex(entity => entity.Unique)
            .IsUnique();
    }
}

public class DeleteUpdateOrder
{
    public int DeleteUpdateOrderId { get; set; }
    
    public string Unique { get; set; }
}

Answer 1

AFAIK there is no way to tell EF an explicit order of operations. The obvious way to force it is with 2x SaveChanges calls, but this would require an explicit transaction to ensure both are committed together or rolled back.

EF will enforce an order of operations whenever navigation properties are involved. Depending on the nature of this unique value, if the value itself has meaning you could introduce a table and shadow navigation property.

For instance if we have a UniqueValues table with this UniqueValue as the PK, then we can configure a relationship as:

modelBuilder
    .Entity<DeleteUpdateOrder>()
    .HasOne<UniqueValue>("UniqueValue") // Name doesn't matter, but needs to be different to the "Unique" FK name.
    .WithOne()
    .HasForeignKey<DeleteUpdateOrder>(entity => entity.Unique)
    .IsRequired();

Now when you run through the scenario you can:

var rowToDelete = context.DeleteUpdateOrders.Single(r => r.DeleteUpdateOrderId == 1); 
context.DeleteUpdateOrders.Remove(rowToDelete);

// Get the existing row with Unique = "B" and set Unique = "A"
var rowToModify = context.DeleteUpdateOrders.Single(r => r.DeleteUpdateOrderId == 2); 
rowToModify.Unique = "A";
context.SaveChanges();

I tested this with a few different runs with delete/insert, insert/update etc. Order of operations didn't matter, EF looks to resolve the order correctly when it knows there is a dependent relationship.

The caveat of this approach is that it does need the FK and association table for the unique values so this wouldn't work if you just wanted a unique value that could be checked and inserted if not present. It would require an insert into the UniqueValues table for new values before a new Order can be inserted.