You are implementing an application that uses LINQ to SQL to update a data source. You add the following code that uses a DataContext named northwindContext:

using (TransactionScope ts = new TransactionScope())
{
Product prod1 = northwindContext.Products.First(p => p.ProductID == 4);
Product prod2 = northwindContext.Products.First(p => p.ProductID == 5);
prod1.UnitsInStock -= 3;
prod2.UnitsInStock -= 5;
northwindContext.SubmitChanges();
}

You notice that when this code executes the data source is not updated. You need to fix the problem and ensure that the data source changes execute in the scope of the transaction.

What should you do?

You can call LINQ to SQL APIs (including, but not limited to, SubmitChanges) in the scope of an active Transaction. LINQ to SQL detects that the call is in the scope of a transaction and does not create a new transaction. LINQ to SQL also avoids closing the connection in this case. You can perform query and SubmitChanges executions in the context of such a transaction. To complete the transaction and persist changes made with SubmitChanges, you must call the Complete method of the TransactionScope after calling the SubmitChanges method.

You must call the TransactionScope's Complete method after, not before, the call to SubmitChanges to ensure that the changes occur in the scope of the transaction.

The DataContext Refresh method refreshes object state by using data in the database. This method does not complete the transaction in scope, and calling this method after the call to the SubmitChanges method will not fix the problem.