Using Entity Framework Core in-memory database for unit testing

ASP.NET Core applications can be tested with different testing frameworks and Entity Framework Core makes testing specially easy by removing different technical problems from our way by using in-memory data provider. This blog posts shows how to unit test controllers that use data from Entity Framework Core.

NB! The code and tests given here are really minimal and illustrative. In real situations there are probably at least some service classes that carry use cases initiated from controllers. Still the code similar to what we have here can be used in smaller utility applications. For basic about ASP.NET Core and xUnit please see my blog post Using xUnit with ASP.NET Core.

In-memory database

Entity Framework Core makes testing of data related controllers easier. We don’t have to necessarily mock or fake database context as context is not affected by selection of database. We build our enitites and database context and what database we use we decide in application startup class. Of course there can be differences in different database providers and their capabilities but in the light of unit testing it’s the matter of integration tests.

To avoid mocking and faking Entity Framework Core provides us with in-memory data provider. It has no database back-end and its only purpose is to support scenarios where persistent data storage is not needed. One of the scenarios is unit testing.

Simple product catalog

This blog post builds on simple product catalog that uses SQL Server as database. We start with defining product and category entities.


public class Category
{
    [
Key
]
   
public int Id { get; set
; }

    [
Required
]
   
public string Name { get; set
; }
}

public class Product
{
    [
Key
]
   
public int Id { get; set
; }

    [
Required
]
   
public string Name { get; set
; }

    [
Required
]
   
public Category Category { get; set; }
}

To use these classes with databases and EntityFramework Core we need also database context class.


public class MyDbContext : DbContext
{
   
public MyDbContext(DbContextOptions<MyDbContext> options) : base
(options)
    {
    }

   
public DbSet<Category> Categories { get; set
; }
   
public DbSet<Product> Products { get; set; }
}

To make application use our database context with SQL Server we have to add the following block of code to ConfigureServices() method of Startup class.


services.AddDbContext<MyDbContext>(options =>
{
    options.UseSqlServer(Configuration[
"ConnectionStrings:Default"]);
});

This code expects that we have connection string called Default defined in application settings file.

Now let’s write simple controller to display data from product catalog.


public class HomeController : Controller
{
   
private readonly MyDbContext
_context;

   
public HomeController(MyDbContext
context)
    {
        _context = context;
    }

   
public IActionResult
Index()
    {
       
var model = new FrontPageModel
();
        model.Top5 = _context.Products.Take(5);
        model.Items = _context.Products.Take(10);

       
return
View(model);
    }

   
public IActionResult Category(int
id)
    {
       
var
category = _context.Categories.FirstOrDefault(c => c.Id == id);
       
if(category == null
)
        {
           
return
NotFound();
        }

       
var
products = _context.Products.Where(p => p.Category.Id == id);
       
return
View(products);
    }

   
protected override void Dispose(bool
disposing)
    {
       
if
(!disposing)
        {
            _context.Dispose();
        }

       
base.Dispose(disposing);
    }
}

To show data on front-page we need view model for top 5 products and list of products.


public class FrontPageModel
{
   
public IEnumerable<Product
> Top5;
   
public IEnumerable<Product> Items;
}

When instance of controller is created the database context is injected to its constructor by framework. Index method asks five products for products top five and first ten products for products list. Category method takes category id as argument and displays ten products from given category. If category is not found then error 404 is returned.

Preparing for testing

For testing we need project for tests and some testing framework. I went with xUnit as it works also with .NET Core and we can also use it on Linux and Apple platforms. For unit tests we add new xUnit Test Project to our solution.

Visual Studio: Create new xUnit test project

For tests there is empty class with one dummy method that shows how test method should be written so it is understandable for test framework. In xUnit world methods decorated with Fact attribute are tests.


public class UnitTest1
{
    [
Fact
]
   
public void Test1()
    {
    }
}

As our controller needs database context we have to provide it with one. But there’s one trick – we don’t want SQL Server to be involved as we are not writing integration tests. In this point EntityFramework Core makes us huge favor. Without changing anything in database context we can make Entity Framework Core use in-memory data store that is design for testing and other scenarios where we don’t need persistent storage for data.

Looking at Index() and Category methods we also see that we need some test data. As we need database context with test data probably in more than one test we will add GetContextWithData() method to our test class.


private MyDbContext GetContextWithData()
{
   
var options = new DbContextOptionsBuilder<MyDbContext
>()
                      .UseInMemoryDatabase(
Guid
.NewGuid().ToString())
                      .Options;
   
var context = new MyDbContext
(options);

   
var beerCategory = new Category { Id = 1, Name = "Beers"
};
   
var wineCategory = new Category { Id = 2, Name = "Wines"
};
    context.Categories.Add(beerCategory);
    context.Categories.Add(wineCategory);

    context.Products.Add(
new Product { Id = 1, Name = "La Trappe Isid'or"
, Category = beerCategory });
    context.Products.Add(
new Product { Id = 2, Name = "St. Bernardus Abt 12"
, Category = beerCategory });
    context.Products.Add(
new Product { Id = 3, Name = "Zundert"
, Category = beerCategory });
    context.Products.Add(
new Product { Id = 4, Name = "La Trappe Blond"
, Category = beerCategory });
    context.Products.Add(
new Product { Id = 5, Name = "La Trappe Bock"
, Category = beerCategory });
    context.Products.Add(
new Product { Id = 6, Name = "St. Bernardus Tripel"
, Category = beerCategory });
    context.Products.Add(
new Product { Id = 7, Name = "Grottenbier Bruin"
, Category = beerCategory });
    context.Products.Add(
new Product { Id = 8, Name = "St. Bernardus Pater 6"
, Category = beerCategory });
    context.Products.Add(
new Product { Id = 9, Name = "La Trappe Quadrupel"
, Category = beerCategory });
    context.Products.Add(
new Product { Id = 10, Name = "Westvleteren 12"
, Category = beerCategory });
    context.Products.Add(
new Product { Id = 11, Name = "Leffe Bruin"
, Category = beerCategory });
    context.Products.Add(
new Product { Id = 12, Name = "Leffe Royale"
, Category = beerCategory });
    context.SaveChanges();

   
return context;
}

Notice how we name in-memory database by GUID. This is to make sure that every test run has new database that is not affected by previous runs anyhow.

Writing controller tests

Now let’s write our first test. Our first test makes sure that Index() method returns correct view. View is correct when its name is not given or if its name is Index.


[Fact(DisplayName = "Index should return default view")]
public void
Index_should_return_default_view()
{
   
using (var
context = GetContextWithData())
   
using (var controller = new HomeController
(context))
    {
       
var result = controller.Index() as ViewResult
;

       
Assert
.NotNull(result);
       
Assert.True(string.IsNullOrEmpty(result.ViewName) || result.ViewName == "Index");
    }
}

Now let’s write test that checks model given to Index view. We don’t focus on actual in model attributes. We just want to make sure that there is data like expected.


[Fact(DisplayName = "Index should return valid model")]
public void
Index_should_return_valid_model()
{
   
using (var
context = GetContextWithData())
   
using (var controller = new HomeController
(context))
    {
       
var result = controller.Index() as ViewResult
;
       
var model = result.Model as FrontPageModel
;

       
Assert
.NotNull(model);
       
Assert
.NotNull(model.Top5);
       
Assert
.NotNull(model.Items);
       
Assert
.Equal(5, model.Top5.Count());
       
Assert.Equal(10, model.Items.Count());
    }
}

This test makes sure that model is given to view and it is not null. It also checks that top five and items collections are not null and these collections contain data.

If category is not found then Category action returns status code 404. In our case it is represented as NotFoundResult that action returns. Here is the test.


[Fact(DisplayName = "Category should return 404 for missing category")]
public void
Category_should_return_404_for_missing_category()
{
   
using (var
context = GetContextWithData())
   
using (var controller = new HomeController
(context))
    {
       
var result = controller.Category(-1) as NotFoundResult
;

       
Assert.NotNull(result);
    }
}

Running tests

Succeeded tests in Visual Studio test runnerNow let’s run the tests. For this we can right-click on test project and select Debug.

Visual Studio opens test runner and runs tests one by one. Image on right shows the Test Explorer with our tests. As we used DisplayName parameter with fact attributes our tests in Test Explorer have well readable names. Also we can see how much time it took for tests to run.

Normally unit tests should run fast. It shouldn’t take more than few milliseconds. It holds true for index tests but for some reason category test takes almost second. Things get mopre interesting when we look at summary: it took almost five seconds to run these tests. What’s the trick? Well, building and loading tests takes also time and these times are also included in summary.

Wrapping up

Writing unit tests for controllers is easy when EnitiyFramework Core is in use. We can avoid mocks and fakes of database context as database context doesn’t depend on database we are using. In-memory database is non-persistent implementation of data provider that we can use when testing Entity Framework related code. As it doesnät make any requests to network or other local processes it is fast as we saw from test running times.



See also

8 thoughts on “Using Entity Framework Core in-memory database for unit testing

  • There is a lot of problem with this tool, for instance your unit tests won’t check if your expressions can be translated to SQL by EF. For instance if you add a “Where(e => e.IsSomething())” it will work with in memory DB but won’t work once in production. IMHO it’s way better to use SQL lite or SQL express.

  • Gunnar says:

    Unit tests test only the functionality of given code unit and in this case there is no matter what database you are actually using. If you start using SQL Express then you can be sure that SQL Server data provider translates expression tree to valid SQL. It tells nothing about what are doing all MySQL providers. Worse yet, suddenly you are testing not only functionality but also integration with database.

    When real services like databases, web services etc come to play we write separate set of tests called integration tests.

  • Using SQLite in memory is the BEST option to go. Fast and you don’t even have to think about the annoying integration vs pure unit test dispute.

  • Gunnar says:

    But still you are testing also how one database system works 🙂

  • Nathan Risto says:

    The comments about using some sort of actual database for unit tests clearly shows the lack of understanding of what a unit test is, its scope, and what it should test. I have to struggle with this often among other co-workers.

  • David says:

    Very good article! Thanks for explaining it.

    I am getting an exception. “Message: System.InvalidOperationException : Unable to resolve service for type ‘Microsoft.EntityFrameworkCore.Storage.IRelationalConnection’. This is often because no database provider has been configured for this DbContext.

    My ConfigureServices has the following entry:

    services.AddDbContext(options => options.UseSqlServer(conn));

    Do I need to add a new entry in ConfigureServices method like,
    services.AddDbContext(opt => opt.UseInMemoryDatabase());

  • David says:

    It works fine. Nothing wrong with the above implementation. I found out the problem in my program and resolved it.

    The context’s constructor was invoking ‘Database.OpenConnection()’. And I realized that it was not needed. When I removed that line of code, the tests working fine now.

Leave a Reply

Your email address will not be published. Required fields are marked *