Skip to content

Entity Framework Core In Memory Testing database

Reading Mode

Entity Framework core has really simplified working with databases and has drastically improved the testability aspects of these databases.

Providing developers the ability to create their databases as separate class library projects

As any developer will tell you releasing a product or library without testing it is generally accepted as an unforgivable sin in the software development world. Therefore if you're making use of the Code-First database development process, enabled via Entity Framework then you should also ensure you at least make an attempt to write Unit & Integration tests for your databases.

What are Unit tests

Unit tests are used to check if small units of code are functioning as you would expect them to. Typically this testing is done as part of the development process, and a unit test will check that the code being tested meets a specification, while a library of unit tests together will check the functions expected of the application. The process is usually automated, which allows the library of tests to be re-run frequently, allowing us to find bugs earlier, and in smaller units of code, which are easier to debug.

A unit test is an automated piece of code that invokes the method or class being tested and then checks some assumptions about the logical behaviour of that method or class.

Roy Osherove - Art of unit testing

Unit Tests enable developers to think and reason about their code in a modular fashion. They are frequently combined with a continuous integration system, to regression test changes, and help speed up and minimize the risk from refactoring.

Unit testing can also form the basis of documentation as the functional requirements should tie to unit tests, and vice versa, allowing you to see which requirements are met by the developed code.

My two favourite books on Unit Testing : The Art of Unit Testing : With Examples in C# & Clean Code: A Handbook of Agile Software Craftsmanship explain how Unit testing, done right, can be the difference between a failed project and a successful one, between a maintainable code base or a big ball of mud.

Using In Memory Databases in Unit tests

When developing Unit or Integration Tests you don't want to use a physical database on a server, because you don't necessarily want to concern yourself with ceremony or administration tasks of maintaining the database server. Fortunately, Entity Framework core provides a couple of different in-memory database options to assist in developing tests.

These two choices for in-memory database providers depend on whether or not you're using the Microsoft.EntityFrameworkCore.Relational package and if you need the full behaviour of a relational database during testing i.e. Foreign Key Constraints etc.

If you don't really want to or need to worry about Relational database characteristics then you can make use of the Microsoft.EntityFrameworkCore.InMemory or if you would prefer a simulation closer to the real thing then Microsoft.EntityFrameworkCore.Sqlite should be your library of choice.

In order to provide examples of using both these options I will use the unit tests of my Generic Repository Nuget Package .

All code in the examples make use of my current favourite Unit Testing Framework xUnit, but should be fairly easy to convert to any of the others.

Testing Entity Framework Core using In-Memory Database Provider

To use the In-Memory database provider first we need to add the following nuget package :

dotnet add package Microsoft.EntityFrameworkCore.InMemory

In order to create an instance of a DbContext to use for our tests, we create an instance of DbContextOptions. To create an instance of DbContextOptions use the DbContextOptionsBuilder class.

A key point to take note of is that we generate a GUID to use as a name for the database. This is to ensure that every TestContext run has new database that is not affected by other test runs.

public TestDbContext Context => InMemoryContext();
private TestDbContext InMemoryContext()
{
   var options = new DbContextOptionsBuilder<TestDbContext>()
         .UseInMemoryDatabase(Guid.NewGuid().ToString())
         .EnableSensitiveDataLogging()
         .Options;
   var context = new TestDbContext(options);
          
   return context;
}

View Full Code

Although not completely necessary I make use of Class Fixtures to share contexts between tests.

public class RepositoryAddTest : IClassFixture<InMemoryTestFixture>
{
   private readonly InMemoryTestFixture _fixture;
   public RepositoryAddTest(InMemoryTestFixture fixture)
   {
       _fixture = fixture;
   }
  [Fact]
  public void ShouldAddNewProduct()
  {
     // Arrange 
     var uow = new UnitOfWork<TestDbContext>(_fixture.Context);
     var repo = uow.GetRepository<TestProduct>();
     var newProduct = new TestProduct() { Name = GlobalTestStrings.TestProductName };
 
    // Act
    repo.Add(newProduct);
    uow.SaveChanges();
 
    //Assert
    Assert.Equal(1, newProduct.Id);
  }
}

Clean Code

A Handbook of Agile Software Craftsmanship

software developers irrespective of programming language or discipline should read this book

Testing Entity Framework Core Relational Databases using SQLite In-Memory Mode

In order to use the SqLite provider you will first need to add a reference your Unit Test Project.

dotnet add package Microsoft.EntityFrameworkCore.Sqlite

The SQLite provider itself is a relational database provider, However we can take advantage of SQLite in-memory mode. The key benefit is that we get the full behavior of a relational database, with the benefits of the running in-memory.

By default each SQLite connection using the ":memory:" connection string will use a different in-memory database, so we don't need to generate a new name for each connection.

public TestDbContext Context => SqlLiteInMemoryContext();
private  TestDbContext SqlLiteInMemoryContext()
{

  var options = new DbContextOptionsBuilder<TestDbContext>()
         .UseSqlite("DataSource=:memory:")
         .Options;

  var context = new TestDbContext(options);
  context.Database.OpenConnection();
  context.Database.EnsureCreated();
  return context;
}

An Additional step required for the SQLite provider is to call both the OpenConnection and EnsureCreated methods on the context’s Facade, else we'll get DbUpdateException: "SQLite Error 1: 'no such table: " exception .

public class RepositoryAddTestsSqlLite : IClassFixture&lt;SqlLiteTestFixture&gt;
{
  private readonly SqlLiteTestFixture _fixture;
  public RepositoryAddTestsSqlLite(SqlLiteTestFixture fixture)
  {
     _fixture = fixture;
  }

 [Fact]
 public void ShouldAddNewProduct()
 {

    //Arrange
    var uow = new UnitOfWork&lt;TestDbContext&gt;(_fixture.Context);
    var repo = uow.GetRepository<TestProduct>();
    var newProduct = new TestProduct() { Name = GlobalTestStrings.TestProductName, Category = new TestCategory() { Id = 1, Name = GlobalTestStrings.TestProductCategoryName } };

    //Act 

     repo.Add(newProduct);
     uow.SaveChanges();

    //Assert
    Assert.Equal(1, newProduct.Id);

 }
}

Limitations of In-Memory Databases

In-memory databases are great for unit testing and can really assist in providing the ability to re-create Unit Testing databases. However, there are a few limitations to using In-Memory databases like SqLite that you should be aware of. Particulary if the DbContext your project is using is configured to use some advanced Database features, this is due to the fact that most In-Memory databases do not support advanced Relational Database Management System (RDBMS) features.

RDBMS FeatureSupport
SchemaNone
SequencesNone
Computed ColumnsDifferent
User Defined Functions (UDF)Different
Fragment Default ValuesDifferent
DatatypesDifferent

You should also check out SQLite EF Core Database Provider Limitations, as most of the limitations are a result of limitations in the underlying SQLite database engine and are not specific to EF Core.

You should exercise caution when developing tests making use of In-Memory databases especially if the conditions you're testing explicitly make use of advanced database features. It is usually the case that you should reconsider your Unit testing strategy and confirm whether you are actually attemtpting to write Integration tests. Check out Roy Osheroves - The Art of Unit Testing : With Examples in C#

Summary

Entity Framework core has made it really easy to set up and configure in-memory database options, reducing the amount of ceremony and configuration one has to do to get it up and running.

Being able to quickly and easily replicate and simulate database interactivity in your Unit and Integration tests helps to ensure your code operates and functions as expected in all conditions.

Gary Woodfine
Latest posts by Gary Woodfine (see all)