Entity Framework Core

Exploring the InMemory Provider and SQLite In-Memory Database for Unit Testing in EF Core and Csharp

Mukesh Kumar Posted :6 Years Ago Views :28819

Writing Unit Test Cases is an essential aspect for any software development which helps us to test small building block 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 the 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 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 the multiple databases for a single project, one for development, one for testing and one for production.

The question arises here, can we reduce the number of databases, at least testing database 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, providing the step by step information how to write Unit Test Case for Entity Framework Core without actual database 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 can say, we have available two packages, which help for generating the in memory database on runtime as follows.

1. In-Memory Provider
2. 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 Relational Database but SQLite supports it. Both are useful as per scenario which is fitting best as per your requirement. So, let's move and 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 version 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 below image. To know more about, how to create Asp.Net Core Web API application and how to perform CRUD operation 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.

CRUD Operation in Asp.Net Core Web API with Entity Framework Core

CRUD Operations Unit Testing in Asp.Net Core Web API with xUnit

SQLite Project Structure

We hope you have created a similar solution structure as above. So, let 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 as follows. 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 points that it is the primary key for "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 inheriting the DbContext class. Here, we have two DbSet property for Category and Post models which will work as tables when we will perform query to getting 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 preparing the test project and install 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 following required packages.

Microsoft.EntityFrameworkCore
Microsoft.EntityFrameworkCore.InMemory
Microsoft.EntityFrameworkCore.Sqlite

The first package will provide all the required classes for using Entity Framework Core functionality within the xUnit project and rest of two 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 quickly is that how we will get the instance of the BlogDBContext class, so that 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 create one class as "ConnectionFactory" which check the connection, generate the runtime database and return the context for BlogDBContext.

In the following class, we have two methods as "CreateContextForMemory" which will create a database on runtime as name "Test_Database" and before returning the object of BlogDBContext, it ensures that database has deleted from the memory with the similar name if available and regenerated again. We have another method as "CreateContextForSQLite" for SQLite which also return the object of BlogDBContext after validating the connection string and ensuring that database has deleted and 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 above Connection Factory class is returning the BlogDBContect class object for In-Memory Provider and SQLite In-Memory Database. So, it's time to writing some Unit Test Cases. Let 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 cases have three portions as "Arrange", "Act" and "Assert". In the "Arrange" section, we are preparing the object of BlogDBContext from the ConnectionFactory class, in "Act" section, executing the action to the database and in the "Assert" section, just checking the data and validating it. So, let's understand about these test case one by one as follows.

Task_Add_Without_Relation: As we are using the Relational Database, but in this test case, we are not passing data along with relational mapping and see data get saved into 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 see 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: This test case, we have written this test case to test the time is taken by In-Memory Provider while adding 1000 records in one go. To 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 follow 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 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 cases have three portions as "Arrange", "Act" and "Assert". In the "Arrange" section, we are preparing the object for context from the ConnectionFactory class, in "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: As we are using the Relational Database, but in this test case, we are not passing data with relational mapping and see 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 be failed.
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 get failed and return 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 try 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: This test case, we have written this test case to test the time is taken by SQLite while adding 1000 records in one go. To 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 follow 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 total 7 Unit Test Cases as 3 for In-Memory Provider and 4 for SQLite In-Memory database. To run the Test Cases, open Test Explorer and click to Run All button and you will find that all test cases pass successfully as shown in below image. Here you can also find the comparison for executing 1000 records in one go between both.

Test Explorer

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 comment which helps me to improve myself for next post. If you have any doubts please ask your doubts or query in the comment section and If you like this post, please share it with your friends. Thanks

EFCore

SQLite

InMemory

Unit Testing

Share this article

Next >>

<< Prev

Posted Comments :

Gautam Sharma Posted : 6 Years Ago

Hi buddy nice post and please also check out my post https://www.codingshiksha.com/blogs/android/firebase-phone-authentication-in-android-full-tutorial/

Jeff Posted : 6 Years Ago

Nice one, thanks a lot !

Astrid Posted : 4 Years Ago

Hello, I'm making a unit testing. for projecting i have to use squence from sqm server programmability. so for db testing I use sqlLite. how do you create squence from sqlLite?

Reham Posted : 2 Years Ago

Nice post :) but I was waiting more details about repositories

Leave your comment

Your Name

Email Id

Your Site

Your Comment

Latest eBook Download

Azure DevOps CI/CD Pipeline Complete Practical Guide - By Mukesh Kumar

CSharp 6 - Practical Guide - By Mukesh Kumar