Entity Framework Core - transaction cannot be roll back after commit

Question

It is using Entity Framework Core to update database.

dbContextTransaction.Commit(); is working fine, and after this it is some file operation with bad result. And then it throws an error, so it tries to roll back using dbContextTransaction.Rollback(); but results in:

This SqlTransaction has completed; it is no longer usable.

DbContext dbContext = scope.ServiceProvider.GetService<DbContext>();
IDbContextTransaction dbContextTransaction = dbContext.Database.BeginTransaction();

try
{
    IOrderDao orderDao = scope.ServiceProvider.GetService<IOrderDao>();
    IItemSoldPriceDao itemSoldPriceDao = scope.ServiceProvider.GetService<IItemSoldPriceDao>();

    ItemSoldPrice itemSoldPrice = new ItemSoldPrice
    {
      ...
    };

    itemSoldPriceDao.AddItemSoldPrice(itemSoldPrice);
    order.SoldPriceCaptured = true;

    dbContext.SaveChanges();
    dbContextTransaction.Commit();
    //... some other file operation throws out error
    throw new Exception("aaa");
}
catch (Exception ex)
{
    CommonLog.Error("CaptureSoldPrice", ex);
    dbContextTransaction.Rollback();
}

After a transaction is committed, it cannot be rolled back?

Answer 1

When using Entity Framework, explicit transactions are only required when you want to link the success or failure of operations against the DbContext with other operations outside of the scope of the DbContext. All operations within a DbContext prior to SaveChanges are already grouped within a transaction. So for instance saving entities across two or more tables within a DbContext do not require setting up an explicit transaction, they will both be committed or rolled back together if EF cannot save one or the other.

When using an explicit transaction, the Commit() call should be the last operation for what forms essentially a unit of work. It will be the last operation to determine whether everything in the transaction scope is successful or not. So as a general rule, all operations, whether Database-based, file based, or such should register with and listen to the success or failure of the transaction.

An example of using a transaction: Say we have a system that accesses two databases via two separate DbContexts. One is an order system that tracks orders and has a record for a Customer and one is a CRM that tracks customer information. When we accept a new order from a new customer we check the CRM system for a customer and create a customer record in both systems if it is someone new.

using (var orderContext = new OrderDbContext())
{
    var transaction = orderContext.Database.BeginTransaction();

    try
    {
        var order = new Order
        {
            // TODO: Populate order details..
        }

        if(customerDetails == null && registerNewCustomer) // Where customerDetails = details loaded if an existing customer logged in and authenticated...
        {
            using(var crmContext = new CrmDbContext())
            {
                crmContext.Database.UseTransaction(transaction);
                var customer = new Customer 
                {
                    UserName = orderDetails.CustomerEmailAddress,
                    FirstName = orderDetails.CustomerFirstName,
                    LastName = orderDetails.CustomerLastName,
                    Address = orderDetails.BillingAddress
                };
                crmContext.Customers.Add(customer);
                crmContext.SaveChanges();
                var orderCustomer = new Orders.Customer
                {
                    CustomerId = customer.CustomerId,
                    FirstName = customer.FirstName,
                    LastName = customer.LastName
                }
                orderContext.Customers.Add(orderCustomer);
            }
         }
         
         order.CustomerId = crmContext.Customers
             .Select(c => c.CustomerId)
             .Single(c => c.UserName == customerDetails.UserName);
     
         orderContext.Orders.Add(order);
         orderContext.SaveChanges();

         transaction.Commit();
     }
     catch(Exception ex)
     {
         // TODO: Log exception....         
         transaction.Rollback();
     }
}

The order DB customer is just a thin wrapper of the CRM customer where we would go for all of the customer details. The CRM customer manages the Customer IDs which would correlate to an Order Customer record. This is by no means a production code type example, but rather just to outline how a Transaction might coordinate multiple operations when used.

In this way if there is any exception raised at any point, such as after a new Customer record has been created and saved, all saved changes will be rolled back and we can inspect any logged exception details along with recorded values to determine what went wrong.

When dealing with combinations of DbContext operations and other operations that we might want to support a rolling back process flow on failure then we can leverage constructs like the TransactionScope wrapper. However this should be used with caution and only in cases where you explicitly need to marry these operations rather than attempting to use the pattern as a standard across all operations. In most cases you will not need explicit transactions.