[c#] How are people unit testing with Entity Framework 6, should you bother?

In order to unit test code that relies on your database you need to setup a database or mock for each and every test.

1. Having a database (real or mocked) with a single state for all your tests will bite you quickly; you cannot test all records are valid and some aren't from the same data.
2. Setting up an in-memory database in a OneTimeSetup will have issues where the old database is not cleared down before the next test starts up. This will show as tests working when you run them individually, but failing when you run them all.
3. A Unit test should ideally only set what affects the test

I am working in an application that has a lot of tables with a lot of connections and some massive Linq blocks. These need testing. A simple grouping missed, or a join that results in more than 1 row will affect results.

To deal with this I have setup a heavy Unit Test Helper that is a lot of work to setup, but enables us to reliably mock the database in any state, and running 48 tests against 55 interconnected tables, with the entire database setup 48 times takes 4.7 seconds.

Here's how:

1. In the Db context class ensure each table class is set to virtual

   public virtual DbSet<Branch> Branches { get; set; }
   public virtual DbSet<Warehouse> Warehouses { get; set; }
   

2. In a UnitTestHelper class create a method to setup your database. Each table class is an optional parameter. If not supplied, it will be created through a Make method

   internal static Db Bootstrap(bool onlyMockPassedTables = false, List<Branch> branches = null, List<Products> products = null, List<Warehouses> warehouses = null)
   {
       if (onlyMockPassedTables == false) {
           branches ??= new List<Branch> { MakeBranch() };
           warehouses ??= new List<Warehouse>{ MakeWarehouse() };
       }
   

3. For each table class, each object in it is mapped to the other lists

       branches?.ForEach(b => {
           b.Warehouse = warehouses.FirstOrDefault(w => w.ID == b.WarehouseID);
       });
   
       warehouses?.ForEach(w => {
           w.Branches = branches.Where(b => b.WarehouseID == w.ID);
       });
   

4. And add it to the DbContext

        var context = new Db(new DbContextOptionsBuilder<Db>().UseInMemoryDatabase(Guid.NewGuid().ToString()).Options);
        context.Branches.AddRange(branches);
        context.Warehouses.AddRange(warehouses);
        context.SaveChanges();
        return context;
    }
   

5. Define a list of IDs to make is easier to reuse them and make sure joins are valid

    internal const int BranchID = 1;
    internal const int WarehouseID = 2;
   

6. Create a Make for each table to setup the most basic, but connected version it can be

    internal static Branch MakeBranch(int id = BranchID, string code = "The branch", int warehouseId = WarehouseID) => new Branch { ID = id, Code = code, WarehouseID = warehouseId };
    internal static Warehouse MakeWarehouse(int id = WarehouseID, string code = "B", string name = "My Big Warehouse") => new Warehouse { ID = id, Code = code, Name = name };
   

It's a lot of work, but it only needs doing once, and then your tests can be very focused because the rest of the database will be setup for it.

[Test]
[TestCase(new string [] {"ABC", "DEF"}, "ABC", ExpectedResult = 1)]
[TestCase(new string [] {"ABC", "BCD"}, "BC", ExpectedResult = 2)]
[TestCase(new string [] {"ABC"}, "EF", ExpectedResult = 0)]
[TestCase(new string[] { "ABC", "DEF" }, "abc", ExpectedResult = 1)]
public int Given_SearchingForBranchByName_Then_ReturnCount(string[] codesInDatabase, string searchString)
{
    // Arrange
    var branches = codesInDatabase.Select(x => UnitTestHelpers.MakeBranch(code: $"qqqq{x}qqq")).ToList();
    var db = UnitTestHelpers.Bootstrap(branches: branches);
    var service = new BranchService(db);

    // Act
    var result = service.SearchByName(searchString);

    // Assert
    return result.Count();
}