C# TransactionScope - L2E

Question

Is it worth using System.Transactions.TransactionScope on Linq to Entities?

On the MS documentation, it says that SQL calls within ObjectContext.SaveChanges() are all rolled into one transaction internally.

We have 1 database connection, that is a local SQLite database on the file system. We just want to make sure all our operations to the database are atomic, do we need TransactionScope? I.E. when we call for some deletes, updates, inserts, etc., we want them all to happen or none at all.

Answer 1

If you want to include more than the ObjectContext.SaveChanges in a single transaction (e.g. reads of data you are going to change, as well as the changes) then you need to make use of TransactionScope.

Answer 2

Jon, no you don't need to use TransactionScope. Optimistic concurrency is handled automatically by Linq. The code sample in the link you provide explains that rather well, you don't have to roll back transactions yourself. I would use the same code as in the sample:

    try
    {
        // Try to save changes, which may cause a conflict.
        int num = context.SaveChanges();
        Console.WriteLine("No conflicts. " +
            num.ToString() + " updates saved.");
    }
    catch (OptimisticConcurrencyException)
    {
        // Resolve the concurrency conflict by refreshing the 
        // object context before re-saving changes. 
        context.Refresh(RefreshMode.ClientWins, orders);

        // Save changes.
        context.SaveChanges();
        Console.WriteLine("OptimisticConcurrencyException "
        + "handled and changes saved");
    }

Notice the refresh, re-save, which handles your concern. You could test this out by throwing an exception from within the try block.

Best Regards

Answer 3

You could use the following code if you need to do what Richard says (though it seems rather unlikely):

TransactionManager transactionManager = null;

try
{
    bool isBorrowedTransaction = ConnectionScope.Current.HasTransaction;
    transactionManager = ConnectionScope.ValidateOrCreateTransaction(true);

    //MANY SAVES

    if (!isBorrowedTransaction && transactionManager != null && transactionManager.IsOpen)
        transactionManager.Commit();
}
catch (Exception ex)
{
    if (transactionManager != null && transactionManager.IsOpen)
        transactionManager.Rollback();
    log.Error("An unexpected Exception occurred", ex);
    throw;
}