Swapping values with unique constraint in Entity Framework

Question

I have a unique constraint on a Navigations table's column called Index. I have two Navigation entities and I want to swap their Index values.

When I call db.SaveChanges it throws an exception indicating that a unique constraint was violated. It seems EF is updating one value and then the other, thus violating the constraint.

Shouldn't it be updating them both in a transaction and then trying to commit once the values are sorted out and not in violation of the constraint?

Is there a way around this without using temporary values?

Answer 1

You could run a custom SQL Query to swap the values, like this:

update Navigation
set valuecolumn = 
        case 
            when id=1 then 'value2' 
            when id=2 then 'value1'
        end
where id in (1,2)

However, Entity Framework cannot do that, because it's outside the scope of an ORM. It just executes sequential update statements for each altered entity, like Ladislav described in his answer.

Another possibility would be to drop the UNIQUE constraint in your database and rely on the application to properly enforce this constraint. In this case, the EF could save the changes just fine, but depending on your scenario, it may not be possible.

Answer 2

It is not problem of EF but the problem of SQL database because update commands are executed sequentially. Transaction has nothing to do with this - all constrains are validated per command not per transaction. If you want to swap unique values you need more steps where you will use additional dummy values to avoid this situation.

Answer 3

There are a few approaches. Some of them are covered in other answers and comments but for completeness, I will list them out here (note that this is just a list that I brainstormed and it might not be all that 'complete').

1. Perform all of the updates in a single command. See W0lf's answer for an example of this.
2. Do two sets of updates - one to swap all of the values to the negative of the intended value and then a second to swap them from negative to positive. This is working on the assumptions that negative values are not prevented by other constraints and that they are not values that records other than those in a transient state will have.
3. Add an extra column - IsUpdating for example - set it to true in the first set of updates where the values are changed and then set it back to false in a second set of updates. Swap the unique constraint for a filtered, unique index which ignores records where IsUpdating is true.
4. Remove the constraint and deal with duplicate values.