EF problem with entity re-ordering and uniqueness constraint

Question

I am using Entity Framework and I've come to an interesting stumbling block. Let's say there is a db table "Item" with "sequence" column of type int (and others of course). Column "sequence" must be unique and it is used for (re)ordering of items.

EF maps this table to "Item" class with "sequence" int property. Now let's say I want to swap position of two items by mutually exchanging each other's sequence number. Upon calling SaveChanges() EF throws an exception complaining about "sequence" uniqueness. It probably generates two UPDATEs and the first one probably fails.

I assume that plain SQL solution to this issue is using a third UPDATE to introduce a unique sequence value in the process but I am stuck with EF.

Any thoughts?

EDIT: I am using SQL Express.

Answer 1

If you are using SQL Server Express 2008 you could explore using the new T-SQL MERGE statement. You should be able to update the sequence column with a single MERGE statement.