EF Core In-Memory Database Test Is Flaky: Data Not Persisting Consistently Between Queries

Question

I’m using EF Core’s in-memory database for unit testing a job that processes event queues and updates existing events in the database. The problem is that my test sometimes passes but often fails, with inconsistencies in data retrieval. Specifically, the test sometimes can’t find an Event entity that was explicitly added to the database during the test setup.

Problem Summary:

1. I add data to the database using DbContext in the PopulateDb method.
2. During the execution of my job, I query the Events table using FirstOrDefaultAsync.
3. Occasionally, the FirstOrDefaultAsync query returns null, even though the data was successfully added in PopulateDb.

Here’s an example:

Populate DB function

private async Task PopulateDb(ApplicationDbContext dbContext)
{
    var eventQueue = FakerGenerator.EventQueue()
        .RuleFor(p => p.EventUid, "test-event-01")
        .RuleFor(e => e.Start, DateTime.Parse("2022-01-01T00:00:00"))
        .Generate()

    var existingEvent = FakerGenerator.Event()
        .RuleFor(e => e.Uid, "test-event-01")
        .RuleFor(e => e.StartDate, DateTime.Parse("1999-01-01T00:00:00"))
        .Generate();

    dbContext.EventsQueue.AddRange(eventsQueue);
    var addedEventQueues = await dbContext.SaveChangesAsync();

    dbContext.Events.Add(existingEvent);
    var addedEvents = await dbContext.SaveChangesAsync();

    Assert.Equal(1, addedEventQueues);
    Assert.Equal(1, addedEvents);
}

Test Function

[Fact]
public async Task Execute_ShouldSendEmailsForNewEvents()
{
    var options = new DbContextOptionsBuilder<ApplicationDbContext>()
        .UseInMemoryDatabase(Guid.NewGuid().ToString())
        .Options;

    var dbContext = new ApplicationDbContext(options);

    await PopulateDb(dbContext);

    var emailServiceMock = new Mock<EmailService>(dbContext);

    var job = new QueuedEventNotifierJob(
        dbContext,
        emailServiceMock.Object
    );

    await job.Execute();

    var updatedEvent = await dbContext.Events.AsNoTracking().FirstAsync(e => e.Uid == "test-event-01");

    // This part fails most of the time but sometimes it is asserter successfully
    Assert.Equal(DateTime.Parse("2022-01-01T00:00:00"), updatedEvent.StartDate);
}

The execute function in the job

public async Task Execute()
{
    var events = await dbContext.EventsQueue
        .Where(e => e.Status == EventQueueStatus.New)
        .ToListAsync();

    foreach (var unprocessedEvent in events)
    {
        var existingEvent = await dbContext.Events.FirstOrDefaultAsync(e => e.Uid == unprocessedEvent.EventUid);

        // desperately trying to catch something
        if (unprocessedEvent.EventUid == "test-event-01" && existingEvent == null)
            throw new Exception("Event not found");

        if (existingEvent != null) {
           existingEvent.StartDate = unprocessedEvent.Start;
           dbContext.Events.Update(existingEvent);
           await dbContext.SaveChangesAsync();
        }
    }
}

The test fails intermittently with the following error:

Xunit.Sdk.EqualException
Assert.Equal() Failure: Values differ
Expected: 2022-01-01T00:00:00.0000000
Actual:   1999-01-01T00:00:00.0000000Z

Environment:

• EF Core Version: 8.0.7
• Test Framework: xUnit 2.5.3
• In-Memory Database: EF Core UseInMemoryDatabase 9.0.0

Answer 1

Unit tests are typically run in parallel by default, including XUnit. This can lead to unexpected race conditions where tests end up relying on the same shared resources like a setup persistence.

For unit tests, mock the persistence rather than trying to substitute the underlying database /w something like an in-memory database. For code that you want to test, all you need to assert is that an entity was modified or requested to be added, not test the fact that EF actually updates/inserts a row. (Entity Framework's capabilities are already tested) There are examples out there to mock the EF DbContext/DbSet or what I generally recommend is to use a thin abstraction /w IQueryable to make mocking operations easier. I personally use a unit of work abstraction over the DbContext called DbContextScope alongside IQueryable repository abstractions which are easier to mock.

For persistence tests, which would be integration type tests rather than unit tests, consider using an instance of the same database engine as you will be using in production rather than something like the in-memory database. Different database providers have different behaviour and the In Memory database provider especially has various warnings to discourage its use, even for testing. "using the in-memory provider for testing is strongly discouraged". This should also include setting up the tests to either run sequentially, or ensuring that each test has access to its own persistence store or shares a persistence store with other tests that you ensure will not tread on each other's data state. Persistence tests will be slower than using mocks, and require some setup and teardown so they are suited to integration, and you would want to ensure that all aspects of the persistence mirror what would happen in production.