Execution strategy 'SqlServerRetryingExecutionStrategy' does not support user-initiated transactions

Question

I have an ASP.NET Core 5 MVC web application, working with Entity Framework Core 5.

We implement web components, like Grid or Spreadsheet (we work with Telerik). When I do some changes in the component, and then try to save the changes, the component calls my ApplicationDbContext.SaveChanges. And then I get the following error:

System.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.

Here is my method where I ConfigureServices for the DBContext:

public static void AddDbContext(this IServiceCollection services, string connectionString) =>
    services.AddDbContext<ApplicationDbContext>(options =>
    {
        options.UseSqlServer(connectionString,
            providerOptions =>
            {
                providerOptions
                    .EnableRetryOnFailure(
                        maxRetryCount: 5, 
                        maxRetryDelay: TimeSpan.FromSeconds(30),
                        errorNumbersToAdd: null)
                    .UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery);
            });
        options.EnableSensitiveDataLogging();
        options.ConfigureWarnings(w => w.Throw(RelationalEventId.MultipleCollectionIncludeWarning));
    });

That error does not happen systematically. For sure when I try to save to DB multiple elements. But also when only one element. Does not happen when I edit or same an element one by one, in a classic form, only when it happens in multiple objects scenario (Grid or Spreadsheet)...

The StackTrace would be like this:

As example, I give bellow a method allowing to submit Entreprise objects, from a Spreadsheet to the DB, in order to use CreateExecutionStrategy method:

public ActionResult Data_Source_Entreprises_Submit(SpreadsheetSubmitViewModel model)
{
    var result = new SpreadsheetSubmitViewModel()
    {
        Created = new List<SpreadsheetEntrepriseViewModel>(),
        Updated = new List<SpreadsheetEntrepriseViewModel>(),
        Destroyed = new List<SpreadsheetEntrepriseViewModel>()
    };

    if ((model.Created != null || model.Updated != null || model.Destroyed != null) && ModelState.IsValid)
    {
        /*             executionStrategy                                 */
        var executionStrategy = _dbContext.Database.CreateExecutionStrategy();

        executionStrategy.Execute(() =>
            {
                using (var transaction = _dbContext.Database.BeginTransaction())
                {
                    try
                    {
                        ///////// Business Logic //////////////////
                        if (model.Created != null)
                        {
                            foreach (var created in model.Created)
                            {
                                EntrepriseDTO dto = new EntrepriseDTO() { Nom = created.Nom };
                                var createdDto = _entrepriseService.Create(dto);
                                created.Id = createdDto.Id;
                                result.Created.Add(created);
                            }
                        }

                        if (model.Updated != null)
                        {
                            foreach (var updated in model.Updated)
                            {
                                var spec = new EntrepriseForDetailsSpecification(updated.Id);
                                var dto = _entrepriseService.Read(spec);
                                dto.Nom = updated.Nom;
                                _entrepriseService.Update(dto);
                                result.Updated.Add(updated);
                            }
                        }

                        if (model.Destroyed != null)
                        {
                            foreach (var destroyed in model.Destroyed)
                            {
                                _entrepriseService.Delete(destroyed.Id);
                                result.Destroyed.Add(destroyed);
                            }
                        }
                        ///////// END Business Logic ///////////////

                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                    }
                }
            }
        );
        /*             END executionStrategy                   */
        
        return Json(result);
    }
    else
    {
        return StatusCode(400, "The models contain invalid property values.");
    }
}

as result I had the following error:

The connection does not support MultipleActiveResultSets

Then I modified the connection string, including MultipleActiveResultSets=True, but finally obtained another error:

A second operation was started on this context before a previous operation completed. This is usually caused by different threads concurrently using the same instance of DbContext. For more information on how to avoid threading issues with DbContext, see https://go.microsoft.com/fwlink/?linkid=2097913.

I don't use in that code asynchronous code, I don't know what is wrong...

Answer 1

I needed to verify all my repository Async calls, and see if all of them was "awaited".

I found one that was not "awaited"... fixing that permitted me to save the objects from the Grid(or Spreadsheet) in the DB. More information is in the MS docs on EF Core Asynchronous Programming...