Unit testing with Entity Framework

Posted: February 4, 2014 in All, Unit Testing, WCF
Tags: , , , , , , , ,

In this post I would like to discuss about writing Unit Tests for BLL components which uses EF for DB operations. My intension here is not to focus on writing tests for DB CRUD operations but to test business logic which involves retrieving further details from Database using EF to validate business scenarios. For these scenarios, either BLL component has to get data from Database or we need to explicitly provide some kind of sample data/objects so that validations can be done against of these objects.

Following are few of the different ways for doing this:

Using SQL Server Compact Edition(SQL CE):
SQL CE database can be created dynamically (.sdf file on disc) and data can be populated using SQL CE schema/sample data scripts. SQL Server scripts don’t work with SQL CE. Using SQL Server Compact Tool we can generate SQL CE compatible Schema/sample data scripts.
Here is the code snippet how to programmatically create CE database. System.Data.SqlServerCe reference has to be added to the project.
Connection String can be defined as shown below:

strConnString = “Data Source=” + CE_DB_FilePath_OnDisk;

SqlCeConnection objConn = null;
objConn = new SqlCeConnection(strConnString);
string[] arrCommands = strDatabaseSchema.Split(new string[] { m_strCommandSeparator }, StringSplitOptions.RemoveEmptyEntries);
SqlCeCommand objCmd = new SqlCeCommand();
objCmd.Connection = objConn;

foreach (string strCmd in arrCommands)
    string strTrimmedCmd = strCmd.Trim();
    if (!String.IsNullOrEmpty(strTrimmedCmd))
        objCmd.CommandText = strTrimmedCmd;

Once we create the CE Database, we can create context by passing connection string.

Public class LibraryContext : DbContext
    public LibraryContent()
           : base("LibraryDBConnection")

Connection string can be configured in .config file.

    <add name=" LibraryDBConnection "
         connectionString="Data Source=|DataDirectory|LibraryDb.sdf"/>

SQL CE is in memory/file IO based and doesn’t require installation of SQL. So these tests can be executed on build agents on TFS without any additional SQL pre-requisites.
It is a sample database which is a replica of production Database, so most of the business logic scenarios can be covered.

The issue with this approach is, we can’t create single DBContext which serves both SQL Server and SQL CE using same model (.edmx) due to following reasons:
SQL server model uses “System.Data.SqlClient” provider whereas SQL CE expects “System.Data.SqlServerCe.4.0”

EntitySet contains Schema=”dbo” however this is not applicable for SQL CE.
SQL CE limitations such as it doesn’t support Stored Procedures, Views, Triggers

Mocking DB context:
Using Moq framework we can have in-memory implementations for DB context and can be used for unit tests.
For example we have a simple model for Novels and Magazines. The context and interface looks like as shown below:

Public interface ILibraryContext
    DbSet Books { get; set;}
	DbSet Magazines { get; set;}
	void SaveChanges();

Public class LibraryContext : DbContext, ILibraryContext
	Public DbSet Books { get; set;}
	Public DbSet Magazines { get; set;}
	Public void SaveChanges()

Now we can mock the context.

_context = new Mock< ILibraryContext>(MockBehavior.Strict);

We need to Setup the methods which are used while testing the actual methods.

_context.Setup(c => c.SaveChanges()).Returns(LocalSaveMethod);

Implement some sample method for save.
We also need to setup objects. Objects setup requires corresponding object data.

_bookData = new List
            new Book(), new Book()
_context.Setup(c => c. Books).Returns(PopulateBooksMockSet);

Private IDbSet PopulateBooksMockSet()
    Mock<IDbSet> _booksMockSet = new Mock<IDbSet>();
    _ booksMockSet.As().Setup(
        m => m.Provider).Returns(_bookData.AsQueryable().Provider);

    _ booksMockSet.As<IQueryable>().Setup(
        m => m.Expression).Returns(__bookData.AsQueryable().Expression);

    _ booksMockSet.As<IQueryable>().Setup(
        m => m.ElementType).Returns(__bookData.AsQueryable().ElementType);

    _ booksMockSet.As<IQueryable>().Setup(
        m => m.GetEnumerator()).Returns(__bookData.AsQueryable().GetEnumerator());
    return _ booksMockSet.Object;

Say the following is the BLL method which we wanted to test.

public void UpdateBookName(ILibraryContext context, Book bookObj, string newName)
    var bookInstance = context.Books.Where(b => b.Id == bookObj.Id).FirstOrDefault();

    if (bookInstance != null)
        bookInstance.Name = newName;


The typical test case would look like this:

public void UpdateName_Test()
     string newName = "Updated name";
     UpdateBookName(context, bookObj, newName);

     Assert.AreEqual(bookObj.Name, newName, "Name not updated");

Now how do we test delete object scenarios.
Generally, when we call context.Remove(object), the object doesn’t get removed from collection instead the object state will be changed to deleted state. When we call context.SaveChanges(), the ojects which are in deleted state will get removed/deleted. With mocking context, saveChanges is a dummy operation.
To test this scenario, we can use Mock<IDbSet<>>. Using Mock sets, We can check if a method was called with a particular parameter or not. We can also check how many times it was called.

bookMockSet.Verify(m => m.Remove(deletedBook), Times.Once(), “Book is not deleted”)

Mocking DbContext works with in memory objects hence it would be faster.
It doesn’t have any addition overhead such as installation/setup etc.

Though we have in memory context it is not equal to DbContext. It uses “LINQ to objects” compared to “LINQ to Entities” which EF actually does.
Creating mock objects (sample data/objects) is tedious.
Though it has such limitations, it can provide a good level of unit test coverage for EF.

Using SQL Express instance:
Create a separate Database programmatically on SQL express instance and populate sample data for every time when a test is executed. Delete the Data at the end of Test execution, so that the data can be repopulated next time when another test is executed. This is more like an integration testing than unit testing.

It is a full blown end to end testing.
It doesn’t have any other maintenance overhead such as creating sample shema/data

This makes SQL express installation as pre-requisite for build agents on TFS.

Please provide your valuable feedback/comments/suggestions that will help me improve my writing.

  1. Marcin says:

    Thanks, really helpful text – short and concrete. Now I am know that I was looking for the solution with SQL Express

  2. Shiva says:

    Do you have the sample code?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s