SQLite for Unit Testing in .Net Core

by

Hello everyone, and welcome back to dotnetcorecentral. I have been receiving requests from a few of you to create a post and YouTube video on unit testing with SQLite and .Net Core. So here it is.

What is SQLite

SQLite is a fully-featured SQL database engine. It is a small, fast, self-contained and highly reliable database engine. SQLite is the most used database engine in the World. It is built into all mobile phones, which I did not know before researching for this blog post.

Setup

Firstly, I will create a .Net Core console application. Which will connect to SQL Server express using Entity Framework. Secondly, I will create a unit testing project with NUnit, which will use SQLite in-memory for unit testing the database components.

Creating a .Net Core Console Application

Firstly, I will open Visual Studio 2017, go to File -> New -> Project. Select .Net Core -> ASP.NET Core Console Application. This will create a new ASP.Net Core console application.

SQLite new project

Secondly, I will create a Test application. For that, I will add a new Class Library project to the solution.

Sqlite test project

Adding Nuget Package

Firstly, I will add the Entity Framework NuGet package to the SqliteDemo console application. Therefore, right-click on the project and select Manage Nuget Packages. Once the Nuget package manager window pops up, in the Browse tab search for EntityFrameworkCore. I will install 2.2.6 stable version.

Entity Framework Core Nuget

Secondly, I will add the EntityFrameworkCore.SqlServer to the SqliteDemo console application. I will install 2.2.6 version.

Entity Framework Core SqlServer Nuget

Additionally, I will install the following NuGet packages to the Unit Test Class library project.

  • Microsoft.EntityFrameworkCore.Sqlite
  • NUnit
  • NUnit3TestAdapter
  • Microsoft.NET.Test.Sdk

Note: Microsoft.EntityFrameworkCore.Sqlite will install Microsoft.EntityFrameworkCore as a dependency.

Unit test project nuget packages

The Database

As mentioned in the setup, I will connect to a SQL Server Express and access a table. And during testing, I will use the same table structure in the SQLite database.

For this example, I will read from the Employee table. Which contains employee information. That is the name, address, and phone.

Data access class

Now, I will create a class EmployeeContext deriving from DbContext of EntityFrameworkCore, to create my data access class. This class will return all Employee information from database. Hence, I will create a Employee data entity class as well.

For the Employee class, I will annotate the properties with the Column attribute from System.ComponentModel.DataAnnotations.Schema namespace. This is needed, as the column names in the database are different from the property names of the class.

The implementation of EmployeeContext is straight forward. It is taking connection string in the constructor. And Overriding OnConfiguring method to setup Entity Framework to use SQL Server.

using System.ComponentModel.DataAnnotations.Schema;

namespace SqliteDemo
{
    [Table("Employee")]
    public class Employee
    {
        public int Id { get; set; }
        [Column("first_name")]
        public string FirstName { get; set; }
        [Column("last_name")]
        public string LastName { get; set; }
        public string Address { get; set; }
        [Column("home_phone")]
        public string HomePhone { get; set; }
        [Column("cell_phone")]
        public string CellPhone { get; set; }
    }
}

using Microsoft.EntityFrameworkCore;

namespace SqliteDemo
{
    public class EmployeeContext : DbContext
    {
        private readonly string connectionString;

        public EmployeeContext(string connectionString)
        {
            this.connectionString = connectionString;
        }

        public DbSet<Employee> Employees { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(connectionString);
        }
    }
}

Employee Provider

Next, I will create an interface and class for accessing the Employee. The interface IEmployeeProvider is the contract for accessing an Employee by id. And class EmployeeProvider will contain the implementation.

The implementation of class EmployeeProvider is to take EmployeeContext as a constructor parameter. And in the Get method use it to return an Employee matching the id.

namespace SqliteDemo
{
    public interface IEmployeeProvider
    {
        Employee Get(int id);
    }
}

using System.Linq;

namespace SqliteDemo
{
    public class EmployeeProvider : IEmployeeProvider
    {
        private readonly EmployeeContext employeeContext;

        public EmployeeProvider(EmployeeContext employeeContext)
        {
            this.employeeContext = employeeContext;
        }

        public Employee Get(int id)
        {
            return employeeContext.Employees.Where(e => e.Id == id).FirstOrDefault();
        }
    }
}

Wiring up the Main method

Finally, I will wire everything up in the Main method, to print out the name and address of an Employee with id 1002. 

using System;

namespace SqliteDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            var employeeContext = new EmployeeContext(@"Persist Security Info = False; Integrated Security = true; Initial Catalog = TimeManagement; server = Nirjhar-Tab\SQLEXPRESS");
            var employeeProvider = new EmployeeProvider(employeeContext);
            var employee = employeeProvider.Get(1002);
            Console.WriteLine($"Name: {employee.FirstName} {employee.LastName}, Address: {employee.Address}");
        }
    }
}

Checking the output

Now that the code to access the database is complete, I will run the application and check the output.

SQLite console output

And the output of the application is as expected.

Setting up the Unit Test with SQLite

Firstly, I will create a class EmployeeProviderTest in the unit test project. Secondly, I will create a method Test_Get_By_Id inside of the class, which will contain the testing code.

To use SQLite, I will have to create an instance of SqliteConnection. But, the EmployeeContext does not have a way to accept a connection object. Hence, I will have to modify the implementation of EmployeeContext. In other words, apart from just accepting connecting string in the constructor, I will create an overloaded constructor. However, the overloaded constructor will not take a connection object, instead, it will take a DbContextOptions<EmployeeContext>. This is needed as we would be setting the context option on the test method.

Since an overloaded constructor in the EmployeeContext will take DbContextOptions, hence, we will need to ignore DbContextOptionsBuilder configuration if it is already configured. And we will do that by checking if it is already configured in the overridden OnConfiguring method.

using Microsoft.EntityFrameworkCore;

namespace SqliteDemo
{
    public class EmployeeContext : DbContext
    {
        private readonly string connectionString;

        public EmployeeContext(string connectionString)
        {
            this.connectionString = connectionString;
        }

        public EmployeeContext(DbContextOptions<EmployeeContext> options) : base(options)
        {
        }

        public DbSet<Employee> Employees { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                optionsBuilder.UseSqlServer(connectionString);
            }
        }
    }
}

After that, I will implement the test method. Firstly, I will create a SqliteConnection in-memory SQLite database option. Secondly, I will DbContextOptionsBuilder for EmployeeContext and configure it to use SQLite.

var connection = new SqliteConnection("DataSource=:memory:");
connection.Open();

var options = new DbContextOptionsBuilder<EmployeeContext>().UseSqlite(connection).Options;

In addition, I will ensure that the database with the Employee table is created.

using (var context = new EmployeeContext(options))
{
    context.Database.EnsureCreated();
}

Subsequently, I will create a record in the Employee table.

using (var context = new EmployeeContext(options))
{
    context.Employees.Add(new Employee { Id = 1, FirstName = "John", LastName = "Doe", Address = "123 Street", HomePhone = "111-111-1111", CellPhone = "222-222-2222" });
    context.SaveChanges();
}

Finally, I will test the EmployeeProvider with the EmployeeContext that is using SQLite.

using (var context = new EmployeeContext(options))
{
    var provider = new EmployeeProvider(context);
    var employee = provider.Get(1);

    Assert.AreEqual("John", employee.FirstName);
}

The complete class implementation below:

using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using NUnit.Framework;

namespace SqliteDemo.Test
{
    [TestFixture]
    public class EmployeeProviderTest
    {
        [Test]
        public void Test_Get_By_Id()
        {
            var connection = new SqliteConnection("DataSource=:memory:");
            connection.Open();

            var options = new DbContextOptionsBuilder<EmployeeContext>().UseSqlite(connection).Options;

            using (var context = new EmployeeContext(options))
            {
                context.Database.EnsureCreated();
            }

            using (var context = new EmployeeContext(options))
            {
                context.Employees.Add(new Employee { Id = 1, FirstName = "John", LastName = "Doe", Address = "123 Street", HomePhone = "111-111-1111", CellPhone = "222-222-2222" });
                context.SaveChanges();
            }

            using (var context = new EmployeeContext(options))
            {
                var provider = new EmployeeProvider(context);
                var employee = provider.Get(1);

                Assert.AreEqual("John", employee.FirstName);
            }
        }
    }
}

Running the test with SQLite

Next, I will run the test, and it is green.

SQLite unit test result

Conclusion

In conclusion, I think SQLite can be a very powerful tool in our toolbox. Given it is a fully functional SQL database engine. Using it with .Net Core is super simple and very easy.

I have added the entire process of coding on YouTube: https://youtu.be/9O-m6okOCQ0

Some links: https://sqlite.org/index.html

Source Code: https://github.com/choudhurynirjhar/SqliteDemo