Entity Framework 5 Unique Key Violation

Question

Given the following database design:

enter image description here

And using the example data below:

enter image description here

How would I delete the record with NodeId= 3, and then update the line numbers on the remaining records to keep them sequential (in increments of 10)?

The code below highlights my attempt at doing this, but I am currently getting a unique Key Constraint Violation exception when I call SaveChanges().

private void Delete(string taskId, string nodeId)
{
    using (var context = new CustomContext())
    {
        var taskStore = new TaskRepository(context);
        var task = taskStore.GetById(taskId);

        if ( task != null )
        {
            var deleteNode = task.Nodes.FirstOrDefault(n => n.NodeId == nodeId);

            if ( deleteNode != null )
            {
                task.Nodes.Remove(deleteNode);  
            }

            var lineCounter = 0;
            foreach ( var node in task.Nodes )
            {
                node.LineNumber = lineCounter += 10;
            }                  
        }

        context.SaveChanges();
    }                   
}

I assume this has something to do with the sequence of the SQL statements or something not being updated correctly in the context cache. I would expect to see a unique key violation if the line numbers were updated before the conflicting row was updated, but I'm deleting the row first.

This is the process as I see it:

Step 1 (Context, Entities & DB are in sync)

Context                                       Task.Nodes          Database
1 / AAA / 10 (EntityState=Unchanged)          1 / AAA / 10        1 / AAA / 10
2 / AAA / 20 (EntityState=Unchanged)          2 / AAA / 20        2 / AAA / 20
3 / AAA / 30 (EntityState=Unchanged)          3 / AAA / 30        3 / AAA / 30
4 / AAA / 40 (EntityState=Unchanged)          4 / AAA / 40        4 / AAA / 40
5 / AAA / 50 (EntityState=Unchanged)          5 / AAA / 50        5 / AAA / 50

Step 2 (Node with NodeID == 3 removed)

Context                                      Task.Nodes           Database
1 / AAA / 10 (EntityState=Unchanged)         1 / AAA / 10         1 / AAA / 10
2 / AAA / 20 (EntityState=Unchanged)         2 / AAA / 20         2 / AAA / 20
3 / AAA / 30 (EntityState=Deleted=>Detached)  **deleted**         3 / AAA / 30
4 / AAA / 40 (EntityState=Unchanged)         4 / AAA / 40         4 / AAA / 40
5 / AAA / 50 (EntityState=Unchanged)         5 / AAA / 50         5 / AAA / 50

Step 3 (Update line numbers for remaining nodes)

Context                                      Task.Nodes           Database
1 / AAA / 10 (EntityState=Unchanged)         1 / AAA / 10         1 / AAA / 10
2 / AAA / 20 (EntityState=Unchanged)         2 / AAA / 20         2 / AAA / 20
3 / AAA / 30 (EntityState=Deleted=>Detached)  **deleted**         3 / AAA / 30
4 / AAA / 40 (EntityState=Modified)          4 / AAA / 30         4 / AAA / 40
5 / AAA / 50 (EntityState=Modified)          5 / AAA / 40         5 / AAA / 50

Step 4 (context.SaveChanges())

I imagine that this is the SQL in the order of execution. Since the unique constraint only exists in the database, this is where the violation will occur. However, given the sequence below, I do not understand why the violation is occurring as there are no duplicates.

//Begin Transaction
DELETE FROM NODES WHERE NODEID = 3;
UPDATE NODES SET LINENUMBER = 30 WHERE NODEID = 4;
UPDATE NODES SET LINENUMBER = 40 WHERE NODEID = 5;
//End Transaction

The answers to this question appear to agree with me that this is the expected sequence of SQL commands.

I have tried calling SaveChanges() before updating the line numbers as well as after updating the line numbers but this gives the same error.

Calling SaveChanges() within the loop works fine but the statements are not executed in a single transaction as I would like.

I seem to remember there being something in Entity Framework which allowed you to specify the order in which SQL Statements were executed but I don't remember the method name. Searching Google hasn't help so far, I'm still looking.

I may also need to insert records and update the line numbers as appropriate, any solution should bear this in mind.

Thanks, Phil

and if you call SaveChanges 1. After removing node 2. after each lineNumber change (so in foreach loop) ? — Raphaël Althaus, Apr 09 '14 at 16:00
I'll give that a go, however, I would prefer to keep this on once transaction if possible. — philreed, Apr 09 '14 at 16:02
Well, you can put all this in a transaction, SaveChanges in the middle, and commit transaction at the end... — Raphaël Althaus, Apr 09 '14 at 16:10
Putting `SaveChanges()` into the loop appears to have resolved the problem. My understanding was that all of the SQL executed when calling `SaveChanges()` runs in a single transaction. How would I update this code to make sure that only a single transaction is used? — philreed, Apr 09 '14 at 16:28
Well, you can look at this for example : http://stackoverflow.com/questions/13592015/ef5-code-first-transactionscope-without-escalating-to-msdtc — Raphaël Althaus, Apr 09 '14 at 16:30
The answer in that question states that it would be best to call `SaveChanges()` once and allow Entity Framework to handle the transactions, which is what I would prefer to do. I would be interested in knowing why `SaveChanges()` has to be included in the loop. There must be a way to get the context to realise that the record causing the violation has been removed, without calling `SaveChanges()` multiple times. I will try using `TransactionScope()` and see what happens. — philreed, Apr 09 '14 at 16:34
It's not the removed entity which causes problem. I'll put this in an answer, it will be easier to read... — Raphaël Althaus, Apr 09 '14 at 16:40

Entity Framework 5 Unique Key Violation

0 Answers0