Entity Framework Core makes it easy to write tests that execute against an in-memory store. Using an in-memory store is convenient since we don't need to worry about setting up a relational database. It also ensures our unit tests run quickly so we aren't left waiting hours for a large test suite to complete.
While Entity Framework Core's in-memory store works great for many scenarios, there are some situations where it might be better to run our tests against a real relational database. Some examples include when loading entities using raw SQL or when using SQL Server specific features that can not be tested using the in-memory provider. In this case, the tests would be considered an integration test since we are no longer testing our Entity Framework context in isolation. We are testing how it will work in the real world when connected to SQL Server.
The Sample Project
For this example, I used the following simple model and DbContext classes.
public class Monster
public class MonsterContext : DbContext
In an ASP.NET Core application, the context is configured to use SQL Server in the
DefaultConnection is defined in
appsettings.json which is loaded at startup.
MonsterContext is also configured to use Migrations which were initialized using the
dotnet ef migrations add InitialCreate command. For more on Entity Framework Migrations, see the official tutorial.
As a simple example, I created a query class that loads scary monsters from the database using a SQL query instead of querying the
public class ScaryMonstersQuery
To be clear, a better way to write this query is
_context.Monster.Where(m => m.IsScary == true), but I wanted a simple example. I also wanted to use
FromSql because it is inherently difficult to unit test. The
FromSql method doesn't work with the in-memory provider since it requires a relational database. It is also an extension method which means we can't simply mock the context using a tool like
Moq. We could of course create a wrapper service that calls the
FromSql extension method and mock that service but this only shifts the problem. The wrapper approach would allow us to ensure that
FromSql is called in the way we expect it to be called but it would not be able to ensure that the query will actually run successfully and return the expected results.
An integration test is a good option here since it will ensure that the query runs exactly as expected against a real SQL Server database.
I used xunit as the test framework in this example. In the constructor, which is the setup method for any tests in the class, I configure an instance of the
MonsterContext connecting to a localdb instance using a database name containing a random guid. Using a guid in the database name ensures the database is unique for this test. Uniqueness is important when running tests in parallel because it ensures these tests won't impact any other tests that aer currently running. After creating the context, a call to
_context.Database.Migrate() creates a new database and applies any Entity Framework migrations that are defined for the
public class SimpleIntegrationTest : IDisposable
The actual test itself happens in the
QueryMonstersFromSqlTest method. I start by adding some sample data to the database. Next, I create and execute the
ScaryMonstersQuery using the context that was created in the setup method. Finally, I verify the results, ensuring that the expected data is returned from the query.
The last step is the
Dispose method which in xunit is the teardown for any tests in this class. We don't want all these test databases hanging around forever so this is the place to delete the database that was created in the setup method. The database is deleted by calling
Use with Caution
These tests are slow! The very simple example above takes 13 seconds to run on my laptop. My advice here is to use this sparingly and only when it really adds value for your project. If you end up with a large number of these integration tests, I would consider splitting the integration tests into a separate test suite and potentially running them on a different schedule than my unit test suite (e.g. Nightly instead of every commit).
You can browse or download the source on GitHub.