Transactions

In this post we will be looking at a way to handle multiple dependent queries in ef core.

With dependent we mean queries that should either be successful in conjunction or let all of them fail together. This is often necessary to keep the business logic consistent.

From database management and design we know this term coming from the ACID range (and no this is not about the drug 😉 ) as to be the atomic part. Generally in RDBMS this is achieved with transactions…

And as we will see this is the same for ef -core.

Also take a look at all the available recipes for ef-core.

• Make multiple queries atomic 
• 3 ways to achieve this with ef-core
• Advanced features

 Make multiple queries atomic

Lets assume we have the following structure for our database.
If we now have a business case, where a customer orders a product we need to write to more than one table, but to keep the data consistent all of them should be changed or none.

1. If a Customer orders, the ordered product needs to decrement the Availability count to synchronize the stored stock with the actual stock
2. The order needs to be saved to the orders table.

If at any step in between (like evaluating the payment process and such) anything fails we want to set back everything.

In essence we need to make all the single queries into seemingly one query, or in other words into an atomic operation.

The term to unify this operations is called a Transaction. We wrap all the calls in such a transaction.
If everything completes successfully, we commit the transaction. If anything fails, the whole transaction is rolled back and no changes are applied to the database.
For the regular use of a RDBMS without ef-core this works like so:

• Begin Transaction
• execute step 1 to n
• If execution is a success:
  • Commit the transaction
• else
  • Rollback transaction

 3 ways to achieve this with ef-core

The good thing about ef-core is, that you can have this behavior in three ways. It only depends on how you structure your application:

1. Utilize the UnitOfWork pattern in your UseCases
   As mentioned in my recipe on query patterns  you can utilize an interface on your repository classes and call the CommitAsync to simulate a transaction.This works because :
2. The SaveChangesAsync method of the DbContext in itself is already transactional
3. If you still need to create your own transactions you can use the BeginTransaction (or for advanced uses the TransactionScope (not covered here))This you can implement like the following:

   using (var scope = _context.Database.BeginTransaction())
   {
       try
       {
           // do your queries
           await _context.SaveChangesAsync()
           scope.Commit();
        }
        catch (Exception ex)
        {
          scope.Rollback();
        }
   }

   Always use try catch in transaction to know when to rollback!
   You can catch specific exceptions, but make sure to catch the base Exception in any case.

   And thats all if you want to make operations with ef core atomic!!

   (at least in a basic way, see advanced section below for possible more complicated scenarios)

Advanced features

• Distributed Transactions
• Correct use of TransactionScope