Integrative Persistence Tests for .NET Core Applications

The project

Integration tests with persistence can be tricky. On one hand, you want tests to run in an environment that's as close to the real thing as possible. On the other hand, you don't want tests to change a shared database, mess up a real system, or interfere with other tests.

I'm currently working on a .NET Core project that includes some integration persistence tests, but I chose a method that led to problematic outcomes. The application uses Oracle as a database, and the application code itself works with the DbContext.

Unit Tests for Application Services Are Challenging Due to DbContext

DbContext can be mocked, and there are libraries for this, such as Moq.EntityFrameworkCore. However, I found that mocking DbContext often doesn't work well, especially with more complex queries and behaviors. Tests for application services that directly access DbContext (or a mock) need a lot of setup and don't focus solely on the service logic.

We decided to use repositories and a unit of work abstraction throughout.

interface ICustomerRepository
{
    Task<Customers[]> GetCustomersAsync(CancellationToken cancellationToken);
    Task Add(Customer customer);
}

interface IMyUnitOfWork
{
    Task SaveChangesAsync(CancellationToken cancellationToken);
}

The repository prevents direct access to DbSets on the DbContext, and the unit of work handles saving changes. With this setup, writing unit tests for persistence becomes easy because you can simply mock the repositories and unit of work.

I know this is somewhat controversial in the .NET community, and there's a heated debate about whether repositories are needed at all since LINQ provides such a good abstraction for data access. In my opinion, the key point is that when you run your LINQ queries in your application services, EF Core namespaces appear, and the data layer merges into your service.

Using the In-Memory Provider for Integration Tests

This approach is the worst. Even Microsoft notes that it's no longer getting new features. The in-memory provider doesn't properly support complex queries or things like referential integrity. This means your tests won't really provide useful insights.

Using SQLite for Integration Tests

I've seen this in projects before. It seems like a good idea at first, but you eventually realize that this database is different from the one you're using. For example, Oracle will silently convert empty strings to null (yes, it's as odd as it sounds), while SQLite does not. This caused tests to pass when they shouldn't. Additionally, there will always be situations where you can't use all your database's features (starting with simple things like data types). It's very problematic when you spend a lot of money on a product and can't use it fully because your tests don't support it.

What I Want to Show Here

The best approach for your integration tests is to use the same database as your live environment. Docker makes this possible on a development machine and in CI/CD pipelines. The remaining question is how to provide a temporary database with a state you can use for your tests. This post will discuss some solutions and findings related to this issue.

The demo solution uses SQLite as a database backend for simplicity. In real projects, you might use a full-fledged database solution like Oracle, PostgreSQL, or SQL Server.

Setting Up a Base State for Your Database

To avoid duplicating your test setup, it would be beneficial to have a hierarchy of database states that become more specific for individual tests. Here is an example:

Getting to the Base State

To move from a clean database to the base state, you can:

Apply migrations
Run SQL scripts
Restore a backup

In my opinion, restoring a backup is the least reliable method. Often, these backups aren't managed like software development, lacking proper testing and a clear change history. You might also face issues if your database gets updated and the backup version becomes unsupported. Additionally, you're closely tied to a specific database vendor.

Applying migrations or running scripts is the simplest approach. In your global test startup, you could include code like this:

using var scope = _serviceScopeFactory.CreateScope();
using var dbContext = scope.ServiceProvider.GetRequiredService<MyDbContext>();

// Apply regular migrations
await dbContext.Database.MigrateAsync(cancellationToken);

You can even include your scripts in EF Core migrations if you want, since migrations can run raw SQL. This gives you the best of both worlds:

The people writing the SQL don't need to be .NET Core developers; they could be database developers, for example.
You can still use EF Core's versioning system and don't need to create your own.

public partial class MigrationV1 : Migration
{
    /// <inheritdoc />
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql("CREATE TABLE FOO");
    }

    /// <inheritdoc />
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql("DROP TABLE FOO");
    }
}

If you have developers who are not familiar with .NET, you might consider using code generation to create migrations from a specific folder where the SQL scripts are stored. The generated migration can then read and execute these scripts.

Returning to the Base State

Once you have a base state, you'll want a method to return to it during test execution. The simplest way to do this in relational databases is by using transactions. You can use the EF Core mechanism for this.

using var transaction = await dbContext.Database.BeginTransactionAsync(cancellationToken);

// do something

await transaction.RollbackAsync(cancellationToken);

… or use ambient transactions

using (var transaction = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
    // do something
}

What if my code uses transactions on its own or I want to support nested states

This was a bit surprising for me. I thought nested transactions would be straightforward, but neither Oracle nor SQLite support them. However, you can use save points with both. A save point is linked to a transaction, and it allows you to return to a specific point within the transaction.

using var transaction = await dbContext.Database.BeginTransactionAsync(cancellationToken);
await transaction.CreateSavepointAsync("BeforeTests", cancellationToken);

await RunTestAsync(dbContext, cancellationToken);

// Test cleanup
transaction.RollbackToSavepoint("BeforeTests");

With multiple save points, you can create a state hierarchy as shown in the diagram above. However, if you try this solution while running an integration test for a service that uses transactions, you might face an issue.

Since the DbContext is registered as Scoped in the DI container, you'll have a different instance in your test setup compared to your service code. Because the transaction operates on the DbConnection of the DbContext, it's unlikely that the transaction will carry over to your service. Even if it did, you would need to manage save points within your service just for the integration tests. This is not an ideal situation.

Changing the Lifecycle for DbContext When Running Integration Tests

You can easily solve this issue for integration tests by changing the lifecycle of the DbContext from Scoped to Singleton. First, set up your DI container using live code, then include a small code snippet to update the registration. You can check what gets registered by looking inside your IServiceCollection after AddDbContext<T> is executed. In my demo solution, I see these five additional entries:

ServiceProviderAccessor (Singleton)
IDbContextOptionsConfiguration<T> (Scoped)
DbContextOptions<T> (Scoped)
DbContextOptions (Scoped)
ManualMigrationsDbContext (Scoped)

To change the lifecycle of the ManualMigrationsDbContext, use the replace method on the service collection.

services.Replace(ServiceDescriptor.Singleton(
    typeof(ManualMigrationsDbContext),
    typeof(ManualMigrationsDbContext)));

This will ensure that the service receives the same instance of the context on which the test fixture created the transaction (with save points).

Abstracting Transaction Handling

This still leaves us with the question of how to keep the service unaware of save points. To address this, we can provide an abstraction for transaction handling in the DI container.

interface ITransactionManager<TDbContext>
  where TDbContext: DbContext
{
    Task<ITransaction> BeginTransactionAsync(CancellationToken cancellationToken);
}

interface ITransaction : IDisposable, IAsyncDisposable
{
    Task CommitAsync(CancellationToken cancellationToken);
    Task RollbackAsync(CancellationToken cancellationToken);
}

class TransactionManager<TDbContext> : ITransactionManager<TDbContext>
  where TDbContext: DbContext
{
    // Do regular transaction handling. Fail on nested transactions
}

class TestTransactionManager<TDbContext>: ITransactionManager<TDbContext>
  where TDbContext: DbContext
{
    // Create initial transaction. Manage a stack of savepoints for nested
    // transactions
}

With this setup, we can use the TestTransactionManager during our integration tests to handle nested database states.

Adding Extra Migrations Beyond the Base State

If you need more specific test data than what your migrations provide, you can write regular code that uses the DbContext to insert data into your database.

dbContext.Customers.Add(new Customer{ ... });
await dbContext.SaveChangesAsync();

If you’re just developers working with the integration tests, this is probably the way to go.

Running migrations imperatively

With this blog post I was thinking about, whether it’s also possible to use migrations to establish the states below the base state and it’s possible with little effort.

Some tinkering on the DbContext with IlSpy led me to the conclusion that an implementation of IMigrationsSqlGenerator in conjunction with an IMigrationCommandExecutorcan apply a single migration.

interface IMigrationsSqlGenerator
{
    IReadOnlyList<MigrationCommand> Generate(IReadOnlyList<MigrationOperation> operations, IModel? model = null, MigrationsSqlGenerationOptions options = MigrationsSqlGenerationOptions.Default);
}

interface IMigrationCommandExecutor
{
    void ExecuteNonQuery(IEnumerable<MigrationCommand> migrationCommands, IRelationalConnection connection);
    int ExecuteNonQuery(IReadOnlyList<MigrationCommand> migrationCommands, IRelationalConnection connection, MigrationExecutionState executionState, bool commitTransaction, IsolationLevel? isolationLevel = null);
    Task ExecuteNonQueryAsync(IEnumerable<MigrationCommand> migrationCommands, IRelationalConnection connection, CancellationToken cancellationToken = default(CancellationToken));
    Task<int> ExecuteNonQueryAsync(IReadOnlyList<MigrationCommand> migrationCommands, IRelationalConnection connection, MigrationExecutionState executionState, bool commitTransaction, IsolationLevel? isolationLevel = null, CancellationToken cancellationToken = default(CancellationToken));
}

You will notice that you cannot directly resolve these services through the DI container. This is because EF Core uses an internal DI container for its own services to avoid cluttering the global DI container. You can change this behavior or resolve these services from within your DbContext.

internal class ManualMigrationsDbContext : DbContext
{
    public TService GetRequiredInternalService<TService>()
        where TService : class
    {
        return this.GetInfrastructure().GetRequiredService<TService>();
    }
}

With this setup, you can run a single migration using this code:

private async Task ApplyManualMigrationAsync(
    ManualMigrationsDbContext dbContext,
    Migration migration,
    CancellationToken cancellationToken)
{
    var migrationsSqlGenerator = dbContext.GetRequiredInternalService<IMigrationsSqlGenerator>();
    var migrationCommandExecutor = dbContext.GetRequiredInternalService<IMigrationCommandExecutor>();
    var relationalConnection = dbContext.GetRequiredInternalService<IRelationalConnection>();

    var commands = migrationsSqlGenerator.Generate(migration.UpOperations, dbContext.Model, MigrationsSqlGenerationOptions.Default);

    // var script = migrationsSqlGenerator.Generate(migration.UpOperations, dbContext.Model, MigrationsSqlGenerationOptions.Script);
    // Console.WriteLine("Generated Script");
    // Console.WriteLine(script.Single().CommandText);

    await migrationCommandExecutor.ExecuteNonQueryAsync(commands, relationalConnection, cancellationToken);
}

Fun fact: The IMigrationsSqlGenerator can also generate SQL instead of directly executing code. Check the commented code for that.

Now you can add migrations for states other than the base state and apply them individually.

internal class TestDataMigration : Migration
{
    private static readonly object[,] _testData = new object[,]
    {
        { 2, "Jane Smith" },
        { 3, "Alice Johnson" },
        { 4, "Bob Brown" }
    };

    private readonly int _numberOfRecords;

    public TestDataMigration(int numberOfRecords)
    {
        if(numberOfRecords < 1 || numberOfRecords > _testData.Length)
        {
            throw new ArgumentOutOfRangeException(nameof(numberOfRecords), $"Number of records must be between 1 and {_testData.Length}");
        }

        _numberOfRecords = numberOfRecords;
    }

    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // Create a new array with the required number of records from the 2D array
        var values = new object[_numberOfRecords, _testData.GetLength(1)];
        for (int i = 0; i < _numberOfRecords; i++)
        {
            for (int j = 0; j < _testData.GetLength(1); j++)
            {
                values[i, j] = _testData[i, j];
            }
        }

        migrationBuilder.InsertData(
            table: "Customers",
            columns: new[] { "Id", "Name" },
            values: values);
    }
}

// Setup test data
var manualMigration = new TestDataMigration(3);
await ApplyManualMigrationAsync(dbContext, manualMigration, cancellationToken);

Make sure not to mark your manual migrations with [DbContext] or [Migration] because you don't want EF Core to include them when applying regular migrations.

These manual migrations can be useful if you're working with non-.NET developers, such as testers or database developers. This way, different people can create test data for integration tests and generate these manual migrations using code generation.

What about multiple DbContexts

Since each DbContext has its own database connection, you cannot have a transaction that spans across them. However, you don't need to do this—they can each have their own transaction, and you can use them in sync.

Conclusion

I hope you found this article interesting and that it gave you some ideas on how to structure integration tests for your .NET Core projects. It also shows how you can use manual migrations if they fit your solutions.

You can find the demo solution I created here.

Integrative Persistence Tests for .NET Core Applications

The project

Unit Tests for Application Services Are Challenging Due to DbContext

Using the In-Memory Provider for Integration Tests

Using SQLite for Integration Tests

What I Want to Show Here

Setting Up a Base State for Your Database

Getting to the Base State

Returning to the Base State

What if my code uses transactions on its own or I want to support nested states

Changing the Lifecycle for DbContext When Running Integration Tests

Abstracting Transaction Handling

Adding Extra Migrations Beyond the Base State

Running migrations imperatively

What about multiple DbContexts

Conclusion

Comments

More from this blog

Make Python Trust Your Corp Proxy Using Sessions

Writeup: The Game v2

The annoying online course

Drill, Baby, Drill

How to Implement Global Exception Handling in ASP.NET Core

Command Palette

The project

Unit Tests for Application Services Are Challenging Due to DbContext

Using the In-Memory Provider for Integration Tests

Using SQLite for Integration Tests

What I Want to Show Here

Setting Up a Base State for Your Database

Getting to the Base State

Returning to the Base State

What if my code uses transactions on its own or I want to support nested states

Changing the Lifecycle for DbContext When Running Integration Tests

Abstracting Transaction Handling

Adding Extra Migrations Beyond the Base State

Running migrations imperatively

What about multiple DbContexts

Conclusion

Comments

More from this blog