.NET Core complex EF Core unit testing

When writing unit tests for EF Core we can use InMemoryDatabase so that we don’t have to create mock repositories. But sometimes we need to test functionalities that are not supported in in-memory-database.

In-memory-database does not support:

• Executing SQL commands
• Enforcing foreign keys
• Returning null objects for properties that needed to be included in the query .Include()
• Failing when using features not supported in Linq-to-sql queries

Of course, we can mock the data layer but sometimes we need to test if the database’s queries are playing well the application logic.

The solution is to use SQLite in-memory DB which allows us to use some of the SQL and DB relational features.

An example of creating DB context that switches between in-memory-database and SQLite database:

private bool useSqlite;

public void UseSqlite()
{
    useSqlite = true;
}

public SampleDbContext GetDbContext()
{
    DbContextOptionsBuilder builder = new DbContextOptionsBuilder();
    if (!useSqlite)
    {
        builder.UseInMemoryDatabase(Guid.NewGuid().ToString()).ConfigureWarnings(w =>
            {
                w.Ignore(InMemoryEventId.TransactionIgnoredWarning);
            }).EnableSensitiveDataLogging(true);
    }
    else
    {
        builder.UseSqlite("DataSource=:memory:", x => { })
            .EnableSensitiveDataLogging(true);
    }

    var dbContext = new SampleDbContext(builder.Options);
    if (useSqlite)
    {
        // SQLite needs to open connection to the DB.
        // Not required for in-memory-database and MS SQL.
        dbContext.Database.OpenConnection();
    }

    dbContext.Database.EnsureCreated();

    return dbContext;
}

Example of a unit test:

public async Task ShouldThrowAnException()
{
    var context = GetDbContext();
    context.TestDatas.Add(new TestData
    {
        Text = "Parent",
        IsDeleted = false,
        Child = null
    });

    // Will not fail even though Child is required.
    context.SaveChanges();

    // Execute and assert
    // This fails because in-memory-database does not support SQL.
    await Assert.ThrowsAsync<System.InvalidOperationException>(
        () => context.Database.GetDbConnection()
            .QueryAsync<TestData>(@"select * from TestDatas"));
}

[Fact]
public async Task ShouldWork()
{
    // Prepare
    UseSqlite();

    var context = GetDbContext();
    context.TestDatas.Add(new TestData
    {
        Text = "Parent",
        IsDeleted = false,
        Child = new ChildData
        {
            Text = "Child"
        }
    });

    // Checks if Child property is correctly populated
    context.SaveChanges();

    // Execute
    var data = await context.Database.GetDbConnection()
        .QueryAsync<TestData>(@"select * from TestDatas");

    // Assert
    Assert.Single(data);
}

However, there are a few caveats regarding using SQLite in unit tests:

• In-memory SQLite is around 5 times slower than EF Core in-memory-database
• The order of results will not be consistent unless it’s ordered by a column
• For complex database structures foreign key restrictions will not work and needs to be disabled in order to allow testing
• If dealing with raw SQL commands, there are some differences between SQLite and MS SQL. It means that the code might need to generate slightly different SQL for SQLite than for MS SQL
• Types like System.Guid are not directly supported and require converters (might get fixed in EF Core 2.1)

The best approach so far is to choose in-memory-database and SQLite in-memory database based on the unit test scenario. Personally, I always prefer in-memory-database because of the performance benefits and I switch to SQLite when absolutely necessary.