Linq2SQL dealing with inserts/deletes on table with unique constraints

Question

Hi

I have a table that looks like the following:

TABLE Foo
{
  Guid Id [PK],
  int A [FK],
  int B [FK],
  int C [FK],
}

And unique constraint over A, B and C.

Now say for example, you insert a row with a fresh PK with with A = 1, B = 1, C = 1.

SubmitChanges(), all happy.

Now you edit the table.

You remove the previous entry, and insert a row with a fresk PK with A = 1, B = 1, C = 1.

SubmitChanges() BOOM! Unique key constraint SQL exception.

From what I can see, it attempts to first insert the new record, and then try to delete the previous one. I can even understand that it is not possible to determine the order this needs to happen.

But what can I do about it? Would making those 3 fields a composite PK (and removing the old one) be a better solution or wont it even work?

For now, the 'solution' is to remove the unique constraints from the DB (but I'll rather not do so).

Answer 1

One option would be to create a transaction (either a connection-bound transaction, or a TransactionScope) - remove the record and SubmitChanges, add the record and SubmitChanges, then finally commit the transaction (or roll-back if you blew up).

Note that you can associate a connection-bound transaction through the data-context constructor IIRC. TransactionScope should also work, and is easier to do - but not quite as efficient.

Alternatively, write an SP that does this swap job at the database, and access that SP via the data-context.

Answer 2

I had the same problem. Ended up writing a wrapper class with an 'Added' and 'Deleted' collection of entities that I maintained. As well as a 'Current' collection. The UI was bound to the current collection.

Only when I go to save do I InsertOnSubmit / DeleteOnSubmit, and I parse the 2 collections to decide which entities to do what to.