Unit Testing With InMemory Provider And SQLite In-Memory Database In EFCore

Writing Unit Test Cases is an essential aspect of any software development which helps us to test small building blocks of an application. While doing development, we write test cases side by side, just parallel to development code. To test the Unit Test cases, we generally try to recreate a similar environment as we have in the development phase so that testing the code becomes easy. But every time, this is not feasible. Let's say one example is if our application is using the database and we would like to test our application then we need a database similar to the dev database with all tables, views, and other components available inside that database at the time of testing. It is a very challenging task to maintain multiple databases for a single project, one for development, one for testing, and one for production.

Download Code from here.

The question arises here, can we reduce the number of databases, at least testing databases, without affecting the work and generate the database on runtime and destroy it once the task is completed?

The answer to the above question is 'YES'. Yes, it is possible in Entity Framework Core to generate the database on runtime and use it, execute test cases, and destroy it. If you are working with Asp.Net Core and Entity Framework Core then Unit Testing is very easy. In Entity Framework Core, we have two packages which help us to create the database on runtime as similar to the actual database and its tables as well. It provides the similar database functionality without over heading the actual database. An objective of this article is to provide step by step information on how to write Unit Test Case for Entity Framework Core without the actual database being ready. We will see how to generate the database on runtime in memory with the help of two packages available in EF Core.

Here in Entity Framework Core, we have two different ways or we can say, we have available two packages, which help for generating the in-memory database on runtime as follows.

In-Memory Provider
SQLite In-Memory Database

Both In-Memory Provider and SQLite In-Memory Database create the database on runtime in memory space but the difference is that In-Memory does not support a Relational Database but SQLite supports it. Both are useful as per the scenario which best fits your requirement. So, let's see how we can implement In-Memory and SQLite in Entity Framework Core and use it while writing Unit Test Cases.

So, let's move to the practical demonstration without wasting much time. Just open Visual Studio 2017 or higher and create an ASP.NET Core Web API application project and add a xUnit Testing project in the same solution. The final solution structure will be similar to the below image. To know more about how to create ASP.NET Core Web API application and how to perform CRUD operations in ASP.NET Core Web API or how to write Unit Test Cases for ASP.NET Core Web API CURD operations, you can follow these articles.

Unit Testing With InMemory Provider And SQLite In-Memory Database In EFCore

We hope you have created a similar solution structure as above. So, let's move and create one folder as "Model" inside the "InMemoryVsSQLiteDemo" project and create two model classes such as "Category" and "Post" along with one Db Context class as "BlogDBContext".

"Post" class will be similar to the following. Here you can see, we have multiple properties and we are marking "CategoryId" properties as Foreign Key for "Id" field which is pointing to "Category" model Id property.

using System;
using System.ComponentModel.DataAnnotations.Schema;
namespace InMemoryVsSQLiteDemo.Model
{
public partial class Post
{
public int PostId { get; set; }
public string Title { get; set; }
public string Description { get; set; }
[ForeignKey("Id")]
public int CategoryId { get; set; }
public DateTime? CreatedDate { get; set; }
public virtual Category Category { get; set; }
}
}

Following is the "Category" model class which has Id property as key, which shows that it is the primary key for the "Category" table and some other properties as well.

using System.ComponentModel.DataAnnotations;
namespace InMemoryVsSQLiteDemo.Model
{
public partial class Category
{
[Key]
public int Id { get; set; }
public string Name { get; set; }
public string Slug { get; set; }
}
}

Now, let's move to BlogDBContext class which is inheriting the DbContext class. Here, we have two DbSet properties for Category and Post models which will work as tables when we will perform a query to get data from runtime database.

using System;
using Microsoft.EntityFrameworkCore;
namespace InMemoryVsSQLiteDemo.Model
{
public partial class BlogDBContext : DbContext
{
public BlogDBContext(){}
public BlogDBContext(DbContextOptions<BlogDBContext> options)
: base(options){}
public virtual DbSet<Category> Category { get; set; }
public virtual DbSet<Post> Post { get; set; }
}
}

So, we are not going to do much more inside the main project. Now, it's time to prepare the test project and install the required components to use In-Memory Provider and SQLite In-Memory Database. So, let's move to "InMemoryVsSQLiteDemo.Test" project and open NuGet Package Manager and install the following required packages.

EntityFrameworkCore
EntityFrameworkCore.InMemory
EntityFrameworkCore.Sqlite

The first package will provide all the required classes for using Entity Framework Core functionality within the xUnit project and the rest are for In-Memory Provider and SQLite In-Memory Database respectively. These packages will help to create a database on runtime.

The first question which comes to mind is how will we get the instance of the BlogDBContext class, so that we can access the model classes. So to do that, right click on "Dependencies" of the "InMemoryVsSQLiteDemo.Test" project and add the main project reference.

Now, let's create one class as "ConnectionFactory" which checks the connection, generates the runtime database and returns the context for BlogDBContext.

In the following class, we have two methods as "CreateContextForMemory" which will create a database on runtime with the name "Test_Database" and before returning the object of BlogDBContext, it ensures that database has been deleted from the memory with the similar name if available and regenerated again. We have another method as "CreateContextForSQLite" for SQLite which also returns the object of BlogDBContext after validating the connection string and ensuring that the database has deleted and been re-created in memory.

using InMemoryVsSQLiteDemo.Model;
using Microsoft.Data.SQLite;
using Microsoft.EntityFrameworkCore;
using System;
using System.IO;
namespace InMemoryVsSQLiteDemo.Test
{
public class ConnectionFactory : IDisposable
{
#region IDisposable Support
private bool disposedValue = false; // To detect redundant calls
public BlogDBContext CreateContextForInMemory()
{
var option = new DbContextOptionsBuilder<BlogDBContext>().UseInMemoryDatabase(databaseName: "Test_Database").Options;
var context = new BlogDBContext(option);
if (context != null)
{
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
}
return context;
}
public BlogDBContext CreateContextForSQLite()
{
var connection = new SqliteConnection("DataSource=:memory:");
connection.Open();
var option = new DbContextOptionsBuilder<BlogDBContext>().UseSqlite(connection).Options;
var context = new BlogDBContext(option);
if (context != null)
{
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
}
return context;
}
protected virtual void Dispose(bool disposing)
{
if (!disposedValue)
{
if (disposing)
{
}
disposedValue = true;
}
}
public void Dispose()
{
Dispose(true);
}
#endregion
}
}

As the above Connection Factory class is returning the BlogDBContect class object for In-Memory Provider and SQLite In-Memory Database, it's time to write some Unit Test Cases. Let's create a new class as "InMemoryDataProviderTest" where we will write the Unit Test Cases for In-Memory Provider. Here we are writing three Unit Test Case as follows.

Every test case has three portions as "Arrange", "Act" and "Assert". In the "Arrange" section, we are preparing the object of BlogDBContext from the ConnectionFactory class, in the "Act" section, we are executing the action to the database and in the "Assert" section, we are just checking the data and validating it. So, let's understand about these test case one by one as follows.

Task_Add_Without_Relation: We are using the Relational Database, but in this test case, we are not passing data along with relational mapping and seeing data get saved into the database and test case pass successfully. It is because In-Memory does not support Relational Database.
Task_Add_With_Relation: Here in this test case, we are passing data with relational mapping and seeing data get saved in the database again and test case passes successfully without any error. It is because In-Memory does not support Relational Database and it does not affect if Primary and Foreign key relationship data are not valid.
Task_Add_Time_Test: We have written this test case to test the time taken by In-Memory Provider while adding 1000 records in one go. You see the time comparison between In-Memory and SQLite for executing the 1000 records in one go in the last image.

You can find the whole code as follows for In-Memory Provider.

using InMemoryVsSQLiteDemo.Model;
using System;
using System.Linq;
using Xunit;
namespace InMemoryVsSQLiteDemo.Test
{
public class InMemoryDataProviderTest
{
[Fact]
public void Task_Add_Without_Relation()
{
//Arrange
var factory = new ConnectionFactory();
//Get the instance of BlogDBContext
var context = factory.CreateContextForInMemory();
var post = new Post() { Title = "Test Title 3", Description = "Test Description 3", CreatedDate = DateTime.Now };
//Act
var data = context.Post.Add(post);
context.SaveChanges();
//Assert
//Get the post count
var postCount = context.Post.Count();
if (postCount != 0)
{
Assert.Equal(1, postCount);
}
//Get single post detail
var singlePost = context.Post.FirstOrDefault();
if (singlePost != null)
{
Assert.Equal("Test Title 3", singlePost.Title);
}
}
[Fact]
public void Task_Add_With_Relation()
{
//Arrange
var factory = new ConnectionFactory();
//Get the instance of BlogDBContext
var context = factory.CreateContextForInMemory();
var post = new Post() { Title = "Test Title 3", Description = "Test Description 3", CategoryId = 2, CreatedDate = DateTime.Now };
//Act
var data = context.Post.Add(post);
context.SaveChanges();
//Assert
//Get the post count
var postCount = context.Post.Count();
if (postCount != 0)
{
Assert.Equal(1, postCount);
}
//Get single post detail
var singlePost = context.Post.FirstOrDefault();
if (singlePost != null)
{
Assert.Equal("Test Title 3", singlePost.Title);
}
}
[Fact]
public void Task_Add_Time_Test()
{
//Arrange
var factory = new ConnectionFactory();
//Get the instance of BlogDBContext
var context = factory.CreateContextForInMemory();
//Act
for (int i =1; i<=1000; i++)
{
var post = new Post() { Title = "Test Title "+i, Description = "Test Description "+i, CategoryId = 2, CreatedDate = DateTime.Now };
context.Post.Add(post);
}
context.SaveChanges();
//Assert
//Get the post count
var postCount = context.Post.Count();
if (postCount != 0)
{
Assert.Equal(1000, postCount);
}
//Get single post detail
var singlePost = context.Post.Where(x=>x.PostId == 1).FirstOrDefault();
if (singlePost != null)
{
Assert.Equal("Test Title 1", singlePost.Title);
}
}
}
}

Now, let's write the Unit Test Case for SQLite and see what are the differences between In-Memory Provider and SQLite. So, let's create a class as "SQLiteTest". Here we also follow the same rule while writing the test case as above. Every test case has three portions as "Arrange", "Act" and "Assert". In the "Arrange" section, we are preparing the object for context from the ConnectionFactory class, in the "Act" section, executing the action to the database, and in the "Assert" section, just checking the data and validating it.

Here, we are writing the four different test cases as follows for different purposes.

Task_Add_Without_Relation: We are using the Relational Database, but in this test case, we are not passing data with relational mapping and seeing it while saving data to the database, we get the DbUpdateException and we need to handle it to pass the Test Case successfully. It means if the database is a relational database and we are using SQLite and we are not passing the appropriate data then it will fail.
Task_Add_With_Relation_Return_Exception: Here, we are passing the data for a relational database but as you can see that, we are passing CategoryId =2 which should exist in the Category table. But for now "Category" is empty. So, here Primary Key and Foreign Key relationship failed and returned DbUpdateException, which needs to be handled.
Task_Add_With_Relation_Return_No_Exception: This test case will run successfully because first, we are preparing the data for the "Category" table and then trying to push the data into "Post" table along with category id which already exists inside the Category table. So, here Primary Key and Foreign Key relationship get passed.
Task_Add_Time_Test: We have written this test case to test the time taken by SQLite while adding 1000 records in one go. See the time comparison between In-Memory and SQLite for executing the 1000 records in one go in the last image.

You can find whole code as follows for SQLite In-Memory Database.

using InMemoryVsSQLiteDemo.Model;
using Microsoft.EntityFrameworkCore;
using System;
using System.Linq;
using Xunit;
namespace InMemoryVsSQLiteDemo.Test
{
public class SQLiteTest
{
[Fact]
public void Task_Add_Without_Relation()
{
//Arrange
var factory = new ConnectionFactory();
//Get the instance of BlogDBContext
var context = factory.CreateContextForSQLite();
var post = new Post() { Title = "Test Title 3", Description = "Test Description 3", CreatedDate = DateTime.Now };
//Act
var data = context.Post.Add(post);
//Assert
Assert.Throws<DbUpdateException>(() => context.SaveChanges());
Assert.Empty(context.Post.ToList());
}
[Fact]
public void Task_Add_With_Relation_Return_Exception()
{
//Arrange
var factory = new ConnectionFactory();
//Get the instance of BlogDBContext
var context = factory.CreateContextForSQLite();
var post = new Post() { Title = "Test Title 3", Description = "Test Description 3", CategoryId = 2, CreatedDate = DateTime.Now };
//Act
var data = context.Post.Add(post);
//Assert
Assert.Throws<DbUpdateException>(() => context.SaveChanges());
Assert.Empty(context.Post.ToList());
}
[Fact]
public void Task_Add_With_Relation_Return_No_Exception()
{
//Arrange
var factory = new ConnectionFactory();
//Get the instance of BlogDBContext
var context = factory.CreateContextForSQLite();
var post = new Post() { Title = "Test Title 3", Description = "Test Description 3", CategoryId = 2, CreatedDate = DateTime.Now };
//Act
for (int i = 1; i < 4; i++){
var category = new Category() { Id = i, Name = "Category " + i, Slug = "slug" + i };
context.Category.Add(category);
}
context.SaveChanges();
var data = context.Post.Add(post);
context.SaveChanges();
//Assert
//Get the post count
var postCount = context.Post.Count();
if (postCount != 0)
{
Assert.Equal(1, postCount);
}
//Get single post detail
var singlePost = context.Post.FirstOrDefault();
if (singlePost != null)
{
Assert.Equal("Test Title 3", singlePost.Title);
}
}
[Fact]
public void Task_Add_Time_Test()
{
//Arrange
var factory = new ConnectionFactory();
//Get the instance of BlogDBContext
var context = factory.CreateContextForInMemory();
//Act
for (int i = 1; i < 4; i++)
{
var category = new Category() { Id = i, Name = "Category " + i, Slug = "slug" + i };
context.Category.Add(category);
}
context.SaveChanges();
for (int i = 1; i <= 1000; i++)
{
var post = new Post() { Title = "Test Title " + i, Description = "Test Description " + i, CategoryId = 2, CreatedDate = DateTime.Now };
context.Post.Add(post);
}
context.SaveChanges();
//Assert
//Get the post count
var postCount = context.Post.Count();
if (postCount != 0)
{
Assert.Equal(1000, postCount);
}
//Get single post detail
var singlePost = context.Post.Where(x => x.PostId == 1).FirstOrDefault();
if (singlePost != null)
{
Assert.Equal("Test Title 1", singlePost.Title);
}
}
}
}

Now, we have written seven Unit Test Cases, with three for In-Memory Provider and four for SQLite In-Memory database. To run the Test Cases, open Test Explorer and click to Run All Buttons and you will find that all test cases pass successfully as shown in the below image. Here you can also find the comparison for executing 1000 records in one go between both.

Unit Testing With InMemory Provider And SQLite In-Memory Database In EFCore

Conclusion

So, today we have learned how to write Unit Test Cases with help of In-Memory Provider and SQLite In-Memory Database in Entity Framework Core.

I hope this post will help you. Please put your feedback using comments which helps me to improve myself for next post. If you have any doubts please ask your doubts or queries in the comment section and if you like this post, please share it with your friends. Thanks.