While writing the book Entity Framework Core in Action I wrote over 600 unit tests, which taught me a lot about unit testing EF Core applications. So, when I wrote the chapter on unit testing, which was the last in the book, I combined what I had learn into a library called EfCore.TestSupport.
Note: EfCore.TestSupport is an open-source library (MIT licence) with a NuGet package available. Documentation is available in the project’s Wiki.
In this article I want to look at using in-memory database to simulate the database when you are unit testing. I also look at how to properly test your ASP.NET Core database update code, which has to handle what is called the disconnected state (explained later), with an in-memory database.
Your options for in-memory databases
There are several ways to simulate the database when unit testing, but one of the simplest ways is to create an in-memory database. They are easy to create and are quicker to create than a real database, but they do have a few limitations, which I will describe at the end. First I want to show you how they work.
The most obvious in-memory database is EF Core’s InMemory database provider, which was written specifically for unit testing. But it turns out the better option is using the SQLite database provider, with an in-memory database. Why is that?
Its because SQLite is a true relational database while EF Core’s InMemory database provider isn’t a real relational database. This means that EF Core’s InMemory database won’t pick up on data that breaks referential integrity, such as foreign keys that don’t match the appropriate primary key. About the only good thing about EF Core’s InMemory is that its slightly faster on setup than SQLite (about 40%), but both are very fast anyway, so the extra checking makes SQLite my choice.
Note My EfCore.TestSupport library has support for both InMemory and SQLite in-memory, so you can choose. But in this article I am only going to describe the SQLite in-memory.
How to configure an in-memory SQLite database?
My EfCore.TestSupport library provides a pre-configured static method that will set up the options for creating a SQLite database that is in-memory. The code below contains a simplified version of that method
public static DbContextOptions CreateOptions() where T : DbContext { //This creates the SQLite connection string to in-memory database var connectionStringBuilder = new SqliteConnectionStringBuilder { DataSource = ":memory:" }; var connectionString = connectionStringBuilder.ToString(); //This creates a SqliteConnectionwith that string var connection = new SqliteConnection(connectionString); //The connection MUST be opened here connection.Open(); //Now we have the EF Core commands to create SQLite options var builder = new DbContextOptionsBuilder<T>(); builder.UseSqlite(connection); return builder.Options; }
Note: The EfCore.TestSupport version has a few extra checks and options, but I left them out to focus on the in-memory bit.
How to use an in-memory database in a unit test
Having shown you the CreateOptions method now I want to show you how you can use that to create an in-memory version of the database that EF a simple unit test that uses the CreateOptions method to create an in-memory database.
Note: I am using the xUnit unit test package with fluent asserts.
[Fact] public void TestSQLiteOk() { //SETUP //Here create the options using SQLite CreateOptions var options = SqliteInMemory .CreateOptions<MyContext>(); //I create an instance of my DbContext using (var context = new MyContext(options)) { //You have to create the database context.Database.EnsureCreated(); context.SeedDatabaseFourBooks(); //ATTEMPT context.Books.First().Title = "New Title"; context.SaveChanges(); //VERIFY context.Books.First().Title.ShouldEqual("New Title"); } }
Using the pattern shown in the code just shown you can test all manner of EF Core code. But let me point out some parts of the code
- Lines 6-7: This is using my EfCore.TestSupport methods to set up the options to use a in-memory SQLite database.
- Line 9: This is where you create an instance of your application’s DbContext.
- Line 12: The context.Database.EnsureCreated(), is very important. This creates the in-memory database using the current entity classes and EF Core configuration of your application. In this case it will return an empty database.
- Line 13: You often need to fill the database (often called seeding the database) with appropriate data for a test. It turns out seeding the database can be quite complex in a big application. I tend to spend time creating helper methods, like the SeedDatabaseFourBooks method in this example, to set up specific database states.
- Lines 16-17: This is where you put the code you want to test. I showed a very simple example – yours is likely to call methods from you own code.
- Line 20: Here is the assert to check the method did what you expected it to do.
Using SQLite in-memory database for testing disconnected state updates
If you are using EF Core in an application such as ASP.NET Core, then you will have to test update methods that have to work across what is called the disconnected state. The disconnected state happens when an operation, normally an update, is split into two separate parts.
The figure below shows an example of the disconnected state where a user updates the publication date of a book. The first stage on the left is where the data is presented to the user to change. The second stage happens when the user presses the UPDATE button, which submits a new HTTP POST request to the web application which uses the information sent back to do the the update.
The important point about the disconnected state is you have two, separate instances of the application’s DbContext. And if you don’t test things in the same way you might miss some errors.
Both the InMemeory and SQLite in-memory databases can handle this problem. In SQLite’s case the database is held in the connection, so, as long as you use the same options you can create new instances of the DbContext that maps to the same database.
Let me give you an example of a bug that would only be picked up by having two instances of the application’s DbContext. In this case I want to add a new review to a book. A Book entity has has zero-to-many Review‘s and a user have add a new review, with a star rating – see figure below.
So, the standard way to add a new Review in a disconnected state is to load the Book entity with all its Reviews, add the review to the collection and then call SaveChanges.
Now, if I get that wrong, and I don’t load the current Reviews I can get a number of possible errors, but only if I replicate the disconnected state properly.
Let me show you the correct way to test this disconnected state, which shows the error.
[Fact] public void CorrectTestOfDisconnectedState() { //SETUP var options = SqliteInMemory .CreateOptions<MyContext>(); //In my first DbContext I make seed the database int bookId; using (var context = new MyContext(options)) { context.Database.EnsureCreated(); context.SeedDatabaseFourBooks(); bookId = context.Books.Last().BookId; } //In the second DbContext I test my disconnected update code using (var context = new MyContext(options)) { //ATTEMPT //I load a book BUT FORGET TO LOAD THE REVIEWS COLLECTION var book = context.Single(x => x.BookId == bookId); book.Reviews.Add( new Review{NumStars = 5}); //… rest of unit test left out, as has errored } }
Note: the correct code for line 22 would be
context.Single(x => x.BookId == bookId).Include(x => x.Reviews);
The point of all that is, if I had used one DbContext and seeded the database and ran the test of the Review update code, then it would have passed. That is because EF Core would have already been tracking the Reviews collections as from the SeedDatabaseFourBooks method, so the Reviews collection would already be present.
Note: In some of my unit tests I need to use three instances of the DbContext to correctly test something. One for seeding the database, one for running the test, and a final one to verify that the database has been written correctly.
Limitations of using SQLite to unit test EF Core
It’s likely that the actual database you use isn’t going to be SQLite. In that case you need to understand how SQLite differs from your actual database, especially if you use a feature that SQLite doesn’t support. The table below gives you the major areas that SQLite doesn’t support, or its support is so different that it will cause a problem.
These are all fairly advanced features, so if you don’t recognise them then you aren’t using them, so its fine to use SQLite for unit testing.
SQL feature | SQLite Support? |
Different schemas | NONE |
SQL Sequences | NONE |
SQL Computed columns | Different |
SQL user defined functions (UDFs) | Different |
SQL fragment default value | Different |
Note: There are other differences such as it doesn’t have the same types as other databases, for instance it stores strings in a TEXT type, while SQL Server has 8-bit varchar(nnn) and 16-bit nvarchar(nnn) types. These don’t normally cause a problem, but be aware in case some odd unit test result comes up.
So, if you use any of the features in the table then you can’t use SQLite in-memory database (nor InMemory – it has even more limitations than SQLite). If you can’t use SQLite you have to use the same database type that you are using for the application in your unit tests.
My EfCore.TestSupport library has help for other database types, with extra helpers for SQL Server databases, but that is most likely needs a new article to cover that.
Conclusion
This article shows you how you can simulate your database quickly and easily by using an in-memory database. I have shown you that SQLite is the best choice for an in-memory database, because it is a true relational database and will catch errors such as incorrect primary or foreign keys.
I also showed you how you can produce different instances of the application’s DbContext that all access the same in-memory database. This is useful for checking code that is used in applications such as ASP.NET Core, where you have a disconnected state.
There are some limitations to SQLite’s implementation of the SQL language, which might rule it out in your application. But for any ‘standard’ EF Core application using SQLite in-memory databases should be fine.
I hope this article, and my EfCore.TestSupport library, which is available as a NuGet package, with documentation of all its feature in the project’s Wiki, will help you write unit tests quicker.
Note: The chapter 15 of my book Entity Framework Core in Action is on unit testing EF Core application and goes into the whole topic in much more detail (its 32 pages long!). Manning Publications have offered the coupon to get 37% off the book – use fccefcoresmith (Christmas 2017 – watch out for 50% off offers too).
Happy coding.
Hi there!
I’m having issues with testing. in parallel. If I run tests in sequence they pass, but if I run them in parallel, they fail. All I’m doing are some tests based on seeded test data. (see below). What can be wrong?
[TestMethod]
public async Task MyTestMethod()
{
SqliteConnectionStringBuilder connectionStringBuilder = new SqliteConnectionStringBuilder
{
DataSource = “:memory:”,
Cache = SqliteCacheMode.Private
};
SqliteConnection connection = new SqliteConnection(connectionStringBuilder.ToString());
DbContextOptions<myContext> options = new DbContextOptionsBuilder<myContext>()
.UseSqlite(connection)
.Options;
using (var context = new myContext(options))
{
connection.Open();
context.Database.EnsureCreated();
await context.Seed();
}
using (var context = new myContext(options))
{
CountryZone z = context.CountryZones.AsNoTracking()
.Include(e => e.CountryZoneType)
.Single(e => e.UniqueId == CountryZoneTestData.TopCZ1);
Assert.AreEqual(CountryZoneTypeTestData.LEVEL_0_TOP, z.CountryZoneType.UniqueId);
}
connection.Close();
connection.Dispose();
}
[TestMethod]
public async Task MyTestMethod2()
{
SqliteConnectionStringBuilder connectionStringBuilder = new SqliteConnectionStringBuilder
{
DataSource = “:memory:”,
Cache = SqliteCacheMode.Private
};
SqliteConnection connection = new SqliteConnection(connectionStringBuilder.ToString());
DbContextOptions<myContext> options = new DbContextOptionsBuilder<myContext>()
.UseSqlite(connection)
.Options;
using (var context = new myContext(options))
{
connection.Open();
context.Database.EnsureCreated();
await context.Seed();
}
using (var context = new myContext(options))
{
Config setting = context.Configs.AsNoTracking().FirstOrDefault();
Assert.IsNotNull(setting);
Assert.AreEqual(ConfigSettings.PasswordMask, setting.Id);
}
connection.Close();
connection.Dispose();
}
Hi Aline,
I’m not totally sure why you have a problem but you have made a few changes from the example I show. Couple of things to that I don’t do are:
I suggest you try my version, which has been tested extensively, and see it that works. If my version doesn’t work, then you have some issue in your unit test setup.
I am trying to set up your code example in LinqPad as a unit test project. What I am missing is how you seed the database – there aren’t any details nor code samples so I can’t get this running. Can you help?
Tried to do something like
internal void SeedDatabaseFourBooks()
{
var reviews = new List<Review>()
{
new Review() { ReviewId = 1, NumStars = 5 },
new Review() { ReviewId = 2, NumStars = 5 }
};
this.Reviews.AddRange(reviews);
this.Books.Add(new Book() { BookId = 1, Title = “Mostly Harmless”,
Reviews = this.Reviews.Take(1).ToList() });
this.Books.Add(new Book() { BookId = 2, Title = “The Hobbit”,
Reviews = this.Reviews.Skip(1).Take(1).ToList() });
this.SaveChanges();
}
but it don’t work with your code (“this” refers to MyContext, SeedDatabaseFourBooks() is a method of MyContext).
Hi Matt,
I haven’t had much success of building tests in LinqPad. I’m not saying you can’t do that in LinqPad but I did try and it didn’t work, but I didn’t look into why.
What I would say that your SeedDatabaseFourBooks code looks OK, so I would try a super-simple seed and see if you can get it back.
Hi Matt,
Yes, seeding the database in an test can take the most time to set up. As you can see from the context.SeedDatabaseFourBooks() method in the test I often create extension methods to setup the database in certain ways. They work great, but sometimes you need a setup that is unique to the test, in which case I add the code in the setup part.
PS. See my answer to your other question about seeding in LinqPad.
PPS. You might like to look at the newer article called “New features for testing your Entity Framework Core 5 and above”, which shows the EF Core 5 context.ChangeTracker.Clear() method, which is useful in making sure you test emulates the read use setup – see this section of the article.
How do I get RowVersion field’s value while executing unit test cases?
The RowVersion in each database type is different, so if your production database isn’t SQLite, then you should really use your production database type to test code that uses the RowVersion. Having a mixture of SQLIte in-memory and production databases in your unit tests is fine (I do it all the time).
Another option is to use the .IsSqlite in your DbContext configuration and set up the SQLite RowVersion. See this article on how to set up the SQLite RowVersion.
I may have missed something. How to you force the SqlLite InMemory database to start empty for each test? If not how do you keep tests from conflicting when you have hundreds of tests to run if they share same data? I have switched using Bogus to generate unit test data for me.
With the InMemory Database you can just give database a different name. Not sure if this could cause memory issues though with hundreds of tests in a single project.
Hi D Nurtig,
The database is held in the SqliteConnection instance which held in the options variable. This means the database it linked to a local variable, so each database stays with each unit test.
PS. In the earlier versions of my EfCore.TestSupport library I didn’t dispose of the options variable and I didn’t have any problems with unit testing. But the recommended approach is to dispose the options variable, so version 5 of the EfCore.TestSupport library (which works with EF Core 5) I made the options variable disposable – see this section in an article on EF Core 5 and unit testing.
Hello Jon,
I’m afraid that theSQLite tests do seem to be sharing data. I’m using the SqliteInMemory exactly as you are doing, with a local variable holding the options.
I have three tests. When I run them all the first one succeeds, the second one detects more rows than it should detect (since it finds rows inserted in the first test as well), and the third test complains about a duplicate insert (because it is inserting data that was already inserted in the second test). However, when I run all test one by one, they all succeed.
EDIT: I am using version 5.0.0 of EfCore.TestSupport.
I have found the culprit. A certain testobject was a static instance and that caused the databases to be kept alive. When I converted it to method to create new instances each call this behavior stopped.
Hi Björn,
Glad you found it!
I am a bit surprised that the dispose of the DbContext didn’t kill it – EfCore.TestSupport 5 does a dispose if you your code uses a ‘using’ to dispose the instance of the DbContext.
Thank you for sharing your experiences and thoughts, Jon
Right in the beginning you mention that you wrote about 600 automated tests. Could you please tell us how long it takes to execute them? Beside the unit vs. integration test discussion, one big issue when it comes to testing is performance. With a standard mocking library and a mocked DbSet, I could always reach 100 tests per second. However, getting rid of the mocks is tempting so I’d be interested what the performance of EF Core with an in-memory database is.
Hi mu88,
Good question! It’s a bit hard to test as I have a mixture of SQLite in-memory and SQL Server unit tests. I tried to filter out the SQL Server unit tests and the elapsed time was about 4 to 5 seconds (The Test Explorer said they took 37 second, which makes sense because the tests are run in parallel).
So, from your timings SQLite in-memory is much slower than your times. You have to decide if that is too long or not.
Personally I use SQLite in-memory because it using a real database (with some limitations), so its testing my EF Core code properly. If I am testing some complex business logic build it with a mini-repository pattern so I can fake the database access.
PS. Have a look at the recent article “New features for unit testing your Entity Framework Core 5 code” on some changes to the EfCore.TestSupport library.
Great stuff and the package is really useful. As a token of appreciation AND also because it looks really nice, I bought your book 🙂
Keep pushing!
Thanks for your kind words Tiago, and the purchase of the book of course! Chapter 15 covers the “whys” of unit testing and uses this library throughout. In addition EfCore.TestSupport Wiki gives the the details and settings (see https://github.com/JonPSmith/EfCore.TestSupport/wiki ).
Since finishing the book I have created four more libraries – have a look at my GitHub site https://github.com/JonPSmith if you haven’t already.
Thanks for the extra juice, I bought the physical one at Book Depository, should get it over the weekend. Curious to explore!
This is an integration test not a unit test. You are not testing a functionality you have written but you are testing how the framework is dealing with a specific implementation details.
Writing unit tests need to be simple and quick. I don’t use a repository pattern (see https://www.thereformedprogrammer.net/is-the-repository-pattern-useful-with-entity-framework-core/ ) for basic CRUD (Create, Read, Update, Delete) operations and mocking the DbContext is hard work. Therefore I use an in-memory database.
If I’m writing business logic then I often use a repository pattern, which allows me to isolate the business logic. But CRUD its all about the the interaction with the database, such as did I read the right data or did I update the foreign key properly. In that case I use an in-memory database: its quick to write, quick to run, and checks that my CRUD operations work with a real database.
If that doesn’t suit you, then fine. But for me I actually like the link to the database as it catches errors I wouldn’t have found without it. The example of the disconnected update test in this article is one of many errors I wouldn’t have found without using EF directly.
ORM framework such as EF core and EF 6.0 deal with CRUD ( inserting/updating the correct FK and etc) given the model is correct and accurate. If we have to go that deep and handle such operations why we even use an ORM in the first place.
You said tests should be simple and quick but you also mentioned in the post : “In some of my unit tests I need to use three instances of the DbContext to correctly test something”
Do you still consider this simple?
Thanks for the reply.
Microsoft recommends against attempting to mock DbContext or IDbContext so any code the depends on it, such as a repository pattern object, needs to use an actual instance. It is possible to use layers and dependency inversion to insert Mocks, but there will always be this one layer that cannot be mocked. However I don’t see why this disqualifies it from being considered a unit test. I use a lot of real objects in unit tests. And in this case, it really is a mock, just simply implemented using the actual class like they do in monkey patching languages.
I don’t believe anyone’s arguing over that, but rather arguing over the definition of this type of test. If you’re hitting an in-memory database, then some would argue it’s an integration test and not a unit test, that’s all.
Your right Richard. I misinterpreted Michel Eissa’s original comment. Personally I find the different names for tests not that useful. In my more recent articles I have used the word “testing” or “automated testing”.
What I do convey that when you are testing an application using EF Core its so much easier to use a database (especially an in-memory database) than any other way.
It’s an integration test, but integration tests like this can cover a lot of territory that you might otherwise be writing unit tests for, only more efficiently. I think it’s important to have an efficient test strategy and that this can be an important and powerful element of it.
I don’t mind the name integration test, but the tests I write are checking either a) checking a service/business rules and the database is supplying the data for the test, or b) I’m checking that my EF Core code is working the way I want it to.
As I said in one of the other comments for complex business logic I use a per-business logic repository which allows me to mock out the database, but I rarely do that nowadays as EF Core has great features for unit testing.
Useful info, thanks.
But can you really still call these tests “unit tests”? IMHO when you do this, you test not only the piece of your implementation, but also the sqlite part.
Hi Štěpán,
While I don’t think I’m testing the Sqlite database provider (someone else has done that), but yes I am checking that my code can run on a SQL database, with all its constraints (well, the constraints that Sqlite has). Personally I find that useful, but I understand if you want to replace the database access with a mock or fake. The problem is that mocking EF is very hard.
Some people use a repository pattern on top of EF which allows you to mock the database. But I don’t use a repository pattern, so accessing a EF database is the next best thing (see my article https://www.thereformedprogrammer.net/is-the-repository-pattern-useful-with-entity-framework-core/ ).
There is no hard and fast way to do unit testing in EF Core, and this article describes one way that is pretty efficient in terms of quickly writing unit tests.
Hi Jon, In the Microsoft page [docs.microsoft.com/en-us/ef/core/miscellaneous/testing/sqlite] they close the connection after the unit test, but it seems that you don’t in your example (or repo). Will this cause an issue?
Hi Christian,
No, it won’t cause an issue in a unit test as the whole instance is dropped once the options part is disposed at the end of the unit test.
The thing I like about my SQLiteInMemory.CreateOptions() method is it hides all the connection setup and opening of the connection etc. Microsoft tend to write code that allows you to see how it works so that you can follow things, which I think is good. But when it comes to writing unit tests fast then a single method to set it all up in one go is what you want. I hope that answers your question.
PS. My CreateOptions method has an optional throwOnClientServerWarning (defaults to true) so that you get an exception if the LINQ you have written doesn’t convert to ‘good’ SQL. Another hidden, but useful feature.
Thanks Jon. That’s great to hear. I will give your code a try. Thanks for the quick reply.
I just discovered in-memory for testing (haven’t had a need in a while), and I love it. I love that you don’t have to deal with disposing of your test data at the end of the test, or worrying that someone else put something funky into a testing database somewhere (I have always like the idea of each test creating just the data it needs to test the functionality under test). I don’t have to know where on disk this create files, because it doesn’t. It runs really really fast. If I want my tests to run concurrently and independently, I can just give each one a different connection name, OR I can have them share data, my choice.
It makes mocking the database almost obsolete, in my opinion, and that is a big workload reduction.
Yes, once I found how to use SQLite in-memory it massively simplified unit tests. And as you say its a) quick, b) database starts out empty, and c) allows parallel unit testing, which xUnit does.
There are some things where SQLite doesn’t mimic a full SQL database such as SQL Server. I have a way to handle that at the moment, but I’m working on a better way to update its schema and empty its data.
Hi Jon and thanks for sharing you experience in working with EF Core.
I’ve installed EfCore.TestSupprot 1.8.0 via Nuget, and just wanted to point out that static class SQLiteInMemory is actually called SqliteInMemory (notice that SQL is capitalized). You might want to correct that in the blog post to make the example work.
Good catch! I have changed the post. Thanks for sharing.