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