When writing unit tests for EF Core we can use InMemoryDatabase so that we don’t have to create mock repositories. But sometimes we need to test functionalities that are not supported in in-memory-database.
[embedded content] In-memory-database does not support:
Executing SQL commands
Enforcing foreign keys
Returning null objects for properties that needed to be included in the query .Include()
Failing when using features not supported in Linq-to-sql queries
Of course, we can mock the data layer but sometimes we need to test if the database’s queries are playing well the application logic.
The solution is to use SQLite in-memory DB which allows us to use some of the SQL and DB relational features.
An example of creating DB context that switches between in-memory-database and SQLite database:

private bool _useSqlite;

public void UseSqlite()
{
_useSqlite = true;
}

public async Task GetDbContext()
{
DbContextOptionsBuilder builder = new DbContextOptionsBuilder();
if (_useSqlite)
{
// Use Sqlite DB.
builder.UseSqlite(“DataSource=:memory:”, x = > { });
}
else
{
// Use In-Memory DB.
builder.UseInMemoryDatabase(Guid.NewGuid().ToString()).ConfigureWarnings(w = >
{
w.Ignore(InMemoryEventId.TransactionIgnoredWarning);
});
}

var dbContext = new SampleDbContext(builder.Options);
if (_useSqlite)
{
// SQLite needs to open connection to the DB.
// Not required for in-memory-database and MS SQL.
await dbContext.Database.OpenConnectionAsync();
}

await dbContext.Database.EnsureCreatedAsync();

return dbContext;
}

Example of a unit test:

public async Task ShouldThrowAnException()
{
var context = GetDbContext();
context.TestDatas.Add(new TestData
{
Text = “Parent”,
IsDeleted = false,
Child = null
});

// Will not fail even though Child is required.
await context.SaveChangesAsync();

// Execute and assert
// This fails because in-memory-database does not support SQL.
await Assert.ThrowsAsync(
() = > context.TestDatas
.FromSqlRaw(@”select * from TestDatas”));
}

[Fact]
public async Task ShouldWork()
{
// Prepare
UseSqlite();

var context = GetDbContext();
context.TestDatas.Add(new TestData
{
Text = “Parent”,
IsDeleted = false,
Child = new ChildData
{
Text = “Child”
}
});

// Checks if Child property is correctly populated
context.SaveChanges();

// Execute
var data = await context.Database.GetDbConnection()
.QueryAsync(@”select * from TestDatas”);

// Assert
Assert.Single(data);
}

However, there are a few caveats regarding using SQLite in unit tests:
In-memory SQLite is around 5 3 times slower than EF Core in-memory-database
The order of results will not be consistent unless it’s ordered by a column
For complex database structures foreign key restrictions will not work and needs to be disabled in order to allow testing
If dealing with raw SQL commands, there are some differences between SQLite and MS SQL. It means that the code might need to generate slightly different SQL for SQLite than for MS SQL
The best approach so far is to choose in-memory-database and SQLite in-memory database based on the unit test scenario. Personally, I always prefer in-memory-database because of the performance benefits and I switch to SQLite when absolutely necessary.
Watch my DDD talk for more unit test content (DDD Sydney 2018)
[embedded content]
UPDATE: I’m going around Australia to present this topic on various user groups. Here is the GitHub repository for the session: https://github.com/jernejk/NorthwindTradersUPDATE 2: Updated the blog post and GitHub code for .NET Core 3.0.

This is the second in a series of posts about building Single Page Applications with ASP.NET Core 2.0. In this post, I’ll show you how to upgrade to Angular 5. …
The post SPA & ASP.NET Core – Part 2: Upgrading to Angular 5 appeared first on Jason Taylor.

This is the first in a series of posts about building Single Page Applications with ASP.NET Core. As a developer, keeping up with the latest technologies is not always easy! …
The post SPA & ASP.NET Core – Part 1: Create a SPA appeared first on Jason Taylor.

Microsoft offers a large selection of professional certifications and exams focused on their core technologies.  Often I have encountered developers who hold the opinion that these exams are not useful since they learn a lot more by using …
The post Successfully Passing Microsoft Exams appeared first on Jason Taylor.

Code reviews, also known as peer reviews or code walkthroughs, are an effective way of identifying defects and sharing knowledge. A code review takes place when a programmer engages a …
The post The Importance of Code Reviews appeared first on Jason Taylor.