CRUD – Transaction Behavior in Entity Framework Core

Transaction means allowing several database operations to be processed by programs in the unit manner or atomic manner. If the web applications commit the transaction, all operations will be successfully executed in the Microsoft SQL Server(MS SQL) database. If the transaction is rolled back by programs, none of the operations will be successful in the MS SQL database.

Most developers will use the stored procedures or Integration Services (SSIS) packages in the MS SQL database, which has the design concepts and the performance concepts. Other developers don’t like the stored procedures and SSIS packages because they think the MS SQL engine performance isn’t outstanding. They will use third-party tools and T-SQL script applied to the CRUD process in the .NET MVC framework and .NET Core framework. Based on this factor, many developers will have a question:

“Could I control transaction?“

Before reply to this question answers, we talk about the default behavior in the entity framework core. If the database supports transactions, all operations in a single call to “SaveChanges” are applied in the transaction. If the operations fail, the transaction is rolled back by programs, none of the operations will be successful in the database. It means the “SaveChanges” guarantee ultimately succeeds. It has become the default transaction behavior in the entity framework core. If you want to control transactions, it can do in the entity framework core.

Microsoft provides two methods such as System.Transactions.TransactionScope and the Database.BeginTransaction(). We can quickly find out one method name has the “Scope” word and understand its scope concepts. From MSDN, we can advance proof the TransactionScope method can support the distributed transaction. In recent years, Microsoft improves the scope concepts in the .NET Core framework project, but the TransactionScope has a few advantages and disadvantages in transaction behavior.

Disadvantages of TransactionScope:

• Requires .NET 4.5.1 or greater to work with asynchronous methods.
• It cannot be used in cloud scenarios unless you are sure you have one and only one connection (cloud scenarios do not support distributed transactions).
• It cannot be combined with the Database.UseTransaction() approach of the previous sections.
• It will throw exceptions if you issue any DDL and have not enabled distributed transactions through the Microsoft Distributed Transaction Coordinator(MSDTC) Service.

_{Base on MSDN article.}

Advantages of TransactionScope:

• It will automatically upgrade a local transaction to a distributed transaction if you make more than one connection to a given database or combine a connection to one database with a connection to a different database within the same transaction (note: you must have the MSDTC service configured to allow distributed transactions for this to work).
• Ease of coding. If you prefer the transaction to be ambient and dealt with implicitly in the background rather than explicitly under your control, then the TransactionScope approach may suit you better.

_{Base on MSDN article.}

Prepare in Advance

We need to prepare the MS SQL, which needs to turn on the SQL Server Profiler’s Transaction events. You can use the local MS SQL database.

Tools -> SQL Server Profiler -> Trace Properties -> Transaction events

Note: Transaction events won’t show in the trace because “Transaction” isn’t included in the “Standard” template. The transaction is hidden by default. To show it up, check “Show all events.”

We demo the transaction behavior in the .NET Core project. If you want to see the .NET MVC project demo, please, click my GitHub repository link at the bottom position or on my profile page.