How to unit test code with database interaction?

Question

I want to write a unit test for this code to check whether the TotalPrice is calculated correctly. I found that mocking and dependency injection is needed for that because the code interacts with database. But I can't figure out how to apply it to my code since I am new to both concepts. Can somebody help me with that?

public partial class PrintBillVM : ObservableObject
{
    [ObservableProperty]
    public double subTotal;

    [ObservableProperty]
    public double tax;

    [ObservableProperty]
    public double totalPrice;

    double taxRate = 5;

    public PrintBillVM()
    {
        using (var db = new DatabaseContext())
        {
            var orders = db.Orders_t.Include(o => o.Product).ToList();
            SubTotal = orders.Sum(i => i.Price);
            Tax = SubTotal * taxRate / 100;
            TotalPrice = SubTotal+Tax;
        }
    }
}

Unit test should be something like this.

public class UnitTest1
{
    [Fact]
    public void Calculate_TotalPrice()
    {
        var bill = new PrintBillVM();
        bill.Tax.Should().Be(5);
        bill.TotalPrice.Should().Be(105);
    }
}

Answer 1

Usually an elegant way to do this is to create an interface which is an abstraction to the database layer, say IBillRepository, and this interface has some method Get(int billId) which returns a bill object by its ID. Then you can implement this interface with a class BillRepositoryImpl where you do the actual DB call inside Get(int billId). Now, mocking is very easy - you can use Moq for example, just do something like:

Mock<IBillRepository> repository = new Mock<IBillRepository>();
repository.Setup(r => r.Get(12345)).Returns(<some_PrintBillVM_object>);

Then once you call your repository with bill ID 12345, you do the assertions on the returned <some_PrintBillVM_object>.

Answer 2

Sure, I can help you with that. Here is a simplified explanation of how to unit test code with database interaction using mocking and dependency injection:

• Mocking is a technique that allows you to create fake objects that behave like real objects. This can be useful for unit testing code that depends on external resources, such as databases.
• Dependency injection is a technique that allows you to pass objects into other objects as dependencies. This can make your code more flexible and easier to test.

To unit test code with database interaction using mocking and dependency injection, you would do the following:

1. Create a mock object for the database.
2. In the constructor of your class that interacts with the database, inject the mock object instead of the real database object.
3. In your unit test, mock the behavior of the database object. For example, you can mock the GetOrders() method to return a specific list of orders.
4. Run the unit test. The unit test should pass if the code that interacts with the database behaves as expected.

Here is an example of how to do this:

using Moq;

public class PrintBillVM
{
    private readonly IDatabaseContext _databaseContext;

    public PrintBillVM(IDatabaseContext databaseContext)
    {
        _databaseContext = databaseContext;
    }

    public void CalculateTotalPrice()
    {
        var orders = _databaseContext.GetOrders();
        SubTotal = orders.Sum(i => i.Price);
        Tax = SubTotal * taxRate / 100;
        TotalPrice = SubTotal + Tax;
    }
}

In this example, the PrintBillVM class interacts with the IDatabaseContext interface to get a list of orders. To unit test this code, we can create a mock object for the IDatabaseContext interface and inject it into the PrintBillVM constructor. Then, we can mock the behavior of the GetOrders() method to return a specific list of orders.

Here is an example of how to mock the GetOrders() method:

using Moq;

public class UnitTest1
{
    [Fact]
    public void Calculate_TotalPrice()
    {
        // Create a mock of IDatabaseContext using a mocking framework like Moq
        var mockDatabaseContext = new Mock<IDatabaseContext>();
        mockDatabaseContext.Setup(db => db.GetOrders())
            .Returns(new List<Order>()
            {
                new Order { Price = 100 },
                // Add more sample orders as needed
            });

        // Create the PrintBillVM instance with the mocked database context
        var bill = new PrintBillVM(mockDatabaseContext.Object);

        // Call the CalculateTotalPrice method
        bill.CalculateTotalPrice();

        // Perform assertions
        Assert.Equal(5, bill.Tax);
        Assert.Equal(105, bill.TotalPrice);
    }
}

In this example, we create a mock object for the IDatabaseContext interface and set up the mock to return a list of sample orders. Then, we create a PrintBillVM instance with the mocked database context. Finally, we call the CalculateTotalPrice() method and assert that the results are correct.

I hope this explanation helps you get started with unit testing your code with database interaction using mocking and dependency injection.

Answer 3

The normal Moq package is not capable to mock a DbContext, but the package Moq.EntityFrameworkCore is.

What you could do is the following.

1. You would need to change the implementation of your PrintBillVM class, so it injects the DbContext via the constructor. Otherwise you won't be able to pass the correct, mocked object to it in your unit tests. Furthermore I would recommend to build a method to calculate the total price, instead of having this information in the constructor.

public partial class PrintBillVM : ObservableObject
{
    // other properties...

    private readonly DatabaseContext _dbContext;

    public PrintBillVM(DatabaseContext dbContext)
    {
        _dbContext = dbContext;
    }

    public void Calculate()
    {
        var orders = _dbContext.Orders_t.Include(o => o.Product).ToList();
        SubTotal = orders.Sum(i => i.Price);
        Tax = SubTotal * taxRate / 100;
        TotalPrice = SubTotal+Tax;
    }
}

2. Then you can test the method like the following.

[Fact]
public void Calculate_TotalPrice()
{
    // Arrange
    
    // Create a mock DbContext
    var myDatabaseContextMock = new Mock<DatabaseContext>();

    // Create a sample list of products
    var productList = new List<Product>
    {
        new Product { Id = 1, Name = "Product 1", Price = 10.99 },
        new Product { Id = 2, Name = "Product 2", Price = 19.99 },
        // Add more products as needed
    };

    // Create a sample order and associate it with the products
    var order = new Order { Id = 1, Products = productList };

    // Setup the mock to return the sample order when querying orders
    dbContextMock
        .Setup(db => 
            db.Orders_t
            .Include(o => o.Product)
            .ToList())
        .Returns(new List<Order> { order });

    var expectedTax = productList.Sum(p => p.Price) * 5 / 100;
    var expectedTotalPrice = productList.Sum(p => p.Price) + expectedTax;

    // Pass the mocked dbContext to the constructor
    var bill = new PrintBillVM(dbContextMock.Object);

    // Act
    bill.Calculate()

    // Assert
    bill.Tax.Should().Be(expectedTax);
    bill.TotalPrice.Should().Be(expectedTotalPrice);
}

Answer 4

The first step is to make your VM testable. Your current constructor creates the DbContext and loads data directly form the database, this is problematic for unit testing because you have no direct control over the connection string that is used and therefore can only assume that the data within the database will match your test criteria. The next problem is that your class doesn't make a great deal of sense, there is no concept of a specific bill/order so your logic will actually calculate the total price and tax component across ALL Order entities in your database. From a testing point of view this again makes things hard, especially if other tests or program executions generate additional orders in the database over time. So before you worry about testing, I would first fix your PrintBillVM so that it is at least functional.

You are right, using DI to inject the database connection will help to make this testable, that is so that we can guarantee the connection and the state of the database as part of the test, before we instantiate an instance of the PrintBillVM class.

It is NOT usually a good idea to Mock a DbContext because your test will then not be testing the implementation of the database interactions at all, mocking allows you to effectively exclude the database itself from the test scenario, instead we can only test that certain functions and properties on the mocked object are called at all.

Mocking requires you to have intimate knowledge of the implementation of a class and the method that you are testing. In DbContext there are multiple methods and properties that can be used to find and retrieve data, but also to write data back to the DbContext. If you are mocking the specific endpoints on the DbContext that you know are being used in the method you are testing, then you have failed the first rule of Unit testing! You should be testing the inputs and output of the target, the purpose is specifically for the test to be de-coupled, that is, not to be tightly coupled with the implementation, otherwise you will be forced to continually change the test any time the implementation is modified.

To further enhance testability, you should also try to avoid executing business logic inside constructors. This is mainly because failure to construct the instance means that we will have no instance to inspect to determine the reason for failure, the only information available will be in the exception and that will require you to handle the exception in the calling logic, which leads to a number of anti-patterns. Constructors should setup any immutable, invariant or default states for the properties on the instance, and nothing more. You especially want to avoid any I/O operations (Disk access, HTTP, Serial Communications...) that might raise transient errors that may need to be retried, you can't retry a constructor, doing so can only be done by creating an entirely new instance. Just keep your code cleaner by keeping constructors simple. Database interactions within a constructor are one of the strongest red flags you will come across.

• How much work should be done in a constructor?
• Find out if constructor executed successfully or not
  (This is a java discussion but the concepts and general arguments apply equally to C#)

So let's modify the class to allow you to inject the DbContext and to load the data in a separate discrete step.

public partial class PrintBillVM : ObservableObject
{
    [ObservableProperty]
    public double subTotal;

    [ObservableProperty]
    public double tax;

    [ObservableProperty]
    public double totalPrice;

    double taxRate = 5;

    private readonly DatabaseContext _dbContext;

    public PrintBillVM(DatabaseContext dbContext)
    {
        _dbContext = dbContext;
    }

    public LoadPriceInfo()
    {
        // TODO: consider loading for specific Order Ids, not for all
        var orders = db.Orders_t.ToList();
        SubTotal = orders.Sum(i => i.Price);
        Tax = SubTotal * taxRate / 100;
        TotalPrice = SubTotal+Tax;
    }

}

Removed .Include() from this example, it serves no purpose in this logic.

If you really want to ensure that I/O or otherwise business logic is executed when a class is instantiated, knowing that it might fail, then the Factory pattern should be implemented, then the factory method can deal with potential failures that we should not try to do in the constructor.

We need to recognize that testing database interactions is fundamentally flawed as Unit Tests. To do it properly would require the database to be cleared and seeded with "known" data between tests but even then, this should be treated as Integration Tests because the database is it's own isolated external system.

In the .Net stack, the best practise method to unit test database interactions is by encapsulating the logic using a repository pattern around your data context. Testing without your production database system. Your PrintBillVM might be the repository, but it is more common for the repo to be the factory that returns instances of PrintBillVM, the repo itself shouldn't have State information, and PrintBillVM is a representation of state.

The problem with doing this however is that going down the repo rabbit hole with EF/DbContext is that it generally adds performance overheads and functional restrictions to your overall solution, effectively masking out many of the benefits to using DbContext at all.

There is a solution to avoiding the Repository pattern specifically for testing, and that is to use an In-Memory database context. There are In-Memory providers for many of the support RDBMS, but in all cases not all of the functionality to the physical database are replicated or supported by the in-memory provider. So while it can be used to test simple CRUD scenarios, it is not a useful testing solution for more complex applications.

To test using an In-Memory provider might look like this, adapted from the official example docs: https://github.com/dotnet/EntityFramework.Docs/blob/main/samples/core/Testing/TestingWithoutTheDatabase/InMemoryBloggingControllerTest.cs#L10

    [Fact]
    public void Calculate_TotalPrice()
    {
        using (var context = CreateContext())
        {
            var bill = new PrintBillVM(context);
            bill.LoadPriceInfo();

            // assertions
            bill.Tax.Should().Be(5);
            bill.TotalPrice.Should().Be(105);
        }
    }

Test class in full:

using System.Linq;
using EF.Testing.BloggingWebApi.Controllers;
using EF.Testing.BusinessLogic;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;
using Xunit;

namespace EF.Testing.UnitTests;

public class BillUnitTest
{
    private readonly DbContextOptions<BillContext> _contextOptions;

    #region Constructor
    public BillUnitTest()
    {
        _contextOptions = new DbContextOptionsBuilder<BillContext>()
            .UseInMemoryDatabase("BillUnitTest")
            .ConfigureWarnings(b => b.Ignore(InMemoryEventId.TransactionIgnoredWarning))
            .Options;

        using var context = new BillContext(_contextOptions);

        context.Database.EnsureDeleted();
        context.Database.EnsureCreated();

        context.AddRange(
            new Order { Id = 1, Price = 25.00 },
            new Order { Id = 2, Price = 75.00 }
        );

        context.SaveChanges();
    }
    #endregion

    [Fact]
    public void Calculate_TotalPrice()
    {
        using (var context = CreateContext())
        {
            var bill = new PrintBillVM(context);
            bill.LoadPriceInfo();
            // assertions
            bill.Tax.Should().Be(5);
            bill.TotalPrice.Should().Be(105);
        }
    }

    BillContext CreateContext() => new BillContext(_contextOptions, (context, modelBuilder) =>
    {
        #region ToInMemoryQuery
        ...
        #endregion
    });
}

Designing an effective strategy for testing database applications is itself a major topic on its own, writing code that can be tested is an artform itself. I hope that this steers you towards some other resources that will help you on this journey. There are pros and cons to everything, keep testability front of mind when you design your business logic and try to de-couple the logic itself from the DTOs to keep your options open for later test implementations.