How to delete and insert records with the same primary key using Linq transactions

Question

We have a single table in the database that represents a bunch of divs in our web page. Each div has a pixel width, height, top, and left saved as individual records in the table. It has a primary key. We load all divs from the table to the web page, the user rearranges the squres, then in a single transaction saves the altered squares back to the table using Linq "OnSubmit" methods.

Our problem is we delete all the squares then reinsert all squares. Without transactions, this works fine. With transactions, it flags all records to be deleted, then the inserts actually update the records already flagged to be deleted. In the end, some of the squares are missing.

We do DeleteOnSubmit(), then InsertOnSubmit(), then eventually .SubmitChanges().

Where are we making our mistake? How to you handle deleting and reinserting all in the same transaction? Obviously we could SubmitChanges() after the delete, but this wouldn't put it in the same transaction.

Any ideas? Any patterns? Do you need the actual source code to continue?

Answer 1

We recommend you to open the transaction yourself and commit it, when necessary.
In this case the inernal transaction will not be opened.

t = db.Connection.BeginTransaction();
db.Transaction = t;
DeleteOnSubmit();
SubmitChanges();
InsertOnSubmit();
SubmitChanges();
t.Commit();