EF-6 Unit testing (Part 2)

Introduction

I’ve covered some unit testing in a previous blog post. This time I want to expand on my earlier post and show how to mock two or more tables. I will also demonstrate a weakness I discovered in mocking a database using the mock and a method to get around it.

Two or more Tables

You might remember this code:

using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Moq;

namespace DatabaseTestConsole
{
    [TestClass]
    public class UnitTests
    {
        [TestMethod]
        public void TestQuery()
        {
            var data = new List<account>
            {
                new account { username = "test",pass="testpass1" },
                new account { username = "ZZZ",pass="testpass2" },
                new account { username = "AAA",pass="testpass3" },
            }.AsQueryable();

            var mockSet = new Mock<DbSet<account>>();
            mockSet.As<IQueryable<account>>().Setup(m => m.Provider)
                   .Returns(data.Provider);
            mockSet.As<IQueryable<account>>().Setup(m => m.Expression)
                   .Returns(data.Expression);
            mockSet.As<IQueryable<account>>().Setup(m => m.ElementType)
                   .Returns(data.ElementType);
            mockSet.As<IQueryable<account>>().Setup(m => m.GetEnumerator())
                   .Returns(data.GetEnumerator());

            var mockContext = new Mock<DatabaseContext>();
            mockContext.Setup(c => c.accounts).Returns(mockSet.Object);

            UserRights rights = new UserRights(mockContext.Object);

            Assert.AreEqual("testpass1", rights.LookupPassword("test"),
                  "password for account test is incorrect");
            Assert.AreEqual("testpass2", rights.LookupPassword("ZZZ"),
                  "password for account ZZZ is incorrect");
            Assert.AreEqual("testpass3", rights.LookupPassword("AAA"),
                  "password for account AAA is incorrect");
        }
    }
}

I used it to demonstrate how to mock a table and unit test it. Now I’m going to add a table to the mix. First, I’m going to add an object that uses two tables (departments and accounts). Then I’ll show how to mock the database that it uses:

public class PersonnelPerDepartment
{
    private DepartmentContext _DeptContext;

    public PersonnelPerDepartment(DepartmentContext deptContext)
    {
        _DeptContext = deptContext;
    }

    public int TotalPersonnel()
    {
        var personnelDeptQuery = (
            from d in _DeptContext.departments
            join p in _DeptContext.people on d.id equals p.department
            select p).ToList();

        return personnelDeptQuery.Count();
    }
}

I specifically setup the object to require two tables to query from. Then I altered the unit test code as thus:

[TestMethod]
public void TestTwoTables()
{
    var deptData = new List<department>
    {
        new department {id=1, name="Operations"},
        new department {id=2, name="Sales"}
    }.AsQueryable();
 
    var deptMockSet = new Mock<DbSet<department>>();
    deptMockSet.As<IQueryable<department>>().Setup(m => m.Provider)
        .Returns(deptData.Provider);
    deptMockSet.As<IQueryable<department>>().Setup(m => m.Expression)
        .Returns(deptData.Expression);
    deptMockSet.As<IQueryable<department>>().Setup(m => m.ElementType)
        .Returns(deptData.ElementType);
    deptMockSet.As<IQueryable<department>>().Setup(m => m.GetEnumerator())
        .Returns(deptData.GetEnumerator());
 
    // department table
    var persData = new List<person>
    {
        new person {id=1, first="Joe",last="Smith",department=1},
        new person {id=2, first="Jane", last="Summers",department=1},
        new person {id=2, first="Bob", last="Anders",department=1},
    }.AsQueryable();
 
    var personMockSet = new Mock<DbSet<person>>();
    personMockSet.As<IQueryable<person>>().Setup(m => m.Provider)
        .Returns(persData.Provider);
    personMockSet.As<IQueryable<person>>().Setup(m => m.Expression)
        .Returns(persData.Expression);
    personMockSet.As<IQueryable<person>>().Setup(m => m.ElementType)
        .Returns(persData.ElementType);
    personMockSet.As<IQueryable<person>>().Setup(m => m.GetEnumerator())
        .Returns(persData.GetEnumerator());
 
    var mockContext = new Mock<DepartmentContext>();
    mockContext.Setup(c => c.departments).Returns(deptMockSet.Object);
    mockContext.Setup(c => c.people).Returns(personMockSet.Object);
 
    PersonnelPerDepartment persDept = new PersonnelPerDepartment(mockContext.Object);
    int total = persDept.TotalPersonnel();
 
    Assert.AreEqual(3, total);
}

Each table will need it’s own mockSet, which I admit, is ugly. The mockContext has two setups, one for each table (and you can add more tables to the context as needed). Now the test runs and returns the three personnel listd in the person table. You can experiment with different data sets and queries to test this for yourself. I wanted to give an example of how the code would look for two tables.

I’ve been writing unit tests for a real application that I’m working on for DealerOn. This application has dozens of tables and, as you might have guessed, this method gets lengthy. Of course, you can setup all this stuff in an initialization method before running your tests, but there are other problems. One problem I discovered was that you cannot add to your tables in your methods under test. This mock object doesn’t support adding or deleting records. For that, we need a different technique.

Using Test Doubles

This article at Microsoft (Testing with your own test doubles (EF6 onwards)) demonstrates how to use test doubles with EF-6.  The advantages to using this technique is that you can add and delete from your tables and the unit testing code is more compact (most of the code is in the fake object).  

How to do it

First, create a solution with a console application project and and a test project. Make sure you include your console app in your references inside your unit test project and include your using statement so you can unit test objects in your console application.

Next create an EF-6 edmx file in your console application and add your tables (my example uses my demo database that I’ve been using for quite a few blog posts. This MS SQL server sample data has a department and person table in it).

Go to the link above (testing doubles) and copy the code under the header “Creating the in-memory test doubles”, then paste it into it’s own cs file in your unit testing project. You’ll need to change the top class to match your EF-6 database:

public class TestContext : ISampleDataContext
{
    public TestContext()
    {
        this.people = new TestDbSet<person>();
        this.departments = new TestDbSet<department>();
    }
 
    public DbSet<person> people { get; set; }
    public DbSet<department> departments { get; set; }
    public int SaveChangesCount { get; private set; }
    public int SaveChanges()
    {
        this.SaveChangesCount++;
        return 1;
    }
}

You’ll need to add a bunch of usings to make the errors go away. You should end up with these:

using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Linq;
using System.Linq.Expressions;
using System.Threading;
using System.Threading.Tasks;
using EF6UnitTestingUsingDoubles; //your console project

In your console project, create an interface.  It’ll look something like this:

using System.Data.Entity;
 
namespace EF6UnitTestingUsingDoubles
{
    public interface ISampleDataContext
    {
        DbSet<person> people { get; set; }
        DbSet<department> departments { get; set; }
        int SaveChanges();
    }
}

You’ll need to add this interface to your EF context.cs file constructor:

public partial class sampledataEntities : DbContext, ISampleDataContext
{
    public sampledataEntities()
        : base("name=sampledataEntities")
    {
    }   
}

It’s best to put the “ISampleDataContext” (and the comma) in the T4 (“tt”) script file, so auto-generated code will not wipe out your changes:

<#=Accessibility.ForType(container)#> partial class <#=code.Escape(container)#> : DbContext, ISampleDataContext

Next create your object in the main console app:

using System.Linq;
 
namespace EF6UnitTestingUsingDoubles
{
    public class PersonnelPerDepartment
    {
        private ISampleDataContext _DeptContext;
 
        public PersonnelPerDepartment(ISampleDataContext deptContext)
        {
            _DeptContext = deptContext;
        }
 
        public int TotalPersonnel()
        {
            var personnelDeptQuery = (
                from d in _DeptContext.departments
                join p in _DeptContext.people on d.id equals p.department
                select p).ToList();
 
            return personnelDeptQuery.Count();
        }
    }
}

One thing to note: You will be passing the interface definition to your classes instead of the Entity Framework context object like normal. When your program executes normally, it will use the interface just like the fake context does when performing your unit tests.

Last, but not least, your unit test:

[TestMethod]
public void TestMethod1()
{
    var context = new TestContext();
 
    var deptTable = new department()
    {
        id=1,
        name="Sales"
    };
    context.departments.Add(deptTable);
    context.SaveChanges();
 
    var personTable = new person()
    {
        id=1,
        first = "Joe",
        last = "Smith",
        department = 1
    };
    context.people.Add(personTable);
    personTable = new person() 
    { 
        id = 2, 
        first = "Jane", 
        last = "Summers", 
        department = 1 
    };
    context.people.Add(personTable);
    context.SaveChanges();
 
    PersonnelPerDepartment persDept = new PersonnelPerDepartment(context);
    int total = persDept.TotalPersonnel();
 
    Assert.AreEqual(2, total);
}

Notice how much cleaner the test method is. The first line creates a test context, then one record is added to the department table. Next two records are added to the person table and that leaves the remaining lines to call the method under test (persDept) and do an assert. The fake context object and the interface should be setup to contain all the tables in your context. You will then be able to add data to your tables inside your unit tests as needed. Your methods under test can perform queries on all the tables in the context without an issue when under tests.

Downloadable Sample Project

You can download the entire sample project here: EF6 Unit Testing Doubles Project. Be aware that this project does not include the database and you’ll need to alter the connection to your database and possibly manipulate the edmx file to make it work with your database. If you have an MS SQL server setup on your PC, then you can use these two scripts to generate the tables used in this sample (after creating a database called “sampledata”):

USE [sampledata]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[person](
[id] [int] IDENTITY(1,1) NOT NULL,
[first] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[last] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[department] [int] NOT NULL,
 CONSTRAINT [PK_person] PRIMARY KEY CLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF
USE [sampledata]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[department](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_department] PRIMARY KEY CLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF