Testing EF Core in Memory using SQLite

A good practice is to test your software. EF Core provides an In-Memory provider to easily test your code without an actual database. However, this provider acts differently from a relational database. Here's an extract from the documentation

EF Core database providers do not have to be relational databases. InMemory is designed to be a general-purpose database for testing, and is not designed to mimic a relational database.

• InMemory will allow you to save data that would violate referential integrity constraints in a relational database.
• If you use DefaultValueSql(string) for a property in your model, this is a relational database API and will not affect when running against InMemory.

The idea of this post is to use SQLite as the database. SQLite also has an option to run fully in-memory (e.g. without writing data on disk). This way, you use a relational database but in memory, which is great for unit testing. To be clear, SQLite doesn't act as SQL Server. For instance, SQLite doesn't support collation and is case sensitive by default. Anyway, it should behave more like SQL Server than the InMemory provider. So, it's an interesting database for testing purposes.

Let's do some code!

#The model

Let's use a very simple model and context:

public class SampleDbContext : DbContext
{
    public DbSet<User> Users { get; set; }

    public SampleDbContext()
    {
    }

    public SampleDbContext(DbContextOptions<SampleDbContext> options) : base(options)
    {
    }
}

public class User
{
    public int Id { get; set; }
    public string Email { get; set; }
}

#Testing using In-Memory provider

First, let's see how to use the in-memory provider to test the database.

1. Add the package: Microsoft.EntityFrameworkCore.InMemory
2. Configure the DbContext to use the InMemory provider using UseInMemoryDatabase

[TestMethod]
public async Task TestMethod_UsingInMemoryProvider()
{
    // The database name allows the scope of the in-memory database
    // to be controlled independently of the context. The in-memory database is shared
    // anywhere the same name is used.
    var options = new DbContextOptionsBuilder<SampleDbContext>()
        .UseInMemoryDatabase(databaseName: "Test1")
        .Options;

    using (var context = new SampleDbContext(options))
    {
        var user = new User() { Email = "test@sample.com" };
        context.Users.Add(user);
        await context.SaveChangesAsync();
    }

    // New context with the data as the database name is the same
    using (var context = new SampleDbContext(options))
    {
        var count = await context.Users.CountAsync();
        Assert.AreEqual(1, count);

        var u = await context.Users.FirstOrDefaultAsync(user => user.Email == "test@sample.com");
        Assert.IsNotNull(u);
    }
}

#Testing using SQLite In-Memory provider

To use the SQLite provider, you must add the NuGet package Microsoft.EntityFrameworkCore.Sqlite (already included in Microsoft.AspNetCore.All metapackage)

SQLite can run in-memory using the connection string DataSource=:memory. When the connection is opened, a new database is created in memory. This database is destroyed when the connection is closed. This means, you must keep the connection open until the test ends. This means you cannot just change UseInMemoryDatabase to UseSqlite in the previous test. Indeed, when the context is disposed, the connection is closed and the database is destroyed. The following test fails as the database is not preserved between contexts:

[TestMethod]
public async Task TestMethod_UsingSqliteInMemoryProvider_Fail()
{
    var options = new DbContextOptionsBuilder<SampleDbContext>()
        .UseSqlite("DataSource=:memory:")
        .Options;

    // Create the dabase schema
    using (var context = new SampleDbContext(options))
    {
        await context.Database.EnsureCreatedAsync();
    } // The connection is closed, so the database is destroyed

    using (var context = new SampleDbContext(options))
    {
        // Error: the table Users does not exist
        await context.Users.CountAsync();
    }
}

The solution is to handle the connection by ourselves. This way, we can close it only at the end of the test. Thus, the database won't be destroyed during the execution of the test.

[TestMethod]
public async Task TestMethod_UsingSqliteInMemoryProvider_Success()
{
    using (var connection = new SqliteConnection("DataSource=:memory:"))
    {
        connection.Open();

        var options = new DbContextOptionsBuilder<SampleDbContext>()
            .UseSqlite(connection) // Set the connection explicitly, so it won't be closed automatically by EF
            .Options;

        // Create the dabase schema
        // You can use MigrateAsync if you use Migrations
        using (var context = new SampleDbContext(options))
        {
            await context.Database.EnsureCreatedAsync();
        } // The connection is not closed, so the database still exists

        using (var context = new SampleDbContext(options))
        {
            var user = new User() { Email = "test@sample.com" };
            context.Users.Add(user);
            await context.SaveChangesAsync();
        }

        using (var context = new SampleDbContext(options))
        {
            var count = await context.Users.CountAsync();
            Assert.AreEqual(1, count);

            var u = await context.Users.FirstOrDefaultAsync(user => user.Email == "test@sample.com");
            Assert.IsNotNull(u);
        }
    }
}

Using all this code is not convenient. Let's create a wrapper that handle the connection lifetime, the creation of the DbContext and the creation of the schema.

public class SampleDbContextFactory : IDisposable
{
    private DbConnection _connection;

    private DbContextOptions<SampleDbContext> CreateOptions()
    {
        return new DbContextOptionsBuilder<SampleDbContext>()
            .UseSqlite(_connection).Options;
    }

    public SampleDbContext CreateContext()
    {
        if (_connection == null)
        {
            _connection = new SqliteConnection("DataSource=:memory:");
            _connection.Open();

            var options = CreateOptions();
            using (var context = new SampleDbContext(options))
            {
                context.Database.EnsureCreated();
            }
        }

        return new SampleDbContext(CreateOptions());
    }

    public void Dispose()
    {
        if (_connection != null)
        {
            _connection.Dispose();
            _connection = null;
        }
    }
}

Here's how to use the factory:

[TestMethod]
public async Task TestMethod_WithFactory()
{
    using (var factory = new SampleDbContextFactory())
    {
        // Get a context
        using (var context = factory.CreateContext())
        {
            var user = new User() { Email = "test@sample.com" };
            context.Users.Add(user);
            await context.SaveChangesAsync();
        }

        // Get another context using the same connection
        using (var context = factory.CreateContext())
        {
            var count = await context.Users.CountAsync();
            Assert.AreEqual(1, count);

            var u = await context.Users.FirstOrDefaultAsync(user => user.Email == "test@sample.com");
            Assert.IsNotNull(u);
        }
    }
}

#Conclusion

The SQLite provider is not as easy to use as the In-memory provider. However, SQLite is a relational database, so it should behave like another relational database such as SQL Server. This means, your tests are closer to reality, and you'll be able to track bugs sooner in the development.

Do you have a question or a suggestion about this post? Contact me!