5

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.

Luke Franklin
  • 514
  • 4
  • 13
  • "Is EF not using a transaction?" - that depends on what type of query you're running ([see this article](https://msdn.microsoft.com/en-us/data/dn456843.aspx#default)). Assuming it's an UPDATE query (which this should be), it's supposed to wrap it in a transaction. – Tieson T. Jul 01 '15 at 03:21
  • You might have to make one of the keys nullable, or remove and reinsert the rows (assuming this is just a join table). – Tieson T. Jul 01 '15 at 03:24
  • According to the SQL profiler. EF is running two separate statements for the update of the two rows. – Luke Franklin Jul 01 '15 at 03:27
  • Can you show a stripped-down version of the LINQ query? Also, even if "two separate statements" are executed, the transaction is on the **connection**, so those two queries will be rolled back together when the exception occurs. – Tieson T. Jul 01 '15 at 03:35
  • @TiesonT. I can see that it is running separate statements by using a similar operation that does not make any changes to the UNIQUE column. – Luke Franklin Jul 01 '15 at 03:38
  • Scratch that. SQL Profiler was filtering out transaction statements. – Luke Franklin Jul 01 '15 at 04:45
  • Isn't FeeSchemeId meant to be a GUID given that it is a UniqueIdentifier – Kirsten Jul 01 '15 at 06:09
  • Yes, FeeSchemeId is a Guid, but I'm conflicting on SortPosition. – Luke Franklin Jul 01 '15 at 06:29

2 Answers2

3

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

bubi
  • 6,414
  • 3
  • 28
  • 45
  • SQL Server Profiler only includes TRANSACTION statements explicitly. They are not included by default. – Luke Franklin Jul 01 '15 at 06:28
  • Yes, you are right. In your case you could expect that it worked if EF generates the 2 update statements inside an explicit transaction so SQL Server Profiler should show it. But also inside the same transaction SQL Server does not work... – bubi Jul 01 '15 at 06:35
  • Looks like you're right. I anticipated that because all the changes were completed during a transaction that SQL Server would check constraints against the overall effect rather than on individual statements. – Luke Franklin Jul 01 '15 at 06:57
  • SQL does not disable constraints during transactions... This is a common misconception. See here http://stackoverflow.com/questions/5974731/does-sql-server-allow-constraint-violations-in-a-transaction-as-long-as-its-not – LMK May 31 '16 at 21:09
  • I don't think this is true, I can swap two unique values with a single statement in TSQL. (well ok, maybe you can't do it with 2 statements because inbetween things would be invalid, but you can do it with 1 statement.) – Dave Cousineau Apr 03 '18 at 17:32
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.

LMK
  • 1,496
  • 1
  • 13
  • 15
  • 1
    yes, this works, and you can't seem to do it through EF. problem is, if you're testing you can't send raw SQL to a mock database. – Dave Cousineau Apr 03 '18 at 17:33