I'm encountering an issue when executing transactions on system-versioned tables (SQL Azure) in my ASPNET Core + Entity Framework app.
In my database, there are two system-versioned tables:
- dbo.Entities
- dbo.Tasks (has an external reference to Entities)
The affected method performs following actions:
- Retrieve main entity.
- Retrieve associated task by entity.
- Perform business logic outside transaction.
- Start transaction.
- Update main entity.
- Update task's steps.
- If task should be completed, append a message.
- Complete the task.
- Save changes and commit the transaction.
Here's the method I'm using:
public async Task<Result<GenericOutcomeResponse>> HandleGenericOutcomeAsync(int entityId, GenericOutcomeRequest request, CancellationToken cancellationToken)
{
// 1. retrieve main entity
var entity = await _dbContext.Entities.FindAsync([entityId], cancellationToken: cancellationToken);
// 2. Retrieve associated task by entity
var task = await _taskService.GetOpenedTaskByTypeIdAsync(entityId, TaskTypeEnum.TypeA, true, cancellationToken);
// 3. Perform business logic outside transaction
var upcomingPhase = await CalculateNextStepAsync(entityId, request, cancellationToken);
var shouldCompleteTask = upcomingPhase is StepEnum.Done or StepEnum.RequiresVerification;
// 4. Start transaction
var transaction = await _dbContext.Database.BeginTransactionAsync(cancellationToken);
try
{
// 5. Update main entity
switch (request.PreviousPhase)
{
case StepEnum.StepA:
entity.SomeProperty = request.StepAData!.SomeValue;
break;
case StepEnum.StepB:
entity.Notes = request.StepBData!.Note;
break;
}
// 6. Update task's steps.
UpdateTaskSteps(task, (int)request.PreviousPhase, (int)upcomingPhase);
// 7. If task should be completed, append a message.
if (shouldCompleteTask)
{
task.Messages.Add(new Messages
{
Message = message
});
}
// 8. Complete the task.
await _taskService.CompleteAsync(task.Id, cancellationToken);
// 9. Save changes and commit the transaction.
await _dbContext.SaveChangesAsync(cancellationToken);
await transaction.CommitAsync(cancellationToken);
return new GenericOutcomeResponse(upcomingPhase);
}
catch
{
await transaction.RollbackAsync(cancellationToken);
return GenericServiceErrors.GenericError;
}
finally
{
// Dispose the transaction if it was started locally
if (!hasExternalTransaction)
await transaction.DisposeAsync();
}
}
private void UpdateTaskSteps(GenericTask task, int previousPhase, int upcomingPhase)
{
// Get the entity tracking entry for the task
var entry = _dbContext.Entry(task);
// Check if the task is not currently tracked by the context
var isDetached = entry.State == EntityState.Detached;
// Attach the task to the context if it's detached
if (isDetached)
_dbContext.Attach(task);
// Update the task's phase tracking properties
task.SetPreviousPhase(previousPhase);
task.SetUpcomingPhase(upcomingPhase);
// Mark the phase property as modified if the entity was detached
if (isDetached)
entry.Property(x => x.Steps).IsModified = true;
}
The problem occurs when I run this method simultaneously (for different entities) and I get the following error:
An error occurred while saving the entity changes. See the inner exception for details. Data modification failed on system-versioned table 'server.dbo.Entities' because transaction time was earlier than period start time for affected records.
Has anyone encountered this issue before? Any suggestions on how to mitigate this?
Attach()can also run foul of other tracking exceptions like "An entity with this Id is already tracked". I do generally recommend avoiding explicit detach/attach designs /w EF. It has to be done quite carefully and still can run foul of several issues like this.