Entity Framework unit testing with mocked data and auto cleanup of data
Overview
This article’s intent is to demonstrate unit testing against SQL-Server database table using Entity Framework 6 or higher rather than using a mocking framework. By working with real data retrieved from a database you can get an idea for performance besides testing your data operations. The downsides to working against live data are, setting up for each test and restoring the database tables to pre-test state and secondly execution time to run these tests.
Entity Framework Core in-memory testing
With Entity Framework Core unit testing can be done with what is known as in memory testing. See the following GitHub repository for a sample using Entity Framework Core 2x with Microsoft SQL-Server and SQLLite.
Setup
Create a test environment on your SQL-Server instance as this should not be done on your production server as if something goes wrong you have not contaminated production data.
For setup of data create a test base class which each unit test class uses. In the test base subscribe to TestInitialize which is a method that runs before a test. In this case, a list of objects is created which will be used to keep track of class entities created which will be destroyed in TestCleanup method of the unit test class method.
Core methods
Each method shown below are generic which means they will work against any classes in your Entity Framework model. Later on in the section Underlying mechanics, you will see that an Interface is required to permit destruction of objects stored in a list which in turn removes records created for unit test methods to be removed from the backend database.
There are three methods which are important to understand, GetSandboxEntities which allows a developer to get data from the list created in TestInitialize.
protected IEnumerable<T> GetSandboxEntities<T>()
{
var returnObject = (
from item in AnnihilationList
where item.GetType() == typeof(T)
select (T)item);
return returnObject;
}
If in our test base class several Customer items were added to the list of objects they can be returned as follows
IEnumerable<Customer> customers = GetSandboxEntities<Customer>();
If there were Product instead of Customer
IEnumerable<Product> customers = GetSandboxEntities<Product>();
To add items to the database, create a method which creates several entities and save them to the database.
public class CustomerCreateData : TestBase
{
public void CreateMockData()
{
AddSandboxEntities(CreateCustomers());
DbContext.SaveChanges();
}
public List<Customer> CreateCustomers()
{
var customerList = new List<Customer>
{
new Customer
{
CompanyName = "Alfreds Futterkiste",
ContactName = "Maria Anders",
ContactTitle = "Sales Representative"
},
new Customer
{
CompanyName = "Ana Trujillo Emparedados y helados",
ContactName = "Ana Trujillo",
ContactTitle = "Owner"
},
new Customer
{
CompanyName = "Antonio Moreno Taqueria",
ContactName = "Antonio Moreno",
ContactTitle = "Owner"
},
new Customer
{
CompanyName = "Around the Horn",
ContactName = "Thomas Hardy",
ContactTitle = "Sales Representative"
},
new Customer
{
CompanyName = "Berglunds snabbkop",
ContactName = "Christina Berglund",
ContactTitle = "Order Administrator"
},
new Customer
{
CompanyName = "Blauer See Delikatessen",
ContactName = "Hanna Moos",
ContactTitle = "Sales Representative"
},
new Customer
{
CompanyName = "France restauration",
ContactName = "Carine Schmitt",
ContactTitle = "Marketing Manager"
},
new Customer
{
CompanyName = "Morgenstern Gesundkost",
ContactName = "Alexander Feuer",
ContactTitle = "Marketing Assistant"
},
new Customer
{
CompanyName = "Simons bistro ",
ContactName = "Dominique Perrier",
ContactTitle = "Marketing Manager"
},
new Customer
{
CompanyName = "Island Trading",
ContactName = "Helen Bennett",
ContactTitle = "Marketing Manager"
}
};
return customerList;
}
}
Save methods
The method responsible for saving data to the database which resides in the test base.
protected IEnumerable<T> AddSandboxEntities<T>(IEnumerable<T> sandboxEntities) where T : class
{
foreach (T entity in sandboxEntities)
{
AddSandboxEntity(entity);
}
return sandboxEntities;
}
To add a single entity
public T AddSandboxEntity<T>(T sandboxEntity) where T : class
{
DbContext.Set<T>().Add(sandboxEntity);
AnnihilationList.Add(sandboxEntity);
return sandboxEntity;
}
Method to create an entity
public GenderType CreateFemale()
{
return new GenderType { Gender = "Female" };
}
Setup, in the following method several items are created using AddSandboxEntity
public void CreateMockData()
{
var female = AddSandboxEntity(CreateFemale());
var male = AddSandboxEntity(CreateMale());
AddSandboxEntity(CreatePerson("Karen", "Payne", female));
AddSandboxEntity(CreatePerson("Mike", "Jones", male));
DbContext.SaveChanges();
}
The reason for compartmentalizing creation of objects is first off code is cleaning then creating each entity in one method and secondly, we can later override or overload a method to fit a specific condition. Once an entity is added to the list of objects you can then use GetSandboxEntities to retrieve an object and alter properties to suit a specific test method.
Sample test method
At this point, a test method can be written to work with the data saved to the database table.
[TestTraits(Trait.Customers)]
[TestMethod()]
public void LocateSpecificCustomerByContactNameAndContactTitle()
{
CreateMockData();
var customers = GetSandboxEntities<Customer>();
var result = (customers.Where(cust =>
cust.ContactName == "Christina Berglund" &&
cust.ContactTitle == "Order Administrator")).Count();
Assert.AreEqual(1, result,
"Expected one customer");
}
Break down after test runs
Once the test has completed a method is called in TestCleanup (tear down)
[TestCleanup]
public void TeardownTestBase()
{
var success = AnnihilateData(AnnihilationList);
DbContext.Dispose();
if (!success)
{
throw new Exception("The database is now dirty!");
}
}
Underlying mechanics
Before inspecting AnnihilateData let’s look at a class generated (same works for code first) by Entity Framework.
namespace EntityLibrary
{
using System;
using System.Collections.Generic;
public partial class Person
{
public int id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public Nullable<int> GenderIdentifier { get; set; }
public Nullable<bool> IsDeleted { get; set; }
public virtual GenderType GenderType { get; set; }
}
}
namespace EntityLibrary
{
using System;
using System.Collections.Generic;
public partial class Customer
{
public int id { get; set; }
public string CompanyName { get; set; }
public string ContactName { get; set; }
public string ContactTitle { get; set; }
}
}
Both classes have id as their primary key. In AnnihilateData part of the code looks for a specific property which denotes the primary key to find in the list of objects. Using the classes above there is no issues but suppose in Customer class id was CustomerIdentifier. This would break the logic in AnnihilateData. So to allow AnnihilateData to find the primary key for each class we need to implement an Interface e.g.
public interface IBaseEntity
{
int Identifier { get; }
}
Implemented as shown below.
public partial class Customer : IBaseEntity
{
public int Identifier => id;
}
So now in AnnihilateData within the for/next the following line of code can find an entity by the property setup by using IBaseEntity Interface.
var existingItem = destroyContext
.Set(currentObject.GetType())
.Find(((IBaseEntity)currentObject).Identifier);
Here is the entire method for iterating over the list of objects which locates an entity, checks it still exists then marks its state for removal. Once finished iterating the list SaveChanges is invoked on the DbContext followed by checking the count returned from SaveChanges to the count from the list of objects which housed the objects created for the unit test method which just executed. Run another unit test method and the cycle repeats.
public bool AnnihilateData(List<object> mAnnihilateList)
{
bool mAnnihilateDataSuccessful = false;
using (var destroyContext = new PersonEntities())
{
for (int i = mAnnihilateList.Count - 1; i >= 0; i--)
{
try
{
var currentObject = mAnnihilateList[i];
var existingItem = destroyContext
.Set(currentObject.GetType())
.Find(((IBaseEntity)currentObject).Identifier);
if (existingItem != null)
{
try
{
var attachedEntry = destroyContext.Entry(existingItem);
attachedEntry.CurrentValues.SetValues(currentObject);
destroyContext.Set(existingItem.GetType()).Attach(existingItem);
destroyContext.Set(existingItem.GetType()).Remove(existingItem);
}
catch (Exception)
{
// ignore nothing do to as the object was not added in properly.
}
}
else
{
var item = currentObject.GetType();
}
}
catch (Exception)
{
//catch and continue save what we can
}
}
try
{
var resultCount = destroyContext.SaveChanges();
var annihlationCount = mAnnihilateList.Count;
mAnnihilateDataSuccessful = (resultCount == annihlationCount);
}
catch (Exception)
{
// keep on going
}
finally
{
destroyContext.Dispose();
}
}
return mAnnihilateDataSuccessful;
}
Implementation in a solution
Step one, setup a partial class for each class in your model using the Interface IBaseEntity. In this example, the class is Product (taken from Microsoft North Wind database) where the primary key is ProductID.
public partial class Product : IBaseEntity
{
public int Identifier => ProductID;
}
Next step is to create the base test class as follows where the difference between the test base above is the DbContext. In the example below, the test base is named ProductTestBase since there are two models now in the entity framework class project. Which brings up a point in that with two models you will need to duplicate functionality which is not common so for your code the class name would be TestBase.
namespace SampleUnitTest
{
[TestClass()]
public class ProductTestBase
{
protected List<object> AnnihilationList;
protected ProductEntities TheDbContext;
public ProductEntities DbContext
{
get
{
return TheDbContext;
}
set
{
TheDbContext = value;
}
}
[TestInitialize]
public void SetupTestBase()
{
AnnihilationList = new List<object>();
DbContext = new ProductEntities();
}
[TestCleanup]
public void TeardownTestBase()
{
var success = AnnihilateData(AnnihilationList);
DbContext.Dispose();
if (!success)
{
throw new Exception("The database is now dirty! "+
"The unit test failed to dispose of its test data!");
}
}
/// <summary>
/// Gets all objects of the given type that exist in the annihilateList.
/// </summary>
/// <typeparam name="T">The type of objects to return</typeparam>
/// <returns></returns>
protected IEnumerable<T> GetSandboxEntities<T>()
{
var returnObject = (
from item in AnnihilationList
where item.GetType() == typeof(T)
select (T)item);
return returnObject;
}
/// <summary>
/// Adds an entity object to the db context and the annihilateList.
/// </summary>
/// <typeparam name="T">An EF entity type</typeparam>
/// <param name="sandboxEntity">
/// An EF entity to add to the sandbox.
/// </param>
public T AddSandboxEntity<T>(T sandboxEntity) where T : class
{
DbContext.Set<T>().Add(sandboxEntity);
AnnihilationList.Add(sandboxEntity);
return sandboxEntity;
}
/// <summary>
/// Adds an entity object to the db context and the annihilateList.
/// </summary>
/// <typeparam name="T">An EF entity type</typeparam>
/// <param name="sandboxEntities">
/// Enumerable of EF entities to add to the sandbox.
/// </param>
protected IEnumerable<T> AddSandboxEntities<T>(IEnumerable<T> sandboxEntities) where T : class
{
foreach (T entity in sandboxEntities)
{
AddSandboxEntity(entity);
}
return sandboxEntities;
}
/// <summary>
/// Removes test data from database, called from all tests that create data
/// </summary>
/// <remarks>
/// If you have issues with data not disposing then set break-points
/// in the emppty try/catch statements to figure out the issue. More likely
/// than not the interface, in this case IBaseEntity was not implemented on
/// one of the classes.
///
/// The try-catches allow us to continue and throw an exception message in
/// the tear down event TeardownTestBase for any test.
///
/// Empty try/catches are okay here as you should be using this only for
/// unit testing and hopefully on a non production database.
///
/// </remarks>
public bool AnnihilateData(List<object> mAnnihilateList)
{
bool mAnnihilateDataSuccessful = false;
using (var destroyContext = new ProductEntities())
{
for (int i = mAnnihilateList.Count - 1; i >= 0; i--)
{
try
{
var currentObject = mAnnihilateList[i];
var existingItem = destroyContext
.Set(currentObject.GetType())
.Find(((IBaseEntity)currentObject).Identifier);
if (existingItem != null)
{
try
{
var attachedEntry = destroyContext.Entry(existingItem);
attachedEntry.CurrentValues.SetValues(currentObject);
destroyContext.Set(existingItem.GetType()).Attach(existingItem);
destroyContext.Set(existingItem.GetType()).Remove(existingItem);
}
catch (Exception)
{
// ignore nothing do to as the object was not added in properly.
}
}
else
{
var item = currentObject.GetType();
}
}
catch (Exception)
{
//catch and continue save what we can
}
}
try
{
var resultCount = destroyContext.SaveChanges();
var annihlationCount = mAnnihilateList.Count;
mAnnihilateDataSuccessful = (resultCount == annihlationCount);
}
catch (Exception)
{
// keep on going
}
finally
{
destroyContext.Dispose();
}
}
return mAnnihilateDataSuccessful;
}
}
}
Create your class for mocking up data which is saved to the database table.
public class ProductCreateData : ProductTestBase
{
public void CreateMockData()
{
AddSandboxEntities(CreateProducts());
DbContext.SaveChanges();
}
public List<Product> CreateProducts()
{
var productList = new List<Product>()
{
new Product()
{
ProductName = "Chef Anton's Cajun Seasoning",
SupplierID = 1,
CategoryID = 11,
QuantityPerUnit = "24 - 12 oz bottles",
UnitPrice = 22
},
new Product()
{
ProductName = "Queso Cabrales",
SupplierID = 4,
CategoryID = 1,
QuantityPerUnit = "1 kg pkg.",
UnitPrice = 40
}
};
return productList;
}
}
Write unit test method, that’s it.
[TestClass]
public class ProductTest : ProductCreateData
{
[TestTraits(Trait.Product)]
[TestMethod]
public void ProductExists()
{
CreateMockData();
var product = GetSandboxEntities<Product>()
.FirstOrDefault(p => p.ProductName == "Queso Cabrales");
Assert.IsTrue(product != null && product.UnitPrice == 40,
"Expected 40 for UnitPrice");
}
}
Summary
Working with live data instead of mocked data allows developers to not only test data operations but also get an idea of performance in a test environment.
What has been presented here can be enhanced with logic to return primary keys if there are failures to remove records in the database. As a solution becomes complex it’s very possible to write mocked data that does not flow properly in regards to table relationships. A follow-up article will show how to implement reporting of primary keys which did not delete returned in the code lens output window of a unit test method.
See also
Unit Testing with Entity Framework
Creating Unit Test for Projects Using Microsoft’s Entity Framework
Source code
MSDN C#
MSDN VB.NET
8/2021 GitHub repository for C#
