1

I have come across an interesting problem in Entity Framework 6 and SQL Server.

We have a table with a Composite Key. Here is an example;

ID  Col1  Col2
--  ----  ----
1   1     1
2   1     2
3   2     1
4   2     2
5   2     3

So, Col2 is unique for each Col1. I have a requirement to swap 2 values to produce this desired result...

ID  Col1  Col2
--  ----  ----
1   1     2
2   1     1

I am using Entity Framework, load the object from the database, make my change, and call SaveChanges.

I Receive the exception: "Violation of UNIQUE KEY constraint 'UQ_TableA_Constraint1'. Cannot insert duplicate key in object 'dbo.TableA'."

Supposedly, SaveChanges is called in a transaction. The EF Source seems to indicate it is, and the fact that a failed update is atomic would indicate this is working. However, it also appears that updates are completed ROW BY ROW, even inside the transaction. Thus that EF first performs an update to record 1 which temporarily produces a duplicate unique key.

Is there a manner to mitigate this? I would rather not update to a temp value, call savechanges, and then update to the correct value as this potentially could fail and leave the data in an incorrect state.

Are there any options?

user1654348
  • 283
  • 4
  • 12

1 Answers1

0

I hope I understand your question.
Contraints must be satisfied also inside a transaction, this is a SQL Server (and other DBMSs) behaviour not an EF behaviour.
You can use a temporary value inside a transaction to be sure that everything went well.
If you need to run update queries (on multiple records), you can use an external library https://github.com/loresoft/EntityFramework.Extended (but if I understand your question, you won't solve contraint issues).

bubi
  • 6,414
  • 3
  • 28
  • 45
  • Thank you. I think I knew this, but needed confirmation. It is however odd because it seems like such a logical thing to need to do. The composite key is actually not 100% MY issue; I just thought it was easier to explain. In my REAL issue we have a list of unique values in Col2 for every value in Col1, which are unique but unordered. However 1 value in col2 is flagged as the default. We then have a unique constraint which returns -1 for the default, or the col2 value is not default which ensures only 1 default value. – user1654348 Mar 31 '17 at 07:54
  • If I update a record with a later ID (ie. down the table structure) to the default, it works. This will be because all defaults are false during the update. However if I set a value higher up the table stack (ie. with a lower ID value) it fails, presumably because it is setting 2 values as default during the update. The only problem with setting all to false, saving changes, and then applying the default is if the second update fails there will be no default value. – user1654348 Mar 31 '17 at 07:57
  • SQL Server doesn't support deferred constraints, as for example, [Postgres](https://www.postgresql.org/docs/9.1/static/sql-set-constraints.html). So, use temp value and wrap your sequence of updates in a single transaction. If you wrap your updates in a transaction, the data will never be left in an incorrect state. – Vladimir Baranov Mar 31 '17 at 09:52