TransactionScope transaction = new TransactionScope() VS TransactionScope s = context.Connection.BeginTransaction()

Question

I just want to know if I want to rollback all changes in database if transaction not complete, is there any difference between

 using (TransactionScope transaction = new TransactionScope())

and

using (var dbContextTransaction = context.Database.BeginTransaction())

I am confused when read these two:

Connection.BeginTransaction and Entity Framework 4?

and

https://learn.microsoft.com/en-us/ef/ef6/saving/transactions

**note that I use entity framework 4 in my project if its necessary

What do you mean by "redo all"? Why can't you use a transaction if you need the generated keys? — CodeCaster, Jan 07 '20 at 11:29
https://stackoverflow.com/questions/542525/transactionscope-vs-transaction-in-linq-to-sql and https://stackoverflow.com/questions/22382892/database-begintransaction-vs-transactions-transactionscope — Amin Golmahalleh, Jan 07 '20 at 12:51
@AminGolmahalle I think your comment is the best answer, if you post it as an answer, I can mark it. — Sepehr Estaki, Jan 20 '20 at 20:29

score 4 · Answer 1 · edited Jun 20 '20 at 09:12

From Programming Microsoft SQL Server 2012:

There are a number of pain points with explicit transactions. The first difficulty lies in the requirement that every SqlCommand object used to perform updates inside the transaction must have its Transaction property set to the SqlTransaction object returned by BeginTransaction. This means that you must take care to pass along the SqlTransaction object to any place in your code that performs an update, because failing to assign it to the Transaction property of every SqlCommand object in the transaction results in a runtime exception. The issue is compounded when you need to track the SqlTransaction object across multiple method calls that perform updates. It becomes even harder to manage things when these methods need to be flexible enough to work whether or not a transaction is involved or required.

The problem is worse when working with any of the other technologies we'll be covering later that provide abstraction layers over the raw objects. For example, because a SqlDataAdapter actually wraps three distinct SqlCommand objects (for insert, update, and delete), you must dig beneath the data adapter and hook into its three underlying command objects so that you can set their Transaction properties. (We don't generally recommend that you mix and match different data access APIs within your application, but if you must transactionalize updates across a combination of technologies, implicit transactions make it easy.)

The TransactionScope object, introduced as part of a dedicated transaction management API with .NET 2.0 in 2005, lets you code transactions implicitly. This is a superior approach that relieves you from all of the aforementioned burdens associated with explicit transactions. So the guidance here is to always work with implicit transactions whenever possible. You will write less code that is more flexible when you allow the framework to handle transaction management for you. However, it is still also important to understand explicit transactions with the SqlTransaction object, as you might need to integrate with and extend existing code that already uses explicit transactions. Therefore, we will cover both transaction management styles to prepare you for all situations.

The transaction management API offers many more benefits besides implicit transactions. For example, TransactionScope is capable of promoting a lightweight transaction (one associated with a single database) to a distributed transaction automatically, if and when your updates involve changes across multiple databases.

score 3 · Answer 2 · answered Jan 07 '20 at 14:07

There are two pitfalls with TransactionScope you should be aware of.

First is that it will, by default, create a transaction with SERIALIZABLE isolation level, which, for SQL Server, is a poor choice. So you should always create your TransactionScope like this:

public class TransactionUtils 
{
  public static TransactionScope CreateTransactionScope()
  {
    var transactionOptions = new TransactionOptions();
    transactionOptions.IsolationLevel = IsolationLevel.ReadCommitted;
    transactionOptions.Timeout = TransactionManager.MaximumTimeout;
    return new TransactionScope(TransactionScopeOption.Required, transactionOptions);
  }
}

See rant here: using new TransactionScope() Considered Harmful

The second is that TransactionScope supports Distributed Transactions. So it will enable you to enlist different connections and even different resource providers in a single transaction. While this is occasionally useful, it's more often a pitfall. If you accidently end up with a distributed transaction you can accidently take a dependency on having a distributed transaction coordinator in your environment. So you should take steps to avoid having a distributed transaction, like shutting down Microsoft Distributed Transaction Coordinator (MSDTC) in your development environment. And ensure that any time you have multiple methods enlisted in a transaction, they don't both have a SqlConnection open at the same time.

How I can see that the default isolation level is SERIALIZABLE? — JohnyL, Jan 07 '20 at 15:46
It's documented here: https://learn.microsoft.com/en-us/dotnet/api/system.transactions.isolationlevel?view=netframework-4.8 — David Browne - Microsoft, Jan 07 '20 at 15:50

score 1 · Accepted Answer · answered Jan 21 '20 at 06:38

While Database. BeginTransaction() is used only for database related operations transaction, System. Transactions. ... TransactionScope for mixing db operations and C# code together in a transaction.

please see Below Links.Hope they help you:

TransactionScope vs Transaction in LINQ to SQL

Database.BeginTransaction vs Transactions.TransactionScope

TransactionScope transaction = new TransactionScope() VS TransactionScope s = context.Connection.BeginTransaction()

3 Answers3