Exchanging Values ​​with a Unique Constraint in the Entity Framework

Question

I have a unique restriction on a Navigations column called Index . I have two Navigation objects, and I want to change their Index values.

When I call db.SaveChanges , it throws an exception indicating that a unique constraint has been violated. It seems that EF updates one value and then another, thereby violating the constraint.

Shouldn't they be updated both in the transaction and then try to commit as soon as the values ​​are sorted and the constraints are not violated?

Is there any way around this without using time values?

Answer 1

This is not an EF problem, but an SQL database problem because update commands are executed sequentially. A transaction has nothing to do with it - all restrictions are checked for each team, and not per transaction. If you want to change unique values, you need more steps to use additional dummy values ​​to avoid this situation.

Answer 2

There are several approaches. Some of them are described in other answers and comments, but for completeness I have listed them here (note that this is just a list that I spent in a brainstorming session, and this may not be all that is “complete”).

• Perform all updates in one team. See W0lf for an example.
• Make two sets of updates - one to change all values ​​to negative values ​​of the expected value, and then to the second to change them from negative to positive. This works on the assumption that negative values ​​are not prevented by other constraints and that they are not values ​​that will have entries other than those in a transient state.
• Add an additional column — for example, IsUpdating — set it to true in the first update set, where the values ​​are changed, and then set it back to false in the second update set. Change the unique constraint for the filtered unique index, which ignores entries where IsUpdating is true.
• Remove the constraint and handle duplicate values.

Answer 3

You can run your own SQL query to replace the values, for example:

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

However, the Entity Framework cannot do this because it is beyond the scope of ORM. It simply executes successive update instructions for each changed object, as described in its Ladislav answer.

Another possibility would be to abandon the UNIQUE in your database and rely on the application to correctly apply this constraint. In this case, EF may save the changes just fine, but depending on your scenario, this may not be possible.