Cannot swap unique value on two rows with EF

Question

I'm trying to swap the value in a unique column for two (or more rows). For example:

Before update:

• Row 1 = 1
• Row 2 = 2

After update:

• Row 1 = 2
• Row 2 = 1

I'm using Entity Framework. These changes take place on a single commit for the same context. However, I always get a unique constrain violation when I attempt this update. Is EF not using a transaction?

For completeness, here is a snippet of my table design:

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

I'm trying to update the 'SortPosition' column. The code is a bit complex to display here, but I can assure you that it is the same context with a single final commit. The error is only thrown when EF tries to write to the database.

UPDATE:

- Using SQL Server Profiler I can see that EF is running separate UPDATE for each affected row. Should EF not be using a single transaction for one call to SaveChanges()? -

UPDATE 2:

Turns out EF is using a single transaction after all. SQL Profiler was filtering it out.

Answer 1

You can't do it with 2 statements also with SQL Server. You need to use a third value

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 profiler shows BEGIN TRANSACTION/COMMIT statements

Answer 2

An alternative trick I have used which doesn't rely on temporary values (which themselves risk violating uniqueness) is to issue a single UPDATE as in:

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

Unfortunately EF is not smart enough to generate those type of statements by itself.