Objective

Creating repeatable Unit Test cases for a .Net applications which uses Entity Framework Core for database operations.

It is recommended to write independent and repeatable test cases for the business logic. We need to handle certain cases where logic depends upon the data already existing in database. In such scenarios we can use an in-memory database like SQLite.

Setup

Following tech stack is used in this example

1. .Net Core 3.1
2. NUnit 3.12
3. EntityFrameworkCore Sqlite 3.1.5
4. Visual Studio Code

Application

Let’s build a school management application which is going to check if there is vacancy for admission in a class.

Create a new dotnet console application SchoolApp:

Add Model classes for Student and Class information:

These classes are associated with database tables and have Foreign Key relationship.

Add AppDBContext extending EntityFrameworkCore DbContext.

This class contains the DbSets for Model classes

Override DBContextOptions constructor and OnConfiguring() so that the application uses in-memory SQLite for unit testing but a different Relational database like Oracle OR SQL Server during actual Production deployment

Define Primary Key for each Model class

Complete Class https://gitlab.com/saurabhjain1537/nunit-sqlite-schoolapp/-/blob/master/SchoolApp/Data/AppDBContext.cs

    public class AppDBContext:DbContext
    {
        public DbSet<SchoolClass> SchoolClasses { get; set;}
        public DbSet<Student> Students {get; set;}

        public AppDBContext() : base()
        {
        }

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

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if(!optionsBuilder.IsConfigured)
            {
                optionsBuilder.UseOracle("TODO - connection string");
            }
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<SchoolClass>(s =>
                {
                    s.HasKey(k => new {k.Id});
                });

            modelBuilder.Entity<Student>(s =>
                {
                    s.HasKey(k => new {k.Id});
                });
        }

    }

Add Admission class having appDBOptions variable.

This class has a constructor to initialize DbContextOptions. The method getAppDBContext() is responsible for providing the appropriate DBContext considering if the application is actually deployed OR running unit tests.

Add a hasVacancy() method to determine if we can allow new admission in a particular school-class.

Complete Class https://gitlab.com/saurabhjain1537/nunit-sqlite-schoolapp/-/blob/master/SchoolApp/Admission.cs

    public class Admission
    {
        private DbContextOptions<AppDBContext> appDBOptions;

        public Admission()
        {
            this.appDBOptions = null;
        }

        public Admission(DbContextOptions<AppDBContext> appDBOptions)
        {
            //This constructor is used for Unit testing
            this.appDBOptions = appDBOptions;
        }

        private AppDBContext getAppDBContext()
        {
            if(this.appDBOptions == null)
            {
                return new AppDBContext();
            }
            else
            {
                return new AppDBContext(this.appDBOptions);
            }

        }

        public bool hasVacancy(string Standard)
        {
            bool hasVacancy = false;

            using(var context = getAppDBContext())
            {
                hasVacancy = context.SchoolClasses
                                        .Where( c => c.Standard == Standard && c.MaxNumStudents > c.Students.Count)
                                        .Count()
                              > 0;

            }

            return hasVacancy;

        }

    }

Test Project

Add Test class AdmissionTest.

This class uses in-memory SQLite to initialize an Object of Admission class.

SQLite connection DataSource=:memory: signifies in-memory usage

Add a test case:

1. Inserts a new SchoolClass in database
2. Checks for Vacancy in the SchoolClass
3. Expects availability to be True

Add a test case:

1. Inserts a new SchoolClass in database
2. Adds Max allowed number of students to the SchoolClass
3. Checks for Vacancy in the SchoolClass
4. Expects availability to be False

Complete Test Class https://gitlab.com/saurabhjain1537/nunit-sqlite-schoolapp/-/blob/master/SchoolApp.Tests/AdmissionTest.cs

    public class AdmissionTest
    {
        DbContextOptions<AppDBContext> configOptions = null;
        private Admission admission;

        [SetUp]
        public void Setup()
        {
            var connection = new SqliteConnection("DataSource=:memory:");

            configOptions = new DbContextOptionsBuilder<AppDBContext>().UseSqlite(connection).EnableSensitiveDataLogging().Options;

            admission = new Admission(configOptions);

        }

        [Test]
        public void hasVacancy_True()
        {
            using (var context = new AppDBContext(configOptions))
            {
                context.Database.OpenConnection();
                context.Database.EnsureCreated();

                context.SchoolClasses.Add( new SchoolClass{ Id = 1, Standard = "6", Section = "B", MaxNumStudents = 10 });

                context.SaveChanges();

            }

            var hasVacancy = admission.hasVacancy("6");

            Assert.AreEqual(true, hasVacancy);

        }

        [Test]
        public void hasVacancy_False()
        {
            using (var context = new AppDBContext(configOptions))
            {
                context.Database.OpenConnection();
                context.Database.EnsureCreated();

                var schoolClass6B = new SchoolClass{ Id = 1, Standard = "6", Section = "B", MaxNumStudents = 5 };
                context.SchoolClasses.Add(schoolClass6B );
                context.Students.Add( new Student { Id = 1, Name = "a", schoolClass = schoolClass6B});
                context.Students.Add( new Student { Id = 2, Name = "b", schoolClass = schoolClass6B});
                context.Students.Add( new Student { Id = 3, Name = "c", schoolClass = schoolClass6B});
                context.Students.Add( new Student { Id = 4, Name = "d", schoolClass = schoolClass6B});
                context.Students.Add( new Student { Id = 5, Name = "e", schoolClass = schoolClass6B});

                context.SaveChanges();

            }

            var hasVacancy = admission.hasVacancy("6");

            Assert.AreEqual(false, hasVacancy);

        }

    }

Execute Tests

Result

We have successfully created a SchoolApp to validate school Admissions and prepared repeatable test cases using SQLite in-memory database.

The complete code this Example project is available at https://gitlab.com/saurabhjain1537/nunit-sqlite-schoolapp/-/tree/master