5

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).

Winston Smith
  • 21,585
  • 10
  • 60
  • 75
leppie
  • 115,091
  • 17
  • 196
  • 297

2 Answers2

3

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.

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • Unfortunately not so easy. The list is maintained via a BindingList, obtained from an EntitySet bound to a DataGridView. – leppie Apr 28 '09 at 14:10
1

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.

ross
  • 263
  • 1
  • 3
  • 5
  • Thanks for the response (+1) Btw I have found the easiest is to actually just send modifications in realtime to the DB, then this cant happen :) Another option might be to add a transaction to the aforementioned if you need to 'undo'. – leppie Feb 02 '10 at 05:10