How to implement database resiliency while having a scoped Dbcontext in EF Core?

Question

I have a simple command as per the CQRS pattern as follows:

public sealed class EditPersonalInfoCommandHandler : ICommandHandler<EditPersonalInfoCommand> {

        private readonly AppDbContext _context;

        public EditPersonalInfoCommandHandler(AppDbContext context) {
            _context = context;
        }

        public Result Handle(EditPersonalInfoCommand command) {
            var studentRepo = new StudentRepository(_context);
            Student student = studentRepo.GetById(command.Id);
            if (student == null) {
                return Result.Failure($"No Student found for Id {command.Id}");
            }

            student.Name = command.Name;
            student.Email = command.Email;

            _context.SaveChanges();
            return Result.Success();
        }

}

Now I have a requirement to attempt _context.SaveChanges() upto 5 times if it fails with an exception. For this I can simply have a for loop in the method as:

for(int i = 0; i < 5; i++) {
    try {
        //required logic
    } catch(SomeDatabaseException e) {
        if(i == 4) {
           throw;
        }
    }
}

The requirement is to execute the method as a single unit. The thing is that once the _context.SaveChanges() throws an exception, the same _context cannot be used to reattempt the logic. The docs say:

Discard the current DbContext. Create a new DbContext and restore the state of your application from the database. Inform the user that the last operation might not have been completed successfully.

However, in the Startup.cs, I have the AppDbContext as a scoped dependency. In order to reattempt the method logic I require a new instance of AppDbContext but being registered as scoped will not allow that.

One solution that comes to my mind is to make the AppDbContext transient. But I have a feeling that by doing that I will open whole set of new problems for myself. Can anyone help me with it?

@mjwills DbUpdateException thrown on the failure of _context.SaveChanges(). — Navjot Singh, Mar 10 '21 at 23:32

King King · Accepted Answer · 2021-03-11T15:12:36.210

There are at least too kinds of error while saving the context. The first occurs during the command execution. The second occurs during committing (which much more rarely happens). The second error may happen even when the data has been updated successfully. So your code just handles the first kind of error but does not take the second kind into account.

For the first kind of error, you can inject into your command handler a DbContext factory or use IServiceProvider directly. It's kind of anti-pattern but in this case we have no choice, like this:

readonly IServiceProvider _serviceProvider;
public EditPersonalInfoCommandHandler(IServiceProvider serviceProvider) {
        _serviceProvider = serviceProvider;
}

for(int i = 0; i < 5; i++) {
  try {
    using var dbContext = _serviceProvider.GetRequiredService<AppDbContext>();
    //consume the dbContext
    //required logic
  } catch(SomeDatabaseException e) {
    if(i == 4) {
       throw;
    }
  }
}

However as I said, to handle both kind of errors, we should use the so-called IExecutionStrategy in EFCore. There are several options as introduced here. But I think the following would suit your scenario best:

public Result Handle(EditPersonalInfoCommand command) {
    var strategy = _context.Database.CreateExecutionStrategy();
    
    var studentRepo = new StudentRepository(_context);
    Student student = studentRepo.GetById(command.Id);
    if (student == null) {
        return Result.Failure($"No Student found for Id {command.Id}");
    }

    student.Name = command.Name;
    student.Email = command.Email;
    const int maxRetries = 5;
    int retries = 0;
    strategy.ExecuteInTransaction(_context,
                                  context => {
                                      if(++retries > maxRetries) {
                                         //you need to define your custom exception to be used here
                                         throw new CustomException(...);
                                      }
                                      context.SaveChanges(acceptAllChangesOnSuccess: false);
                                  },
                                  context => context.Students.AsNoTracking()
                                                    .Any(e => e.Id == command.Id && 
                                                              e.Name == command.Name &&
                                                              e.Email == command.Email));

    _context.ChangeTracker.AcceptAllChanges();
    return Result.Success();
}

Note that I suppose your context exposes a DbSet<Student> via the property Students. If you have any other error not related to connection, it will not be handled by the IExecutionStrategy and that makes a lot of sense. Because that's when you need to fix the logic, retrying thousands of times won't help and always ends up in that error. That's why we don't need to care about the detailed originally thrown Exception (which is not exposed when we use IExecutionStrategy). Instead we use a custom Exception (as commented in my code above) to just notify a failure of saving the changes due to some connection-related issue.

Is the creation of new `context` for each retry taken care of internally in `ExecuteInTransaction` (considering that `context` is still added as a scoped dependency)? Also any specific reason for returning as `AsNoTracking()` ? — Navjot Singh, Mar 10 '21 at 23:38
@NavjotSingh the `ExecuteInTransaction` will care everything for you, one same context is used here (no new instance is created each retry) because we don't use a factory as the first approach of using `IServiceProvider`. Using `AsNoTracking` for the best performance of querying only data (here the query to verify the success of update). — King King, Mar 10 '21 at 23:42

How to implement database resiliency while having a scoped Dbcontext in EF Core?

1 Answers1