How to create a dummy database for functional tests in .NET Core?

Question

My app is built on .NET Core with Entity Framework 6. There are some complex C# LINQ queries that I am making against the db context. While I have XUnit unit tests that mock up the db and check the query results, I've found the behaviour is not always the same. How can I write tests that create some sort of a relational dummy db that would behave exactly as the real db, seed the db, execute and test the results of the query and then delete/tear down the db? I haven't been able to find something. There are some articles that recommend creating a local db separately, but I would like a technique that does everything including db creation and deletion as part of my testing suite.

Answer 1

If you want to write tests against a real database (and that is a good idea imo, if you're able to minimize a count of such tests to avoid too long test runs) you have no other way rather than manage this database manually: create, initialize and restore it after every test.

And then you have a few options:

• use the only or a few predefined databases (you could parallelize tests, if have multiple databases);
• use docker with sth like TestContainers to create databases on-demand.

The former approach is simpler, the latter gives more control in general.

Now you have one problem solved and three more left:

1. execute your DDL to initialize database structure;
2. seed data needed for tests and clean it up;
3. integrate it with you test framework.

Problem 1

Basically, you need to apply the same approach, that you use on production whether it's EF migrations, other migrations library, dbproj or manual application of scripts.

It might be as simple as execution of the only script with SqlCommand

async Task ExecuteAsync(string script)
{
    await using var connection = new SqlConnection(connectionString);
    await using var command = connection.CreateCommand();
    command.CommandText = script;
    await connection.OpenAsync();
    return await command.ExecuteNonQueryAsync();
}

Or you could use sth like DbUp for more complex scenarios.

Problem 2

There are a few approaches to seed your data:

• each test creates entities it needs;
• reused piece of code to create all entities for all the tests at once;
• manually written sql script;
• use some libraries. I know a few: NDbUnit, which is somewhat outdated, but there a few more-or-less fresh forks and Reseed which is still in a development phase (I'm an author of it);

And also there are a few approaches to clean it up afterwards:

• create a database from scratch for every test;
• restore database from backup;
• restore database from shapshot;
• wrap tests in transaction and then revert it;
• restore changes done in each test in the same test manually;
• use insert and delete sql scripts to restore data to its initial state;
• use libraries to insert and delete data for you. Reseed is also able to handle that and also there is Respawn.

Problem 3

You need to integrate with your test framework by executing a few actions to setup/teardown your database before and after fixtures/fixture/test run. Different test framework provide different means for doing that:

• Use Collection fixtures approach for xUnit described by Joshua in another answer;
• Use OneTimeSetup, OneTimeTearDown, FixtureSetUp, FixtureTearDown, SetUp, TearDown attributes for NUnit;
• Use AssemblyInitialize, AssemblyCleanup, ClassInitialize, ClassCleanup, TestInitialize, TestCleanup for MSTest.

---

I personally use DbUp, TestContainers and Reseed to prepare a database for tests.

Here is a snippet to start db in docker:

public sealed class SqlServerContainer: IAsyncDisposable
{
    private readonly string scriptsFolder;
    private readonly Func<string, bool> scriptFilter;
    private readonly MsSqlTestcontainer server;

    public string ConnectionString => server.ConnectionString;

    public SqlServerContainer(string scriptsFolder)
        : this(scriptsFolder, _ => true)
    {
    }

    public SqlServerContainer(string scriptsFolder, Func<string, bool> scriptFilter)
    {
        this.scriptsFolder = scriptsFolder ?? throw new ArgumentNullException(nameof(scriptsFolder));
        this.scriptFilter = scriptFilter ?? throw new ArgumentNullException(nameof(scriptFilter));
        this.server = CreateDatabaseContainer();
    }

    public async Task StartAsync()
    {
        await StartServerAsync();
        EnsureDatabase.For.SqlDatabase(server.ConnectionString);

        var migrationResult = MigrateDatabase(
            server.ConnectionString,
            scriptsFolder,
            scriptFilter);

        if (!migrationResult.Successful)
        {
            throw new InvalidOperationException("Can't apply database migrations", migrationResult.Error);
        }
    }

    private async Task StartServerAsync()
    {
        try
        {
            await server.StartAsync();
        }
        catch (TimeoutException ex)
        {
            throw new InvalidOperationException(
                "Can't start sql server container, make sure docker is running",
                ex);
        }
    }

    public ValueTask DisposeAsync() => 
        this.server.DisposeAsync();

    private static MsSqlTestcontainer CreateDatabaseContainer() =>
        new TestcontainersBuilder<MsSqlTestcontainer>()
            .WithDatabase(new DbContainerConfiguration("MsSqlContainerDb", "!A1B2c3d4_"))
            .WithName($"sql-db_{Guid.NewGuid()}")
            .Build();

    private static DatabaseUpgradeResult MigrateDatabase(
        string connectionString,
        string scriptsPath,
        Func<string, bool> scriptFilter)
    {
        var upgrade = DeployChanges.To
            .SqlDatabase(connectionString, "dbo")
            .WithScriptsFromFileSystem(scriptsPath, scriptFilter)
            .LogToConsole()
            .JournalTo(new NullJournal())
            .WithTransactionPerScript()
            .Build();
            
        return upgrade.PerformUpgrade();
    }
}

public sealed class DbContainerConfiguration : TestcontainerDatabaseConfiguration
{
    private const string ServerImage = "mcr.microsoft.com/mssql/server:latest";
    private const string PasswordKey = "SA_PASSWORD";
    private const string EulaKey = "ACCEPT_EULA";
    private const int ServerPort = 1433;

    public DbContainerConfiguration(string database, string password) : base(ServerImage, ServerPort)
    {
        Environments[EulaKey] = "Y";
        Password = password;
        Database = database;
    }

    public override string Username => "sa";

    public override string Password
    {
        get => Environments[PasswordKey];
        set => Environments[PasswordKey] = value;
    }

    public override IWaitForContainerOS WaitStrategy => Wait
        .ForUnixContainer()
        .UntilPortIsAvailable(ServerPort);
}

Answer 2

I think the combination of what Joshua has provided for integrating with xUnit, and the following should allow you to test database instances. I have included partial source for a POC that I created to test EF6 Migrations. The main type that you need to setup the database is DbMigratorHarness<T>, and it can be used to create the new database. It requires the migration configuration type, which sets up code first migrations:

var harness = new DbMigratorHarness<Migrations.Configuration>();
harness.LatestMigration();

var context = new MyDbContext(harness.ConnectionString);
// Do work with context

Exposing the harness, or the context via the EfFixture type would allow you access to an isolated database instance.

Here is the partial implementation of DbMigratorHarness<T>. The full source is available at github.com/joncloud/ef6-migration-testing.

using System;
using System.Data.Entity.Infrastructure;
using System.Data.Entity.Migrations;
using System.Data.SqlClient;

public class DbMigratorHarness<T> : IDisposable
    where T : DbMigrationsConfiguration, new()
{
    public string ConnectionString { get; }

    readonly DbMigrator _migrator;
    
    public DbMigratorHarness()
    {
        ConnectionString = CreateNewSqlConnection();
        CreateDatabase();

        var dbConnectionInfo = new DbConnectionInfo(ConnectionString, "System.Data.SqlClient");
        var configuration = new T
        {
            TargetDatabase = dbConnectionInfo
        };
        _migrator = new DbMigrator(configuration);
    }

    static string CreateNewSqlConnection()
    {
        // Note it may be important to change the connection string here depending upon how your database connection works.
        var baseConnectionString = "Data Source=.;Initial Catalog=TestDbContext;Integrated Security=true;";
        var builder = new SqlConnectionStringBuilder(baseConnectionString);
        builder.InitialCatalog += Guid.NewGuid().ToString();
        return builder.ConnectionString;
    }

    string GetDatabaseName()
    {
        var builder = new SqlConnectionStringBuilder(ConnectionString);
        return builder.InitialCatalog;
    }

    string GetMasterConnectionString()
    {
        var builder = new SqlConnectionStringBuilder(ConnectionString);
        builder.InitialCatalog = "master";
        return builder.ConnectionString;
    }

    void CreateDatabase()
    {
        var masterConnectionString = GetMasterConnectionString();
        using (var connection = new SqlConnection(masterConnectionString))
        {
            connection.Open();

            using (var command = connection.CreateCommand())
            {
                var databaseName = GetDatabaseName();
                command.CommandText = $@"
                    CREATE DATABASE [{databaseName}]
                ";
                command.ExecuteNonQuery();
            }
        }
    }

    void DropDatabaseIfExists()
    {
        var masterConnectionString = GetMasterConnectionString();
        using (var connection = new SqlConnection(masterConnectionString))
        {
            connection.Open();

            using (var command = connection.CreateCommand())
            {
                var databaseName = GetDatabaseName();
                command.CommandText = $@"
                    IF EXISTS(SELECT 1 FROM sys.databases WHERE name = '{databaseName}')
                    BEGIN
                        ALTER DATABASE [{databaseName}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
                        DROP DATABASE [{databaseName}];
                    END
                ";
                command.ExecuteNonQuery();
            }
        }
    }

    public void LatestMigration()
    {
        _migrator.Update();
    }

    #region IDisposable Support
    private bool disposedValue = false; // To detect redundant calls

    protected virtual void Dispose(bool disposing)
    {
        if (!disposedValue)
        {
            DropDatabaseIfExists();

            disposedValue = true;
        }
    }
    
    ~DbMigratorHarness()
    {
        // Do not change this code. Put cleanup code in Dispose(bool disposing) above.
        Dispose(false);
    }

    // This code added to correctly implement the disposable pattern.
    public void Dispose()
    {
        // Do not change this code. Put cleanup code in Dispose(bool disposing) above.
        Dispose(true);
        GC.SuppressFinalize(this);
    }
    #endregion
}

Answer 3

With xUnit, you can use Collection Fixtures to accomplish something like this.

When to use: when you want to create a single test context and share it among tests in several test classes, and have it cleaned up after all the tests in the test classes have finished.

Sometimes you will want to share a fixture object among multiple test classes. The database example used for class fixtures is a great example: you may want to initialize a database with a set of test data, and then leave that test data in place for use by multiple test classes. You can use the collection fixture feature of xUnit.net to share a single object instance among tests in several test class.

To use collection fixtures, you need to take the following steps:

• Create the fixture class, and put the startup code in the fixture class constructor.
• If the fixture class needs to perform cleanup, implement IDisposable on the fixture class, and put the cleanup code in the Dispose() method.
• Create the collection definition class, decorating it with the [CollectionDefinition] attribute, giving it a unique name that will identify the test collection.
• Add ICollectionFixture<> to the collection definition class.
• Add the [Collection] attribute to all the test classes that will be part of the collection, using the unique name you provided to the test collection definition class's [CollectionDefinition] attribute.
• If the test classes need access to the fixture instance, add it as a constructor argument, and it will be provided automatically.

You could create some class, EfFixture, which implements IDisposable and composes an instance of your Entity Framework DbContext. In the constructor of this class, you would run your scripts or migrations to create the database schema using something like (localdb)\mssqllocaldb as the server. Also in the constructor, you would seed the database.

Then, in the Dispose method you would remove your database from the server.

public class EfFixture
{
   public EfFixture()
   {
      // Initialize the YourDbContext property, run migrations or scripts to create
      // the schema, seed the database.
   }

   public Dispose()
   {
      // Tear down your test database.
   }

   public DbContext YourDbContext { get; }
}

You need a special, empty class which has the CollectionDefinition attribute and implements ICollectionFixture<EfFixture>.

[CollectionDefinition("EfCollection")]
public class EfCollection : ICollectionFixture<EfFixture>
{
}

Then, you mark your test class as being part of the collection using the Collection attribute. You can pass an instance of EfFixture to your test class' constructor.

[Collection("EfCollection")]
public class TestClass1
{
   public TestClass1(EfFixture fixture)
   {
      Fixture = fixture;
   }

   EfFixture Fixture { get; }
}