Database Connection Resiliency in Entity Framework Core: update

In this post, we will see how to implement database connection resiliency in Entity Framework Core, an update to my previous post. Here, I’m using an execution strategy for Database operation define using Transaction scope.

As you know, we already looked at the inbuilt and custom strategy execution pattern using EnableRetry and using a Custom execution strategy.

So, if interested to see those option, please visit this other post

• Database Connection Resiliency Using EnableRetry and Execution Strategy

Limitation of Retry and Default Execution Strategies

As we understood in our previous articles, RetryOnFailure and custom execution strategies patterns are very useful for retrying defaulted commands automatically in Entity Framework Core.

In other words, these patterns help in overcoming Transient errors/faults caused due to network connectivity issues or the temporary unavailability of a service, or timeouts issues, etc.

So, when an issue or transient error occurs, resiliency retries all the operations by re-establishing the connection.

Limitations of Default Execution Strategies

• Retry /Default Execution Strategies automatically retries all operations on failures.
• Retries all operations without considering data and its state verification managed through operations.
• Default Execution strategies retry all operations defined in the Transaction.
• Any database operation using Transaction scope like Ambient transaction could lead to data corruption.

In such scenarios, one can execute the execution strategy using delegate which shall create the scope of all the database operation that needs to be performed, etc.

Execution Pattern for Transaction

Now, we can update our example of database connection resiliency in Entity Framework Core with transaction as in the following code:

public SaveInvoiceResponse SaveInvoice(Invoice invoice)
{
    SaveInvoiceResponse rtn = new SaveInvoiceResponse();

    // step 1
    var strategy = _context.Database.CreateExecutionStrategy();
    strategy.Execute(() =>
    {
        // step 2
        using (var transaction = _context.Database.BeginTransaction())
        {
            try
            {
                // step 3
                _context.Invoice.Add(invoice);
                var result = _context.SaveChanges();

                // step 4
                transaction.Commit();

                rtn.Success = true;
                rtn.InvoiceId = invoice.Id;
            }
            catch (Exception ex)
            {
                _log?.LogError(ex, "Save Invoice Error");
                transaction.Rollback();

                rtn.ErrorMessage = ex.Message;
            }
        }
    });

    return rtn;
}

1. Declare the execution strategy
2. Createa a new Transaction scope for a given context (one can use multi DBContext within given Transcope if needed
3. Define Database resource updates if any
4. Commit the transaction (for one or multiple DBContext)

In conclusion, the above sample uses the same database schema and scaffolding model entities which we learned in our Creating a Model for an Existing Database article.

Transaction State verification

In addition, Entity Framework Code provides an extension method ExecuteInTransaction() to perform state verification of any database operation.

So, this API I found to be very useful to keep connection resiliency and at the same time keeping track of if the database operation is successful or not.

using(var dbcontext = new EmployeeContext()) {
	var strategy = dbcontext.Database.CreateExecutionStrategy();

	dbcontext.EmployeeDb.Add(new EmployeeDb {
		FirstName = "Enrico",
		LastName = "PSC",
		Id = "1234"
	});

	strategy.ExecuteInTransaction(dbcontext, operation: context = >{
		context.SaveChanges(acceptAllChangesOnSuccess: false);
	},

	verifySucceeded: context = >context.EmployeeDb.AsNoTracking().Any(b = >b.Id == "1234"));

	dbcontext.ChangeTracker.AcceptAllChanges();
}

In the above code, verifySucceeded is the delegate, that tests whether the operation succeeded even if the exception is thrown while the Transaction was being committed.

Read more…

In conclusion, I published other posts about Entity Framework Core that may be interesting to you:

• Transactions with Entity Framework Core
• Entity Framework Core and calling a stored procedure
• Creating a Model for an Existing Database
• Database Connection Resiliency in Entity Framework Core