Contrary to what you might think, SQL that gets executed on a database as a result of LINQ operations, might not be in the order of your LINQ calls.

Let’s consider a scenario in which Customer data needs to be updated. You’d like to delete the old row and then insert the new data rather than an Update, because of some triggers which run some complex business logic.

Unfortunately, a sequence of an Insert followed by a Delete does not execute on SQL Server like you would expect. For some reason, LINQ always executes the DELETE operation at the end, no matter where it was in the execution sequence.
So, a Delete and Insert in your LINQ would execute as an Insert and a Delete on SQL Server. This could cause problems like Primary key violations, depending on your table design.
There is no way to get around this, except by calling SubmitChanges() after the different Operations.
So, you would have Delete, DataContext.SubmitChanges and then Insert and DataContext.SubmitChanges. This would preserve the sequence of Deleting before an Insert.

To maintain data integrity, the executions should be in a Transaction. Since you call SubmitChanges() more than once, you need to manually create/manage the Transaction.

This is done by enclosing the operations in a block:


using (var ts = new TransactionScope())
{
    try {
   …
   ts.Complete();   //commit the transaction

    }
    catch
    {
         ts.Dispose();   //rollback
     }
}

Note: To use TransactionScope, you need to reference System.Transactions.

If you are using SQL Profiler to watch the SQL being executed, note that you will always see the Update and Insert statements irrespective of whether the Transaction was rolled back or not, because SQL Profiler does not indicate Transactions, but let that not confuse you. The data will not be affected in case of a Rollback.

Advertisements