Testing EF Core in Memory using SQLite

  • .NET

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 act differently from a relation 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 have no effect when running against InMemory.

The idea of this post is to use SQLite as the database. SQLite also have 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 purpose.

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 opened 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 ourself. 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 the reality, and you'll be able to track bugs sooner in the development.

Follow me:
Enjoy this blog?Buy Me A CoffeeDonate with PayPal

Comments

Blick Dicht -

Very Very nice blog. Thanks for this and for the perfect explanations.

Ryan Atwood -

Hi Gerald, thanks for the nice blog. Could you comment on why you chose to call using (var context = factory.CreateContext()) twice in your final code example instead of just having it all in 1 using block?

Thanks, Ryan

Gérald Barré -

Hi Ryan,

I've created 2 contexts to simulate a first user that inserts data into the database, and another user that read data from the db. It may be needed in some cases as some behaviors may be different if you use the same context. For instance, context.Find may not go to the database if the value is already in the context. If it's not important for your case, you may go with only one context. You can create as many contexts as you want until the factory is disposed.

Regards

Leave a reply