0

I have a simple table with the following:

ID  MarketID Lead
1   1        true
2   1        false
3   2        true
4   2        false

It is a table of paired values, so the pattern is that there are always two rows with the same MarketID, but one must be false (0) and one must be true (1). This is enforced by a unique key constraint (which ensures MarketID and Lead combined are unique) that is working as expected.

However, I want a user to be able to change the "Lead" column, and switch the values.

I have this working fine up until I call SubmitChanges on:

table.AttachAll(updated);
table.Context.Refresh(RefreshMode.KeepCurrentValues, updated);
table.Context.SubmitChanges();

Where updated is the two changed rows

A quick look at SQL Profiler shows SQL Server (2008) is updating row by row, so it is first updating the true value to false, but before it gets to change the false value to true on the second row it causes a break and fails, because now both are set to false.

Does anybody know a way around this?

ASouthorn
  • 399
  • 4
  • 15
  • You could do this with a dedicated command to switch the values around atomically: `UPDATE table SET Lead = ~Lead WHERE MarketID = @MarketID`. I have no idea how to integrate this nicely into the `DataContext` way, though. – Jeroen Mostert Aug 15 '18 at 14:18

1 Answers1

0

  1. Temporarily disable & re-enable the UNIQUE constraint.

  2. Temporarily allow IDENTITY INSERTS, then delete and re-create both rows.

  3. Temporarily allow IDENTITY INSERTS, then update the ID value of both rows. (this might be a 3-step process if the ID column has a UNIQUE constraint)

  4. Allow NULLs in the Lead column, and use NULL to temporarily bypass the UNIQUE constraint while you switch one row and then the other.

...and if I think of any others I'll add them later.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • Thanks Tab. Keen to avoid hacky workarounds that involve temporary rule changes but allowing nulls is a good idea, and would work in my specific instance but there might be an issue later on. – ASouthorn Aug 15 '18 at 15:41