Damir's Corner.

EF Core Integration Testing with SQL LocalDB

In a recent discussion of pros and cons of using EF Core in-memory database provider for testing, the idea of using SQL Server Express LocalDB instead came up. I remembered reviewing an article about this last year. But after reading it once again, it turned out that some work would still be required to create a working sample.

As the first step, I had to create a DbContext instance with the correct database provider and connection string. I decided to implement the IDesignTimeDbContextFactory<TContext> interface which could also be used by the EF Core Migrations commands:

public class SampleDbContextFactory : IDesignTimeDbContextFactory<SampleDbContext>
{
  public SampleDbContext CreateDbContext(string[] args)
  {
    var optionsBuilder = new DbContextOptionsBuilder<SampleDbContext>();
    optionsBuilder.UseSqlServer(
      "Server=(localdb)\\mssqllocaldb;Database=EFSample.Testing;Trusted_Connection=True;");

    return new SampleDbContext(optionsBuilder.Options);
  }
}

I could then use it in the base class for all integration tests to provide the DbContext instance in a simple and reliable way:

public abstract class IntegrationTestBase
{
  protected SampleDbContext dbContext = null!;

  [SetUp]
  public void SetUp()
  {
    var dbContextFactory = new SampleDbContextFactory();
    dbContext = dbContextFactory.CreateDbContext(new string[] { });
  }

  [TearDown]
  public void TearDown()
  {
    if (dbContext != null)
    {
      dbContext.Dispose();
    }
  }
}

Using this base class, the tests can use the DbContext instance knowing a new instance will be provided for them and properly disposed of afterwards:

[TestFixture]
public class PersonTests : IntegrationTestBase
{
  [Test]
  public async Task AddPerson()
  {
    var person = new Person("John", "Doe");

    dbContext.Persons.Add(person);
    await dbContext.SaveChangesAsync();

    var personFromDb = dbContext.Persons.Find(person.Id);
    Assert.AreEqual(personFromDb.FirstName, person.FirstName);
    Assert.AreEqual(personFromDb.LastName, person.LastName);
  }
}

There's still the most important part to take care of. Before running the tests, a clean and up-to-date database must be created. I created a SetUpFixture class for that:

[SetUpFixture]
public class IntegrationSetUp
{
  [OneTimeSetUp]
  public async Task SetUp()
  {
    var dbContextFactory = new SampleDbContextFactory();
    using (var dbContext = dbContextFactory.CreateDbContext(new string[] { }))
    {
      await dbContext.Database.EnsureDeletedAsync();
      await dbContext.Database.EnsureCreatedAsync();
    }
  }
}

I use the EnsureDeletedAsync method to delete the database that might have been left over from the previous test run. This way I can be sure that the EnsureCreatedAsync method will have to create a fresh database with no data and all the migrations applied. I considered also deleting the database in the OneTimeTearDown method but decided against it so that the database would be available for troubleshooting in case of failed tests.

Another design decision was not to clean the database after each test. The easiest way to achieve that would be by modifying the base class to begin a transaction before each test and roll it back after the test. However, this would also mean that the there would be no way to review the data in the database after a failed test.