Geek Answers Handbook

It is not possible to swap a unique value into two lines using EF

I am trying to swap a value in a unique column for two (or more rows). For instance:

Before updating:

  • Line 1 = 1
  • Line 2 = 2

After update:

  • Line 1 = 2
  • Line 2 = 1

I am using Entity Framework. These changes occur in one commit for the same context. However, when I try this update, I always get a unique violation of restrictions. Does EF not use a transaction?

For completeness, here is a snippet of my table:

[FeeSchemeId] UNIQUEIDENTIFIER NOT NULL,
[SortPosition] INT NOT NULL,
UNIQUE (FeeSchemeId, SortPosition)

I am trying to update the column "SortPosition". The code here is a bit complicated, but I can assure you that it is the same context with one final commit. An error is only raised when EF tries to write to the database.

UPDATE:

- SQL Server Profiler, , EF UPDATE . EF SaveChanges()? > -

2:

, EF . SQL Profiler .

+4
2

SQL Server.

BEGIN TRANSACTION;
UPDATE MyTable Set Id = 200 where Id = 1;
UPDATE MyTable Set Id = 1 where Id = 2;
UPDATE MyTable Set Id = 2 where Id = 200;
COMMIT;

BTW, SQL Server BEGIN TRANSACTION/COMMIT

+3

, , ( ), , UPDATE, :

UPDATE MyTable
SET ID = case when id = 1 then 2 else 1 end
WHERE ID in (1, 2)

, EF , .

+2

More articles:


All Articles