Unit Testing for Data Access Layer with MOQ Framework

Software release cycles are becoming shorter and more agile by the day. Enterprises across domains have started automating the software development, deployment processes, and quality checks. The test automation has become the key focus. Tests like unit tests, functional tests, integration tests, and performance/vulnerability tests are the key measures demanded by the customers which make sure that the quality is never compromised.

With advancements in development frameworks and flexibility, the efficiency of an application can now be easily improved by redesigning and rearchitecting various layers. Organizations may not be interested in modifying the business logic layer or data access layer often. For many business needs, they may end up modifying the UI layer, service layer while making minimal changes to the database. This increases the importance of using the Unit Test Frameworks to help identify and resolve issues at an early stage of development.

In this blog, we will explain how the data access layer can be thoroughly unit tested, considering we are not redesigning the core of it. Applications that use the legacy code and old design patterns may not have the code and classes with appropriate abstractions that help you to complete the testing. The intention is not to create a new test database but have a different way to mock the database and enable the “DBSets”.

The scenario in this blog uses Entity Framework in the Data Access Layer and considers MasterEntityContext as the DBContext. It is utilized here to retrieve customer information as detailed below.

using (var context = new MasterEntityContext())
var info = context.Customers.Find(customerID);

In the above code, MasterEntityContext is instantiated inside the data access layer class that creates dependency and doesn’t permit the use of Mock Context. The DBContext class can be mocked using MOQFramework, and to abstract the context class, it must be containerized in the static class and should use the static property cMasterEntityContext as detailed below by adding a parameter to the constructor of the Model class.

public class CustomerModel 
private readonly MasterEntityContext _context;
public CustomerModel (MasterEntityContext context)
_context = context;

Create a static class that provides the instance of MasterEntityContext. It can be “set” and used to create the mock context.

public static class ContextContainer
private static MasterEntityContext _master ;
public static MasterEntityContext cMasterEntityContext
if (master == null) master = new MasterEntityContext ();
return _ master;
_ master = value;

The code in the Data Access Layer class will be changed as below.

using (var context = new MasterEntityContext())
var info = context.Customers.Find(customerID);

using (var context = ContextContainer. cMasterEntityContext)
var info = context.Customers.Find(customerID);

The above changes will enable SET mocked DBContext class by using static context container class, and will allow the use of mocked context class specifically for the Unit Test.

The next step is to setup MOCK/TEST Data. Here, we will use XML values stored in resource file as our data store to enable easy maintenance.

The below code details how XML data is retrieved from the SQL tables with a select query.

<select query> FOR xml AUTO, ROOT (<RootName>), ELEMENTS


SELECT * FROM Customers [Customer]FOR xml AUTO, ROOT ('ListOfCustomers'), ELEMENTS

XML Result:
<Address>12 Temple Bridge</Address>
    <CompanyName>John Visco</CompanyName>
    <Address>15 Street</Address>
    <City>Temple Town</City>
    <CompanyName>Devon Conn</CompanyName>

The XML result is the data store for the customer data. These three customer records can be used for testing. The XML result is now added as a value for an identifiable key name in the resource file. Similarly, multiple XML results can be added. For example: Customer Orders, Products, etc.

The XML result should now be converted into a DBSet. First, convert the XML to an IEnumerable and then to a DBSet by using dotnet Reflection namespace and System.Xml.Serialization namespace. The generated list should be similar to the default DBSet created by the Entity Framework.

public static ICollection ConvertXMLToList(string xmlData, string root)
var reader = new StringReader(xmlData);
XmlRootAttribute xRoot = new XmlRootAttribute(root);
xRoot.IsNullable = true;
var ser = new System.Xml.Serialization.XmlSerializer(typeof(Collection), getXMLAttributes(), null, xRoot, "");
var Data = (Collection)ser.Deserialize(reader);
return Data;

The supporting methods:

public static XmlAttributeOverrides getXMLAttributes()
XmlAttributes attrs = new XmlAttributes();
attrs.XmlIgnore = true;
XmlAttributeOverrides attrOverrides = new XmlAttributeOverrides();
foreach (var property in typeof(T).GetProperties())
if (!MockHelper.IsSimpleType(property.PropertyType))
attrOverrides.Add(typeof(T), property.Name, attrs);
return attrOverrides;
public static bool IsSimpleType(Type type)
type.IsPrimitive ||
new Type[] {
}.Contains(type) ||
Convert.GetTypeCode(type) != TypeCode.Object ||
(type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>) && IsSimpleType(type.GetGenericArguments()[0]));

The output of ConvertXMLToList method is an IEnumerable from the XML value and should be similar to the default DBSet created by the Entity Framework ORM Model.

Below is the method to convert IEnumerable into a DbSet by utilizing MOQ Framework. Mock<<DBset<>> plays a crucial role here. It can hold any list and convert it into a DBSet.

public static Mock> getMoqDBset(ICollection data, Func primaryKey = null)
var listOfT = data.AsQueryable();
var moqData = new Mock>();
moqData.As>().Setup(m => m.Provider).Returns(listOfT.Provider);
moqData.As>().Setup(m => m.Expression).Returns(listOfT.Expression);
moqData.As>().Setup(m => m.ElementType).Returns(listOfT.ElementType);
moqData.As>().Setup(m => m.GetEnumerator()).Returns(()=>listOfT.GetEnumerator());
if (primaryKey != null)
moqData.Setup(set => set.Find(It.IsAny())).Returns((object[] input) => listOfT.FirstOrDefault(x => primaryKey(x).Equals(input.First())));
moqData.Setup(d => d.Add(It.IsAny())).Callback((s) => data.Add(s));
moqData.Setup(d => d.Remove(It.IsAny())).Callback((s) => data.Remove(moqData.Object.Find(primaryKey(s))));
return moqData;

The Mock<DBSet<T>> returned from the above method will help to:
* Test the DbSet.Find(), DBSet.Add(), DBSet.Remove() and other LINQ IQueryable functions.
* Achieve DBSet.Find() for which an explicit Primary Key for that table should be provided.

For example:
var DataList= ConvertXMLToList(MasterResources.Customer, “ArrayOfCustomer”);
DBset MockCustomers =getMoqDBset(DataList, x=> x. CustomerID).object;

After creating mocked DBSets for all required models that need to be subjected to the Unit Test process, create the mocked DBContext class again using the MOQ Framework.

mock mockDataContext= new Mock();
mockDataContext.Setup(c => c.Customers).Returns(MockCustomers);

This Mock DBContext is set to the context property in the static container class. As detailed below, CustomerTests is a test class for the Customer model and the mock DBContext Class is set within the constructor.

public class CustomerTests
public CustomerTests ()
ContextContainer. cMasterEntityContext = mockDataContext.Object;

With this, all methods in the Test Class can now utilize the mock DBContext class and can use the mock DBSets that retrieve all data from the XML data store instead of the actual database.

This is one of the easiest ways to write Unit Tests for the Data Access Layer code using MOQ Framework and without creating a new test database.

Organizations are now using Continuous Integration and Continuous Deployment (CI/CD) to release projects faster. We recommend having Unit Test Suite executed as a part of the automated builds to ensure minimal regression. The approach explained in this blog will help to create a core set of tests that should be focused and assist in redesigning the existing systems and applications. It facilitates test driven development and improves the project quality.