Testing EF Core in Memory using SQLite

Testing your software is essential. EF Core provides an InMemory provider to test your code without a real database. However, this provider behaves differently from a relational database. Here is 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.

This post shows how to use SQLite as the database instead. SQLite supports an in-memory mode that stores data in RAM rather than on disk, which is great for unit testing. Keep in mind that SQLite is not identical to SQL Server; for example, it does not support collation and is case-sensitive by default. Even so, it behaves much more like a relational database than the InMemory provider, making it a solid choice for testing purposes.

Let's write 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, add the NuGet package Microsoft.EntityFrameworkCore.Sqlite (already included in the 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 and destroyed when the connection is closed. This means you must keep the connection open for the duration of the test. You cannot simply replace UseInMemoryDatabase with UseSqlite as in the previous example; when the context is disposed, the connection closes and the database is destroyed. The following test fails because 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 manage the connection manually, closing it only at the end of the test. This keeps the database alive for the entire test execution.

[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);
        }
    }
}

Writing this setup manually every time is cumbersome. Let's create a wrapper that manages the connection lifetime, DbContext creation, and schema initialization.

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 requires a bit more setup than the built-in InMemory provider. However, since SQLite is a relational database, it behaves much more like SQL Server in practice. This means your tests are closer to real-world conditions, helping you catch bugs earlier in development.

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