DbUpdateConcurrencyException error when adding a new record to the database during test method execution

Question

I'm developing an ASP.NET Core 8 Web API. Here is the interface of my repository that belongs to the data access layer:

public interface IWorkTypeRepository
{
    Task<IEnumerable<WorkTypeModel>> GetAll(PageOptionsModel pageOptions = null);
    Task<WorkTypeModel> GetById(Guid workTypeId);

    Task Create(WorkTypeModel workTypeModel);
    Task Update(WorkTypeModel workTypeModel);
    Task Delete(Guid workTypeId);
}

This is the implementation of that interface:

public class WorkTypeRepository : IWorkTypeRepository
{
     private readonly ContractGpdDbContextBase _contractGpdDbContext;

     public WorkTypeRepository(ContractGpdDbContextBase contractGpdDbContext)
     {
         _contractGpdDbContext = contractGpdDbContext;
     }

     public async Task<IEnumerable<WorkTypeModel>> GetAll(PageOptionsModel pageOptions = null)
     {
         return await _contractGpdDbContext.WorkTypes.AsNoTracking()
                                           .Include(p => p.WorkUnit)
                                           .OrderBy(p => p.Id)
                                           .Page(pageOptions)
                                           .ToListAsync();
     }

     public async Task<WorkTypeModel> GetById(Guid workTypeId)
     {
         return await _contractGpdDbContext.WorkTypes
                                           .AsNoTracking()
                                           .Include(p => p.WorkUnit)
                                           .SingleOrDefaultAsync(p => p.Id == workTypeId);
     }

     public async Task Create(WorkTypeModel workTypeModel)
     {
         ArgumentNullException.ThrowIfNull(workTypeModel);

         await _contractGpdDbContext.AddAsync(workTypeModel);
         await _contractGpdDbContext.SaveChangesAsync();

         _contractGpdDbContext.ChangeTracker.Clear();  
     }

     public async Task Update(WorkTypeModel workTypeModel)
     {
         ArgumentNullException.ThrowIfNull(workTypeModel);

         _contractGpdDbContext.Update(workTypeModel);

         await _contractGpdDbContext.SaveChangesAsync();

         _contractGpdDbContext.ChangeTracker.Clear(); 
     }

     public async Task Delete(Guid workTypeId)
     {
        var workTypeModel = new WorkTypeModel
        {
           Id = workTypeId
        };

        _contractGpdDbContext.Remove(workTypeModel);

        await _contractGpdDbContext.SaveChangesAsync();
    }
}

The basic context for working with the database is represented by the following code:

public abstract class ContractGpdDbContextBase : DbContext
{
   public DbSet<WorkUnitModel> WorkUnits { get; set; }
   public DbSet<WorkTypeModel> WorkTypes { get; set; }

   protected ContractGpdDbContextBase(DbContextOptions options) : base(options) { }
}

I want to verify that the Delete method is functioning correctly using XUnit.

Here is the class for testing the Delete method:

public class DeleteTests : IClassFixture<WorkTypeRepositoryFixture>
{
    private readonly IWorkTypeRepository _workTypeRepository;

    public DeleteTests(WorkTypeRepositoryFixture fixture)
    {
       _workTypeRepository = fixture?.WorkTypeRepository ?? throw new ArgumentNullException(nameof(fixture));
    }

    [Theory]
    [MemberData(nameof(WorkTypeRepositoryTestData.DeleteWithCorrectDataTestData),
                MemberType = typeof(WorkTypeRepositoryTestData))]
    public async Task DeleteWithCorrectDataTest(WorkTypeModel workTypeModel)
    {      
        await _workTypeRepository.Create(workTypeModel);
        await _workTypeRepository.Delete(workTypeModel.Id);

        var expected = await _workTypeRepository.GetById(workTypeModel.Id);
    
        expected.Should().BeNull();
    }

    [MemberData(nameof(WorkTypeRepositoryTestData.DeleteNotExistedWorkTypeTestData),
                MemberType = typeof(WorkTypeRepositoryTestData))]

    public async Task DeleteNotExistedWorkTypeTest(Guid workTypeId)
    {
       var action = async () => await _workTypeRepository.Delete(workTypeId);

       await action.Should().ThrowAsync<DbUpdateConcurrencyException>();    
    }
}

WorkTypeRepositoryFixture class code:

public class WorkTypeRepositoryFixture
{
    public IWorkTypeRepository WorkTypeRepository { get; }

    public WorkTypeRepositoryFixture()
    {
        var optionsBuilder = new DbContextOptionsBuilder<ContractGpdDbContext>();
        optionsBuilder.UseSqlServer(ConfigurationHelper.GetConnectionString());
        var options = optionsBuilder.Options;

        WorkTypeRepository = new WorkTypeRepository(new ContractGpdDbContext(options));
    }
}

It should be noted that the same repository instance will be stored in the _workTypeRepository field when the DeleteTests methods are executed.

Test data is defined in the WorkTypeRepositoryTestData class, which has the following code:

public class WorkTypeRepositoryTestData : BaseTestData
{
   /* Code for other tests */

   public static TheoryData<WorkTypeModel> DeleteWithCorrectDataTestData()
   {
      return new TheoryData<WorkTypeModel>
      {
        new WorkTypeModel()
        {
            Id = Guid.NewGuid(), 
            Name = $"Вид работ {Guid.NewGuid()}"
        },
        new WorkTypeModel()
        {
            Id = Guid.NewGuid(), 
            Name = $"Вид работ {Guid.NewGuid()}",
            WorkUnitId = 1
        },
        new WorkTypeModel()
        {
            Id = Guid.NewGuid(), 
            Name = $"Вид работ {Guid.NewGuid()}",
            WorkUnitId = 2
        }
     };
   }

   public static TheoryData<Guid> DeleteNotExistedWorkTypeTestData()
   {
      return new TheoryData<Guid>
      {
            Guid.Empty,
            Guid.NewGuid()
      };
   }

  /*Code for other tests*/
}

When the DeleteWithCorrectDataTest method is run, it executes without errors.

However, if I run DeleteTests, the DeleteWithCorrectDataTest method executes with an error:

Error text

Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException: the database operation was expected to affect 1 row(s), but actually affected 0 row(s); data may have been modified or deleted since entities were loaded. See https://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.

at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.ThrowAggregateUpdateConcurrencyExceptionAsync(RelationalDataReader reader, Int32 commandIndex, Int32 expectedRowsAffected, Int32 rowsAffected, CancellationToken cancellationToken)

Lines with an Id that starts with '8e45d3a4' are indeed missing from the database. It should be so, because in the test I first add a record to the database, and then use the Delete method to delete it.

Question: how to fix this error?

PS: maybe some customization is required at XUnit level?

Answer 1

Made changes to my repository WorkTypeRepository code and all tests were successful. Here's the code:

public class WorkTypeRepository : IWorkTypeRepository
{

    private readonly ContractGpdDbContextBase _dbContext;

    public WorkTypeRepository(ContractGpdDbContextBase dbContext)
    {
       _dbContext = dbContext;
    }

    public async Task<IEnumerable<WorkTypeModel>> GetAllAsync(PageOptionsModel pageOptions = null)
    {
        return await _dbContext.WorkTypes.AsNoTracking()
                                         .Include(p => p.WorkUnit)
                                         .OrderBy(p => p.Id)
                                         .Page(pageOptions)
                                         .ToListAsync();
    }

    public async Task<WorkTypeModel> GetByIdAsync(Guid workTypeId)
    {
        return await _dbContext.WorkTypes.AsNoTracking()
                                         .Include(p => p.WorkUnit)
                                         .SingleOrDefaultAsync(p => p.Id == workTypeId);
    }

    public async Task CreateAsync(WorkTypeModel workTypeModel)
    {
       ArgumentNullException.ThrowIfNull(workTypeModel);

       await _dbContext.AddAsync(workTypeModel);
    
       try
       {
          await _dbContext.SaveChangesAsync();
       } 
       finally
       {
          _dbContext.Entry(workTypeModel).State = EntityState.Detached;
       }
    }

    public async Task UpdateAsync(WorkTypeModel workTypeModel)
    {
       ArgumentNullException.ThrowIfNull(workTypeModel);

       _dbContext.Update(workTypeModel);

       try
       {
          await _dbContext.SaveChangesAsync();
       } 
       finally
       {
         _dbContext.Entry(workTypeModel).State = EntityState.Detached;
       }      
    }

    public async Task DeleteAsync(Guid workTypeId)
    {
       var workTypeModel = new WorkTypeModel
       {
          Id = workTypeId
       };

       _dbContext.Remove(workTypeModel);

       try
       {
          await _dbContext.SaveChangesAsync();
       } 
       finally
       {
          _dbContext.Entry(workTypeModel).State = EntityState.Detached;
       }     
    }
}