Subscribe to this Blog

Subscribe to this Blog

ASP.NET MVC Tip #20 – How to Unit Test Data Access - Stephen Walther on ASP.NET MVC

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
Published Tuesday, July 15, 2008 1:28 PM by swalther
Filed under: , ,

Comments

# re: ASP.NET MVC Tip #20 – How to Unit Test Data Access

Tuesday, July 15, 2008 5:45 PM by Eric

I'd vote for removing the scrollable code regions in your posts.  Feed Readers don't like them, at least not FeedDemon, and they seem a little unnessessary.

Great content though!!

# re: ASP.NET MVC Tip #20 – How to Unit Test Data Access

Tuesday, July 15, 2008 5:48 PM by Jeremy D. Miller

"Many members of the Test-Driven Development community would argue that you should never unit test data access code"

Please don't say things like that.  The TDD community generally says you should test your data access code, but those tests are integration tests.  The TDD community, from very painful experience over 10 years, says you should strive to unit test your business logic code independently of the data access code.

An easier way to do the testing might be to create a wrapper around the DataContext.  Updates through a unit of work, and queries through a Repository interface.  Personally, I wouldn't want my Controller classes to ever be directly exposed to infrastructure like Linq to SQL.

# re: ASP.NET MVC Tip #20 – How to Unit Test Data Access

Tuesday, July 15, 2008 6:28 PM by swalther

@Jeremy -- Could you please elaborate on why you should not use TDD when writing data access code? Is it the speed issue that Michael Feathers brings up? Or, is there some other issue?

# re: ASP.NET MVC Tip #20 – How to Unit Test Data Access

Wednesday, July 16, 2008 2:18 AM by haacked

Not to be nitpicky Jeremy, but isn't that what Stephen said. :)

The part you quoted, emphasis mine:

"Many members of the Test-Driven Development community would argue that you should never *UNIT TEST* data access code"

As you point out, these are *integration tests*, not unit tests. I think it's an important distinction. So the key point here is that the TDD community is *not* saying you shouldn't *test* your data access code, but that you shouldn't *unit test* that code insofar as the unit test touches the database.

On that point, I think you and Stephen were in agreement here all along. :)

# re: ASP.NET MVC Tip #20 – How to Unit Test Data Access

Wednesday, July 16, 2008 5:07 AM by Ronnie Hoogland

I've been testing in a similar way. First I write my tests against a fake repository and when I want to do an integration test I replace the fake repository with the real one. And generate a test database with testdata for every test.

It does take while, but then you will have tested the entire chain.

# re: ASP.NET MVC Tip #20 – How to Unit Test Data Access

Wednesday, July 16, 2008 9:58 AM by MikeBosch

Doesn't this post violate any existence of a business layer / data layer?  I feel like the controllers should not be accessing nor exposing Linq entities to the views.

# re: ASP.NET MVC Tip #20 – How to Unit Test Data Access

Wednesday, July 16, 2008 10:29 AM by Remco Ros

+1 for Jeremy & Phil

Testing Data Access should be done using Integration testing, not Unit testing.

# re: ASP.NET MVC Tip #20 – How to Unit Test Data Access

Wednesday, July 16, 2008 12:51 PM by fogjuice

Stephen, first off... I like the time and effort you are dedicating to ASP.NET MVC.

In addition to Eric's request for removing the scrolling code windows, could you also have a method (cookie maybe?) for either showing the VB or C# listings, but not both?  That would make reading your blog posts a lot easier.