The Fastest Way To Mock a Database for Unit Testing

Developers have always been trying to mimic a production database. Here is a great (and fast) way to unit test your Entity Framework entities while using maximum flexibility.

One thing that always keeps nagging at me with unit tests is the problem of simulating the database without actually hitting the database. Of course, you can hit the database during your unit tests to make sure everything works, but then they are called integration tests.

So how can you simulate a database as close as possible in your unit tests without hitting a database?

Also, what happens when you have deep objects that need testing? Your customer has an order, the order has details, and your details have a product. You need to create three nested objects inside of your customer object just to test whether a product is available or not. Imagine writing that fake.

Throw in Entity Framework to the mix and you have an interesting issue. While Entity Framework helps you with the development of your models, it can also be a problem mocking up the DbContext, DbSets, and repositories for all of your tables.

Today, I will show you a better way to come as close to a production database as possible...all through unit tests.

Throw It Into Reverse!

For our example today, we will be using the Northwind database for our "production data." It's a complete schema with all of the trimmings, a load of data, and a little bit older sample database (I know there is the updated AdventureWorks database, but I'm a little nostalgia). ;-)

Once that's imported (or created) in your SQL Server, we now focus on Entity Framework.

POCO LOCO!

As mentioned before in my ludicrous speed post, I like to speed up my EF development with the Entity Framework Reverse Engineer Code First POCO Generator (Phew! That's a mouthful).

After we install the plugin, the instructions can be found on that extension page on how to use it or I can refer you to the link above ("ludicrous speed") to understand how to use it. Either way, when you are done, you should have a long list of entities from your database.

Once that's done, we need to modify the T4 template for our needs. Here are the steps to modify the template:

1. Open the EF.Reverse.POCO.Core.ttinclude in your favorite editor.
2. Go to the "AddReverseNavigation" method (~lines 2530-2555 for version 2.1.4.3)
3. In the Relationship.ManyToOne and Relationship.ManyToMany enumerated types, change the type from "virtual ICollection" to "virtual Collection" We need a concrete class for what we're about to do.
4. (UPDATE: I forgot this step) For each navigation property you want deserialized, add the XmlElement Attribute with the name of element in your XML. For example, the customer object has an order navigation property. So you're code in the customer object would look like this:
   

   [XmlElement("Order")]
   public virtual Collection<Order> Orders { get; set; }
   

5. Save the file.

Now that you have your entities created we need to create our data.

There is only one structure we can provide that will give us the hierarchical data we need for our entity framework objects: XML.

But how do we populate our objects using the XML? Serialization! Or actually...Deserialization.

This is why we needed the T4 template to generate concrete classes as opposed to interfaces. When you deserialize the XML into an object, it will traverse through your entire entity framework objects and error every time it encounters the interface.

Retrieving the Data

So now, how do we create XML hierachical data for our unit tests?

That's where we move over to SQL Server. SQL Server has this snazzy feature of spitting out XML from your select statements.

So let's make the data for our simple tables. We'll start with the Customers and Orders table.

In Query Analyzer, when I type in:

SELECT
    Customer.CustomerID,
    Customer.CompanyName,
    Customer.ContactName,
    Customer.ContactTitle,
    Customer.Address,
    Customer.City,
    Customer.Region,
    Customer.PostalCode,
    Customer.Country,
    Customer.Phone,
    Customer.Fax
FROM Customers [Customer]
WHERE Customer.Region = 'WA'
FOR xml AUTO, ROOT ('ArrayOfCustomer'), ELEMENTS

If you notice, I also have a root of ArrayOfCustomer. Since this is just data for a unit test, I need a container for this data and the default for the deserialization to work is 'ArrayOf<type>'.

When I execute that SQL, I get the following XML:

<ArrayOfCustomer>
  <Customer>
    <CustomerID>LAZYK</CustomerID>
    <CompanyName>Lazy K Kountry Store</CompanyName>
    <ContactName>John Steel</ContactName>
    <ContactTitle>Marketing Manager</ContactTitle>
    <Address>12 Orchestra Terrace</Address>
    <City>Walla Walla</City>
    <Region>WA</Region>
    <PostalCode>99362</PostalCode>
    <Country>USA</Country>
    <Phone>(509) 555-7969</Phone>
    <Fax>(509) 555-6221</Fax>
  </Customer>
  <Customer>
    <CustomerID>TRAIH</CustomerID>
    <CompanyName>Trail's Head Gourmet Provisioners</CompanyName>
    <ContactName>Helvetius Nagy</ContactName>
    <ContactTitle>Sales Associate</ContactTitle>
    <Address>722 DaVinci Blvd.</Address>
    <City>Kirkland</City>
    <Region>WA</Region>
    <PostalCode>98034</PostalCode>
    <Country>USA</Country>
    <Phone>(206) 555-8257</Phone>
    <Fax>(206) 555-2174</Fax>
  </Customer>
  <Customer>
    <CustomerID>WHITC</CustomerID>
    <CompanyName>White Clover Markets</CompanyName>
    <ContactName>Karl Jablonski</ContactName>
    <ContactTitle>Owner</ContactTitle>
    <Address>305 - 14th Ave. S. Suite 3B</Address>
    <City>Seattle</City>
    <Region>WA</Region>
    <PostalCode>98128</PostalCode>
    <Country>USA</Country>
    <Phone>(206) 555-4112</Phone>
    <Fax>(206) 555-4115</Fax>
  </Customer>
</ArrayOfCustomer>

I kept it to a region of Washington ('WA') because there are a LOT of customers in this database.

Now we have our records. Perform the same process on the tables that you want for your unit tests.

Finally!...Unit Tests!

With our data in hand, we can start working on our unit tests.

Here is how we integrate our data into our unit tests:

1. In your unit test project, Add a Resources folder.
2. In that Resources folder, create a Resource file. I called mine NorthwindData.resx.
3. In that Resource file, I added an entry name called CustomerData and pasted the Customer XML into the Value column.
4. Repeat this for each table that you want to unit test.

I am using Moq for these unit tests.

[TestClass]
public class DbContextTests
{
    private IEnumerable<Customer> customers { get; set; }

    [TestInitialize]
    public void Setup()
    {
        var stream = NorthwindData.CustomerData.ToStream();

        var reader = new StreamReader(stream);
        var serializer = new XmlSerializer(typeof(Collection<Customer>));
        customers = (Collection<Customer>)serializer.Deserialize(reader);
    }

    [TestMethod]
    public void ConfirmDbContextUsesDummyData()
    {
        // Arrange
        var mockContext = new Mock<INorthwindContext>();

        // Populate Customers "table"
        var customerDbSet = customers.GetQueryableMockDbSet();
        foreach (var customer in customers)
        {
            customerDbSet.Add(customer);
        }

        mockContext.Setup(context => context.Customers).Returns(customerDbSet);
        var dbContext = mockContext.Object;

        // Act
        var customerData = dbContext.Customers;

        // Assert
        Assert.IsTrue(customerData.Count() == 3);
    }

    [TestMethod]
    public void ConfirmThatRepositoryUsesFakeCustomerData()
    {
        // Arrange
        var mockContext = new Mock<INorthwindContext>();

        // Populate Customers "table"
        var customerDbSet = customers.GetQueryableMockDbSet();
        foreach (var customer in customers)
        {
            customerDbSet.Add(customer);
        }

        mockContext.Setup(context => context.Customers).Returns(customerDbSet);
        var dbContext = mockContext.Object;

        var mockRepository = new Mock<ICustomerRepository>();
        mockRepository.Setup(e => e.GetAll()).Returns(dbContext.Customers);
        var customerRepository = mockRepository.Object;

        // Act
        var records = customerRepository.GetAll();

        // Assert
        Assert.IsTrue(records.Count() == 3);
    }
}

There was also a DbSetExtensions extension method to convert an IEnumerable into a DbSet for us.

public static class DbSetExtensions
{
    public static DbSet<T> GetQueryableMockDbSet<T>(this IEnumerable<T> sourceList) where T : class
    {
        var queryable = sourceList.AsQueryable();

        var dbSet = new Mock<DbSet<T>>();
        dbSet.As<IQueryable<T>>().Setup(m => m.Provider).Returns(queryable.Provider);
        dbSet.As<IQueryable<T>>().Setup(m => m.Expression).Returns(queryable.Expression);
        dbSet.As<IQueryable<T>>().Setup(m => m.ElementType).Returns(queryable.ElementType);
        dbSet.As<IQueryable<T>>().Setup(m => m.GetEnumerator()).Returns(queryable.GetEnumerator());

        return dbSet.Object;
    }
}

The INorthwindContext was created along with our entities in the T4 generator so that was a bonus for mocking up our DbContext.

Everything passes and we're in business.

That's all for today! Good night, folks! Be sure to tip your waitress!

But Wait...There's More!

Still here, huh?

Wanting a little more?

Ok. Let's revisit the first problem we explained in the beginning. How do we test objects from all levels throughout an object hierarchy all the way down to the lower depths of the nested tree.

Well, since our last example uses one table to generate a single XML file for customers, I'm pretty sure we can create a new XML source to include more data to make our unit test complete.

If I bring up the Northwind database and in SQL Server's Query Analyzer, I type:

SELECT
    Customer.CustomerID,
    Customer.CompanyName,
    Customer.ContactName,
    Customer.ContactTitle,
    Customer.Address,
    Customer.City,
    Customer.Region,
    Customer.PostalCode,
    Customer.Country,
    Customer.Phone,
    Customer.Fax,
    [Order].OrderID,
    [Order].CustomerID,
    [Order].EmployeeID,
    [Order].OrderDate,
    [Order].RequiredDate,
    [Order].ShippedDate,
    [Order].ShipVia,
    [Order].Freight,
    [Order].ShipName,
    [Order].ShipAddress,
    [Order].ShipCity,
    [Order].ShipRegion,
    [Order].ShipPostalCode,
    [Order].ShipCountry,
    OrderDetail.OrderID,
    OrderDetail.ProductID,
    OrderDetail.UnitPrice,
    OrderDetail.Quantity,
    OrderDetail.Discount,
    Product.ProductID,
    Product.ProductName,
    Product.SupplierID,
    Product.CategoryID,
    Product.QuantityPerUnit,
    Product.UnitPrice,
    Product.UnitsInStock,
    Product.UnitsOnOrder,
    Product.ReorderLevel,
    Product.Discontinued
FROM Customers [Customer]
INNER JOIN Orders [Order]
    ON Customer.CustomerID = [Order].CustomerID
INNER JOIN [Order Details] [OrderDetail]
    ON [Order].OrderID = OrderDetail.OrderID
INNER JOIN [Products] [Product]
    ON OrderDetail.ProductID = Product.ProductID
WHERE Customer.Region = 'WA'
FOR xml AUTO, ROOT ('ArrayOfCustomer'), ELEMENTS

I receive the following XML (shortened because of space):

<ArrayOfCustomer>
  <Customer>
    <CustomerID>LAZYK</CustomerID>
    <CompanyName>Lazy K Kountry Store</CompanyName>
    <ContactName>John Steel</ContactName>
    <ContactTitle>Marketing Manager</ContactTitle>
    <Address>12 Orchestra Terrace</Address>
    <City>Walla Walla</City>
    <Region>WA</Region>
    <PostalCode>99362</PostalCode>
    <Country>USA</Country>
    <Phone>(509) 555-7969</Phone>
    <Fax>(509) 555-6221</Fax>
    <Order>
      <OrderID>10482</OrderID>
      <CustomerID>LAZYK</CustomerID>
      <EmployeeID>1</EmployeeID>
      <OrderDate>1997-03-21T00:00:00</OrderDate>
      <RequiredDate>1997-04-18T00:00:00</RequiredDate>
      <ShippedDate>1997-04-10T00:00:00</ShippedDate>
      <ShipVia>3</ShipVia>
      <Freight>7.4800</Freight>
      <ShipName>Lazy K Kountry Store</ShipName>
      <ShipAddress>12 Orchestra Terrace</ShipAddress>
      <ShipCity>Walla Walla</ShipCity>
      <ShipRegion>WA</ShipRegion>
      <ShipPostalCode>99362</ShipPostalCode>
      <ShipCountry>USA</ShipCountry>
      <OrderDetail>
        <OrderID>10482</OrderID>
        <ProductID>40</ProductID>
        <UnitPrice>14.7000</UnitPrice>
        <Quantity>10</Quantity>
        <Discount>0.0000000e+000</Discount>
        <Product>
          <ProductID>40</ProductID>
          <ProductName>Boston Crab Meat</ProductName>
          <SupplierID>19</SupplierID>
          <CategoryID>8</CategoryID>
          <QuantityPerUnit>24 - 4 oz tins</QuantityPerUnit>
          <UnitPrice>18.4000</UnitPrice>
          <UnitsInStock>123</UnitsInStock>
          <UnitsOnOrder>0</UnitsOnOrder>
          <ReorderLevel>30</ReorderLevel>
          <Discontinued>0</Discontinued>
        </Product>
      </OrderDetail>
    </Order>
    <Order>
      <OrderID>10545</OrderID>
      <CustomerID>LAZYK</CustomerID>
      <EmployeeID>8</EmployeeID>
      <OrderDate>1997-05-22T00:00:00</OrderDate>
      <RequiredDate>1997-06-19T00:00:00</RequiredDate>
      <ShippedDate>1997-06-26T00:00:00</ShippedDate>
      <ShipVia>2</ShipVia>
      <Freight>11.9200</Freight>
      <ShipName>Lazy K Kountry Store</ShipName>
      <ShipAddress>12 Orchestra Terrace</ShipAddress>
      <ShipCity>Walla Walla</ShipCity>
      <ShipRegion>WA</ShipRegion>
      <ShipPostalCode>99362</ShipPostalCode>
      <ShipCountry>USA</ShipCountry>
      <OrderDetail>
        <OrderID>10545</OrderID>
        <ProductID>11</ProductID>
        <UnitPrice>21.0000</UnitPrice>
        <Quantity>10</Quantity>
        <Discount>0.0000000e+000</Discount>
        <Product>
          <ProductID>11</ProductID>
          <ProductName>Queso Cabrales</ProductName>
          <SupplierID>5</SupplierID>
          <CategoryID>4</CategoryID>
          <QuantityPerUnit>1 kg pkg.</QuantityPerUnit>
          <UnitPrice>21.0000</UnitPrice>
          <UnitsInStock>22</UnitsInStock>
          <UnitsOnOrder>30</UnitsOnOrder>
          <ReorderLevel>30</ReorderLevel>
          <Discontinued>0</Discontinued>
        </Product>
      </OrderDetail>
    </Order>
  </Customer>
  <Customer>
    <CustomerID>TRAIH</CustomerID>
    <CompanyName>Trail's Head Gourmet Provisioners</CompanyName>
    <ContactName>Helvetius Nagy</ContactName>
    <ContactTitle>Sales Associate</ContactTitle>
    <Address>722 DaVinci Blvd.</Address>
    <City>Kirkland</City>
    <Region>WA</Region>
    <PostalCode>98034</PostalCode>
    <Country>USA</Country>
    <Phone>(206) 555-8257</Phone>
    <Fax>(206) 555-2174</Fax>
    <Order>
      <OrderID>10574</OrderID>
      <CustomerID>TRAIH</CustomerID>
      <EmployeeID>4</EmployeeID>

etc., etc., etc.

We can see that based on our query, we have the one customer who has 2 orders, each order has 1 order detail a piece, and each one has a product.

Now we have our hierarchical data for testing. A couple of notes before we proceed:

• As you can imagine, you can get a TON of XML returning from SQL Server. Try to minimize the amount of data returned to make your unit tests pass. I'll show you in a minute why you should keep it small.
• The great part about this is that you can run your unit tests under any number of specific conditions. Modify the XML for your unit test and you're off and running.
• The aliases make a difference when outputting your XML through SQL Server. Adjust the aliases to get your proper names relating to your entity framework classes.
• Since Entity Framework uses relationships when creating the code first entities, make sure they are defined in your tables before you generate the entities. Otherwise, you won't be able to perform your serialization.

Our unit test will be exactly what you would expect. If we create a new entry in our Resource file called CustomerOrderData, we can run the following Unit Test with a pass.

[TestMethod]
public void ConfirmDeserializationOfBusinessObjects()
{
    // Arrange
    var stream = NorthwindData.CustomerOrderData.ToStream();
    StreamReader reader = new StreamReader(stream);
    XmlSerializer serializer = new XmlSerializer(typeof(Collection<Customer>));
    // Act
    var customerOrderData = (Collection<Customer>)serializer.Deserialize(reader);
    // Assert
    Assert.IsNotNull(customerOrderData);
    Assert.IsTrue(customerOrderData.Count == 3);
    Assert.IsTrue(customerOrderData[0].Orders.Count == 2);
    Assert.IsTrue(customerOrderData[0].Orders[0].OrderDetails.Count == 1);
    Assert.IsFalse(customerOrderData[0].Orders[0].OrderDetails[0].Product.Discontinued);
}

Conclusion

With this technique, you can easily make changes to your unit test data and run experiments on the XML and your business rules. This is will make your Entity Framework Unit Tests more believable with "real" data.

How many of you have had an issue with production where it works on staging, but doesn't work on production...

...and the source code is the same in both environments. :-\

This is one example of being able to take data from a SQL Server (with the query above) from production and immediately place it on your staging server with your code and run it against staging to find out where your issue is and how you can resolve it.

In the long run, this makes your unit tests even more valuable to you.

I hope you enjoyed this technique. Have you implemented something like this before? Post your comments below.