Transactions with Entity Framework Core

In the last couple of weeks, I was talking about Entity Framework Core (see Creating a model for an existing database or Calling Stored Procedures) but today the topic is Transactions.

In the SQL world, it is common to use Transactions to save or update records or bunch of records in a database. Using transactions allows you to implement or increment the resilient of your application. You have the documentation on the Microsoft site.

If you read the documentation, adding transactions is very straight forward and in the Microsoft documentation there are a lot of examples. The following example is the common one.

using (var context = new BloggingContext())
{
    using (var transaction = context.Database.BeginTransaction())
    {
        try
        {
            context.Blogs.Add(new Blog { Url = "https://blogs.msdn.com/dotnet" });
            context.SaveChanges();

            context.Blogs.Add(new Blog { Url = "https://blogs.msdn.com/visualstudio" });
            context.SaveChanges();

            var blogs = context.Blogs
                .OrderBy(b => b.Url)
                .ToList();

            // Commit transaction if all commands succeed, transaction will auto-rollback
            // when disposed if either commands fails
            transaction.Commit();
        }
        catch (Exception)
        {
            // TODO: Handle failure
        }
    }
}

After that, there is only one cryptical error that Entity Framework gives you at runtime:

InvalidOperationException: The configured execution strategy ‘SqlServerRetryingExecutionStrategy’ does not support user initiated transactions. Use the execution strategy returned by ‘DbContext.Database.CreateExecutionStrategy()’ to execute all the operations in the transaction as a retriable unit.

Therefore, this issue is more visible while performing Entity Framework Core operation in a shared environment like cloud.

Resolution

Generally speaking, a common cause for having this error are:

• transient errors
• network issues
• hardware issues

In other words, this error also indicates that if the application is configured with Default Execution strategies like RetryonFailure Pattern or Custom execution strategies are not sufficient to overcome the issue.

So, using Transactions with BeginTransaction() requires to invoke the Execution strategy. In addition, such execution strategy should encapsulate everything that needs to be executed.

Execution Pattern for Transaction

First of all, we have to define the Execution strategy using CreateExecutionStrategy() with a delegate encapsulating all database operation.

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

	strategy.Execute(() = >{
		using(var context = new EmployeeContext()) {
			//BeginTransaction
			using(var transaction = context.Database.BeginTransaction()) {

				//First Update
				context.EmployeeDb.Add(new EmployeeDb {
					FirstName = "Enrico",
					LastName = "PSC"
				});
				context.SaveChanges();

				//SecondUpdate
				context.EmployeeDb.Add(new EmployeeDb {
					FirstName = "Enrico2",
					LastName = "PSC"
				});
				context.SaveChanges();

				//End Transaction 
				transaction.Commit();
			}
		}
	});
}

Configure Context options

Now, to use transactions with Entity Framework Core properly, we have to add a bit more configuration in the context. For example, check in your Startup.cs if you have this setting:

builder.Services.AddDbContext<BillingContext>(options =>
    options.UseSqlServer(configuration.GetConnectionString("BillingDatabase"),
        providerOptions => providerOptions.EnableRetryOnFailure(
            maxRetryCount: 10,
            maxRetryDelay: TimeSpan.FromSeconds(10),
            errorNumbersToAdd: null
        )
    ));