Unit testing is a type of software testing that is performed on the smallest unit of a software application, called a "unit". A unit can be a function, method or module that is tested in isolation from other parts of the code.

The purpose of unit testing is to ensure that a single unit of software functions correctly and produces the expected results. To do this, test cases are developed that cover various inputs and situations and check the expected outputs. Unit tests are usually created and executed automatically to make the development process more efficient and reliable.

However, there are different opinions whether database functionalities should be solved via unit tests: because database providers have certain peculiarities that can only be tested against a real database.
In principle, I agree - but there are simply certain scenarios - like Linq queries - that can be covered super easily and stably via unit tests. Of course, however, these do not replace integration tests against real databases, which, however, have to be implemented with higher effort, costs and not always corresponding benefits.

Sqlite InMemory

To implement certain scenarios with the help of unit tests in .NET, I like to use Sqlite with its InMemory features, which is very easy and stable to use. This in itself is also the recommended variant, although nothing is said about the InMemory functionality of Sqlite itself. It is related to the fact that the InMemory database driver of EFCore is no longer developed, has immense vulnerabilities and incompatibilities and should simply not be used.

The .NET implementation of Sqlite (NuGet Package: Microsoft.EntityFrameworkCore.Sqlite) also supports the InMemory behavior; it just has a certain peculiarity: InMemory is not supported by a name as known from other databases, but by a connection instance. If the connection is closed, the InMemory database is discarded.

// create test im-memory connection
SqliteConnection sqliteConnection = new("DataSource=:memory:");

// ..
DbContextOptions<T> options = new DbContextOptionsBuilder<T>()
                   .UseSqlite(sqliteConnection) // re-use connection
                   .EnableSensitiveDataLogging(true)
                   .Options;

Therefore, the test case must share the connection across multiple DbContext instances, which I implemented accordingly via a test helper class.

Test Helpers

To simplify the tests and their implementation I have written a small helper:

/// <summary>
/// Static class to handle methods related to Sqlite database testing
/// </summary>
public static class TestSqliteDatabase
{
    /// <summary>
    /// Method to create a Sqlite connection for testing purposes
    /// </summary>
    /// <returns>SqliteConnection object for testing</returns>
    public static SqliteConnection CreateSqliteTestConnection()
    {
        SqliteConnection connection = new("DataSource=:memory:");
        connection.Open();
        return connection;
    }

    /// <summary>
    /// Method to create options for the provided SqlServerBaseDbContext
    /// </summary>
    /// <typeparam name="T">Type of SqlServerBaseDbContext</typeparam>
    /// <param name="sqliteConnection">SqliteConnection to provide options for</param>
    /// <returns>DbContextOptions object with options for the provided SqlServerBaseDbContext</returns>
    public static DbContextOptions<T> CreateOptions<T>(SqliteConnection sqliteConnection) where T : SqlServerBaseDbContext
        => new DbContextOptionsBuilder<T>()
                   .UseSqlite(sqliteConnection)
                   .EnableSensitiveDataLogging(true)
                   .Options;

    /// <summary>
    /// Method to create a DbContext for the provided SqlServerBaseDbContext and connection
    /// </summary>
    /// <typeparam name="T">Type of SqlServerBaseDbContext</typeparam>
    /// <param name="sqliteConnection">SqliteConnection to provide context for</param>
    /// <returns>New instance of the provided SqlServerBaseDbContext connected to the provided SqliteConnection</returns>
    public static T CreateContext<T>(SqliteConnection sqliteConnection) where T : SqlServerBaseDbContext
    {
        DbContextOptions<T> dbContextOptions = CreateOptions<T>(sqliteConnection);

        T context = (T)Activator.CreateInstance(typeof(T), dbContextOptions)!;
        context.Database.EnsureCreated();

        return context;
    }
}

Test Case

A test case always has the same structure with regard to the test of the database functionalities:

• Establishment of the connection, which is shared
• First context instance for seeding the database
• Second context to test the actual functionality

[Fact]
public async Task My_Nice_Repository_Test_Case()
{
    // test database context
    using SqliteConnection testDbConnection = TestSqliteDatabase.CreateSqliteTestConnection();

    // arrange
    // ...

    // create test database instance for seed
    using (TenantsDatabaseSqlServerContext dbContext = TestSqliteDatabase.CreateContext<MyDatabaseMssqlServerContext>(testDbConnection))
    {
        // use this to seed your database

        await dbContext.SaveChangesAsync(ct);
    }

    // create test database instance for query
    using (TenantsDatabaseSqlServerContext dbContext = TestSqliteDatabase.CreateContext<MyDatabaseMssqlServerContext>(testDbConnection))
    {
        // act
        // ...
    }
}

It is not necessarily the case that there is no third or fourth context; it is just essential to ensure that the Act part is given a new, fresh context and does not use the seed context, so that the test cannot be falsified.

Conclusion

Unit tests offer several benefits. They help developers detect and fix bugs early by isolating potential problems and preventing bugs from spreading to other parts of the application. Writing tests also more clearly defines requirements and unit behaviors, leading to better code quality and maintainability. Unit tests also facilitate refactoring by providing a safety net to ensure that changes do not have unwanted side effects.

Overall, unit tests are an important practice in software development to improve the quality and stability of an application. They help reduce bugs, increase developer productivity, and improve customer satisfaction.

My variant to implement EF Core unit tests is very simple, works very stable and adds value to all my projects.