ASP.NET MVC Tip #20 – How to Unit Test Data Access
In this tip, I demonstrate how you can write unit tests for MVC controller actions that access a database. I show you how to create unit tests for your LINQ to SQL controller action code.
Most ASP.NET MVC applications that I write contain a substantial amount of data access code. Typically, I use Microsoft LINQ to SQL to perform database operations. How do you unit test this data access code?
There are several different approaches that you might take to this problem:
(1) Don’t unit test data access code.
(2) Create a test database when unit testing data access code
(3) Fake the DataContext when unit testing data access code.
Many members of the Test-Driven Development community would argue that you should never unit test data access code. For example, Michael Feathers in his excellent book Working Effectively with Legacy Code argues that you should never unit test data access code when practicing TDD. According to Feathers, a unit test needs to execute in less than 1/10 of a second. Since data access code is too slow, you shouldn’t unit test it.
The second option is to create a new test database each and every time you run a unit test. This is the approach that I will take in this tip. In this tip, I will show you how to generate a test database from a DataContext automatically.
Finally, you could fake the DataContext with an in-memory database. I actually think that this approach is the best approach. This approach would keep Michael Feathers happy since it would allow you to write unit tests that execute very quickly. I plan to explore this third approach in a future tip.
A Simple Data Access MVC Web Application
When practicing Test-Driven Development, you should write your tests first and then code against the tests. This approach to building applications forces you to write your code from the perspective of someone who uses your code.
Because, in this tip, I am interested in demonstrating how you can unit test data access code in an ASP.NET MVC application, I am going to violate good Test-Driven Development practices and write my code first. Please forgive me for this transgression.
The HomeController in Listing 1 exposes two actions. The first action, named Index(), returns a set of movie database records. The second action, named InsertMovie(), adds a new movie to the database. Both the Index() and InsertMovie() methods use LINQ to SQL to access the database.
Notice that the HomeController class has two constructors. The first constructor accepts a LINQ to SQL DataContext as a parameter. The second constructor is a parameterless constructor. This second constructor creates a DataContext and passes it to the first constructor.
The idea is that the parameterless constructor will be called on the HomeController class when the MVC application is actually running. Unit tests will take advantage of the constructor that takes the DataContext parameter. That way, a unit test can pass a test DataContext instead of the actual DataContext.
Listing 1 – HomeController.vb (VB.NET)
Imports Tip20
Public Class HomeController
Inherits System.Web.Mvc.Controller
Private _dataContext As MovieDataContext
Public Sub New(ByVal dataContext As MovieDataContext)
_dataContext = dataContext
End Sub
Public Sub New()
Me.New(New MovieDataContext())
End Sub
Public Function Index() As ActionResult
Dim movies = _dataContext.Movies.OrderByDescending(Function(m) m.Id)
Return View(movies)
End Function
Public Function InsertMovie(ByVal title As String, ByVal director As String) As ActionResult
Dim newMovie = New Movie()
newMovie.Title = title
newMovie.Director = director
newMovie.DateReleased = DateTime.Parse("12/25/1966")
_dataContext.Movies.InsertOnSubmit(newMovie)
_dataContext.SubmitChanges()
Return RedirectToAction("Index")
End Function
End Class
Listing 1 – HomeController.cs (C#)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Tip20.Models;
namespace Tip20.Controllers
{
public class HomeController : Controller
{
private MovieDataContext _dataContext;
public HomeController(MovieDataContext dataContext)
{
_dataContext = dataContext;
}
public HomeController()
: this(new MovieDataContext())
{ }
public ActionResult Index()
{
var movies = _dataContext.Movies.OrderByDescending(m => m.Id);
return View(movies);
}
public ActionResult InsertMovie(string title, string director)
{
var newMovie = new Movie();
newMovie.Title = title;
newMovie.Director = director;
newMovie.DateReleased = DateTime.Parse("12/25/1966");
_dataContext.Movies.InsertOnSubmit(newMovie);
_dataContext.SubmitChanges();
return RedirectToAction("Index");
}
}
}
Creating a DataContext Unit Test Base Class
So how do you create unit tests for the HomeController class? In this section, I explain how you can create a base DataContextUnitTest class that you can use as the base class for unit testing controller actions that use LINQ to SQL.
The DataContextUnitTest class is contained in Listing 2.
Listing 2 – DataContextUnitTest.vb (VB.NET)
Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports System.Data.Linq
Imports Microsoft.VisualStudio.TestTools.UnitTesting
Imports System.Data.SqlClient
Imports System.Reflection
Imports System.IO
Public MustInherit Class DataContextUnitTest(Of T As DataContext)
Private Const TestDBPath As String = "C:\Users\swalther\Documents\Common Content\Blog\Tip20 Linq to SQL CreateDatabase\VB\Tip20Tests\App_Data\Test.mdf"
Private privateTestDataContext As T
Protected Property TestDataContext() As T
Get
Return privateTestDataContext
End Get
Set(ByVal value As T)
privateTestDataContext = value
End Set
End Property
<TestInitialize()> _
Public Sub Initialize()
Me.CreateTestDB()
End Sub
Public Sub CreateTestDB()
Dim testConnectionString = GetTestConnectionString()
' Need to use reflection here since you
' cannot use Generics with a contructors that require params
Dim types() As Type = {GetType(String)}
Dim typeValues() As Object = {testConnectionString}
Me.TestDataContext = CType(GetType(T).GetConstructor(types).Invoke(typeValues), T)
Me.RemoveTestDB()
Me.TestDataContext.CreateDatabase()
End Sub
<TestCleanup()> _
Public Sub Cleanup()
Me.RemoveTestDB()
End Sub
Protected Sub RemoveTestDB()
If Me.TestDataContext.DatabaseExists() Then
Me.TestDataContext.DeleteDatabase()
End If
End Sub
Private Shared Function GetTestConnectionString() As String
Dim conBuilder = New SqlConnectionStringBuilder()
conBuilder.AttachDBFilename = TestDBPath
conBuilder.DataSource = ".\SQLExpress"
conBuilder.IntegratedSecurity = True
conBuilder.UserInstance = True
Return conBuilder.ConnectionString
End Function
End Class
Listing 2 – DataContextUnitTest.cs (C#)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System.Data.SqlClient;
using System.Reflection;
using System.IO;
public abstract class DataContextUnitTest<T> where T: DataContext
{
const string TestDBPath = @"C:\Users\swalther\Documents\Common Content\Blog\Tip20 Linq to SQL CreateDatabase\CS\Tip20Tests\App_Data\Test.mdf";
protected T TestDataContext { get; set; }
[TestInitialize]
public void Initialize()
{
this.CreateTestDB();
}
public void CreateTestDB()
{
var testConnectionString = GetTestConnectionString();
// Need to use reflection here since you
// cannot use Generics with a contructors that require params
Type[] types = {typeof(string)};
Object[] typeValues = { testConnectionString };
this.TestDataContext = (T)typeof(T).GetConstructor(types).Invoke(typeValues);
this.RemoveTestDB();
this.TestDataContext.CreateDatabase();
}
[TestCleanup]
public void Cleanup()
{
this.RemoveTestDB();
}
protected void RemoveTestDB()
{
if (this.TestDataContext.DatabaseExists())
this.TestDataContext.DeleteDatabase();
}
private static string GetTestConnectionString()
{
var conBuilder = new SqlConnectionStringBuilder();
conBuilder.AttachDBFilename = TestDBPath;
conBuilder.DataSource = @".\SQLExpress";
conBuilder.IntegratedSecurity = true;
conBuilder.UserInstance = true;
return conBuilder.ConnectionString;
}
}
Before you can use the DataContextUnitTest class, you need to add references to the System.Data.Linq and System.Data assemblies to your test project.
Notice that the DataContextUnitTest class is a generic class. When creating an instance of the class, you must specify the type of DataContext that the class represents. The variable T represents a type of DataContext.
Notice, furthermore, that the DataContextUnitTest class includes a constant named TestDBPath. You set this constant to the path where you want to create your test database. Remember to modify this constant if you download the code for this tip and want to use the DataContextUnitTest class in your own projects.
The DataContextUnitTest class includes a method, named Initialize(), that is decorated with the TestInitialize attribute. This attribute causes this method to be executed before each and every unit test. The Initialize() method creates a new test DataContext and generates a new database. The new database is created by calling the CreateDatabase() method of the DataContext class.
The DataContextUnitTest class also includes a Cleanup() method decorated with the TestCleanup attribute. After each and every unit test is executed, the test database is destroyed. The DataContext.DeleteDatabase() method is used to destroy the database file on disk.
You can use the DataContextUnitTest class as the base class for any unit tests that test controller data access. For example, the class in Listing 3 contains two unit tests for the HomeController class. The unit test methods, named IndexMovieCount() and IndexInsertMovie(), are decorated with the TestMethod attribute.
Listing 3 – HomeControllerTest.vb (VB.NET)
Imports System
Imports System.Collections.Generic
Imports System.Text
Imports System.Web.Mvc
Imports Microsoft.VisualStudio.TestTools.UnitTesting
Imports Tip20
<TestClass()> Public Class HomeControllerTest
Inherits DataContextUnitTest(Of MovieDataContext)
Public Function CreateTestMovie(ByVal title As String, ByVal director As String) As Movie
Dim newMovie = New Movie()
newMovie.Title = title
newMovie.Director = director
newMovie.DateReleased = DateTime.Parse("12/25/1966")
Return newMovie
End Function
Public Sub AddTestData()
Dim newMovie1 = Me.CreateTestMovie("Star Wars", "George Lucas")
Me.TestDataContext.Movies.InsertOnSubmit(newMovie1)
Dim newMovie2 = Me.CreateTestMovie("Ghost Busters", "Ivan Reitman")
Me.TestDataContext.Movies.InsertOnSubmit(newMovie2)
Me.TestDataContext.SubmitChanges()
End Sub
<TestMethod()> _
Public Sub IndexMovieCount()
' Arrange
Me.AddTestData()
Dim controller As New HomeController(Me.TestDataContext)
' Act
Dim result As ViewResult = TryCast(controller.Index(), ViewResult)
' Assert
Dim model = CType(result.ViewData.Model, IQueryable(Of Movie))
Assert.AreEqual(2, model.Count())
End Sub
<TestMethod()> _
Public Sub IndexInsertMovie()
' Arrange
Dim controller As New HomeController(Me.TestDataContext)
' Act
Dim title = "King Kong"
Dim director = "Peter Jackson"
controller.InsertMovie(title, director)
' Assert
Dim results = From m In Me.TestDataContext.Movies _
Where m.Title = title AndAlso m.Director Is director _
Select m
Assert.AreEqual(1, results.Count())
End Sub
End Class
Listing 3 – HomeControllerTest.cs (C#)
using System;
using System.Web.Mvc;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Tip20.Controllers;
using Tip20.Models;
using System.Data.Linq;
using System.Linq;
namespace Tip20Tests.Controllers
{
[TestClass]
public class HomeControllerTest : DataContextUnitTest<MovieDataContext>
{
public Movie CreateTestMovie(string title, string director)
{
var newMovie = new Movie();
newMovie.Title = title;
newMovie.Director = director;
newMovie.DateReleased = DateTime.Parse("12/25/1966");
return newMovie;
}
public void AddTestData()
{
var newMovie1 = this.CreateTestMovie("Star Wars", "George Lucas");
this.TestDataContext.Movies.InsertOnSubmit(newMovie1);
var newMovie2 = this.CreateTestMovie("Ghost Busters", "Ivan Reitman");
this.TestDataContext.Movies.InsertOnSubmit(newMovie2);
this.TestDataContext.SubmitChanges();
}
[TestMethod]
public void IndexMovieCount()
{
// Arrange
this.AddTestData();
HomeController controller = new HomeController(this.TestDataContext);
// Act
ViewResult result = controller.Index() as ViewResult;
// Assert
var model = (IQueryable<Movie>)result.ViewData.Model;
Assert.AreEqual(2, model.Count());
}
[TestMethod]
public void IndexInsertMovie()
{
// Arrange
HomeController controller = new HomeController(this.TestDataContext);
// Act
var title = "King Kong";
var director = "Peter Jackson";
controller.InsertMovie(title, director);
// Assert
var results = from m in this.TestDataContext.Movies
where m.Title == title && m.Director == director select m;
Assert.AreEqual(1, results.Count());
}
}
}
Notice that the HomeControllerTest class derives from the base DataContextUnitTest class. The MovieDataContext type is passed to the generic base class.
The first unit test method, IndexMovieCount(), verifies that the Index() controller action correctly returns a set of movie records from the database. First, the test method inserts two movies into the database. Next, the HomeController.Index() method is called. The number of records returned by the Index() method is verified. If two records are returned, the test is a success.
The second unit test method, IndexInsertMovie(), checks whether a new movie record gets inserted correctly into the database. The method calls the HomeController.InsertMovie() record to add a new movie. Next, the test method attempts to retrieve the exact same record from the test database.
Summary
In this tip, I demonstrated one approach for unit testing MVC controller actions that access a database. I demonstrated how you can generate a test database from a LINQ to SQL DataContext automatically. I showed you how you can create a standard base class for unit testing controller actions that perform LINQ to SQL queries.
Download the Code