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?

you need temp values here, an update is a self contained operation. so you will always get to a constraint violation, the only other option is to disable constraints for the operation. — undefined, Apr 19 '12 at 11:07

score 12 · Accepted Answer · answered Apr 19 '12 at 11:05

12

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.

answered Apr 19 '12 at 11:05

Ladislav Mrnka

360,892
59
660
670

1

This seems dangerous because of the potential for issues to come up with concurrent requests utilizing the same dummy value, or perhaps a dummy value which also violates the constraint. Is there a "best practice" for this situation that you know of? – Michael J. Gray Apr 19 '12 at 11:07
1

You can also delete old records and create new ones. I would try to avoid this in the first place. It is a situation where unique constraint doesn't fit into your application logic needs. – Ladislav Mrnka Apr 19 '12 at 11:12
Fair enough. I'm just going to remove the unique constraint from the database. It won't cause any problems if there are duplicates. If there are, they'll just be in an unreliable order when displayed on a navigation bar. – Michael J. Gray Apr 19 '12 at 11:26

score 6 · Answer 2 · answered Apr 19 '12 at 11:12

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.

score 6 · Answer 3 · edited May 23 '17 at 11:53

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').

Perform all of the updates in a single command. See W0lf's answer for an example of this.
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.
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.
Remove the constraint and deal with duplicate values.

The idea of setting to negative then back to positive was clever and did the trick for me. Thanks! — jslatts, Jul 14 '16 at 02:21

Swapping values with unique constraint in Entity Framework

3 Answers3

Linked