Subscribe to this Blog

Subscribe to this Blog

ASP.NET MVC Tip #33 – Unit Test LINQ to SQL - Stephen Walther on ASP.NET MVC

ASP.NET MVC Tip #33 – Unit Test LINQ to SQL

In this tip, I demonstrate how to unit test the LINQ to SQL DataContext object by creating a Fake DataContext. You can perform standard LINQ to SQL inserts, updates, deletes and LINQ queries against the Fake DataContext.

I’ve struggled for the past couple of months with different methods of unit testing MVC controllers that return and update database data. I want an easy way of unit testing the database access code in my ASP.NET MVC applications.

I almost gave up until Rob Conery stopped by my office and showed me an easy method of performing LINQ to SQL queries against a standard collection. Once I made it over that hurdle, the rest of the process of building the FakeDataContext class was straightforward (thanks Rob!).

In this tip, I show how you can create a testable LINQ to SQL DataContext object. First, I demonstrate how you can create a wrapper for the standard DataContext object. By creating a DataContext wrapper, you can program against an abstraction instead of a concrete class.

Next, I show how you can fake the DataContext object with almost full fidelity. We create a FakeDataContext that supports inserts, deletes, and standard LINQ to SQL queries (our fake class supports the IQueryable interface).

Wrapping Up the DataContext Class

The first step required to create a testable DataContext object is to create a wrapper class. The standard DataContext object does not implement an interface and it does not derive from a base class. Even worse, it returns sealed Table classes. This means that we can’t swap the DataContext object for a fake DataContext in our unit tests.

The DataContext wrapper class is contained in Listing 1.

Listing 1 – DataContextWrapper.cs

using System.Configuration;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Web.Configuration;
using System.Web.Hosting;

namespace MvcFakes
{
    public class DataContextWrapper : IDataContext
    {
        private DataContext _dataContext;

        public DataContextWrapper(string connectionStringName, string xmlMapPath)
        {
            var conSettings = WebConfigurationManager.ConnectionStrings[connectionStringName];
            if (conSettings == null)
                throw new ConfigurationErrorsException("Missing " + connectionStringName + " connection string in web configuration.");

            var map = XmlMappingSource.FromUrl(HostingEnvironment.MapPath(xmlMapPath));

            _dataContext = new DataContext(conSettings.ConnectionString, map);
        }

        public DataContextWrapper(string fileOrServerOrConnection)
        {
            _dataContext = new DataContext(fileOrServerOrConnection);
        }

        public DataContextWrapper(string fileOrServerOrConnection, MappingSource mapping)
        {
            _dataContext = new DataContext(fileOrServerOrConnection, mapping);
        }


        public void SubmitChanges()
        {
            _dataContext.SubmitChanges();
        }

        public ITable<TEntity> GetTable<TEntity>() where TEntity:class
        {
            return new TableWrapper<TEntity>(_dataContext.GetTable<TEntity>());
        }


    }
}

The DataContextWrapper class in Listing 1 creates a standard DataContext in its constructor. The class implements the same methods as the standard DataContext object. When you call a method of the DataContextWrapper class, the class delegates the call to the standard DataContext class.

So why wrap? Wrapping the DataContext class enables us to add an interface to the DataContext class.

Notice that the DataContextWrapper implements an interface called the IDataContext interface. This interface is not a standard part of the .NET framework. This interface is contained in Listing 2.

Listing 2 – IDataContext.cs

namespace MvcFakes
{
    public interface IDataContext
    {
        void SubmitChanges();

        ITable<TEntity> GetTable<TEntity>() where TEntity : class;
    }

}

The interface in Listing 2 has two methods: SubmitChanges() and GetTable(). Both the DataContextWrapper class and the FakeDataContext class implement these two methods.

The pattern used here is the same pattern used for the classes in the System.Web.Abstractions namespace. This namespace contains wrapper classes for the standard ASP.NET intrinsics such as the HtpContext, HttpResponse, and HttpSessionState intrinsics. The wrappers add interfaces (and base classes) to these standard ASP.NET classes.

Creating a Fake DataContext

The code for the Fake DataContext class is a little too long to post here (but you can download it by clicking the link at the end of this blog entry).

The FakeDataContext class also implements the IDataContext interface. The FakeDataContext does not interact with a real database. Instead, the FakeDataContext interacts with data stored in-memory.

You can perform standard LINQ to SQL operations and queries against the FakeDataContext object. For example, the unit test in Listing 3 use the FakeDataContext object. First, the test adds data to the FakeDataContext by using the standard DataContext InsertOnSubmit() method. Next, a query is performed to return all records where the Title property starts with the letter “S”. If two records are returned, then the test is successful.

Listing 3 – TestWhere()

[TestMethod]
public void TestWhere()
{
    // Arrange
    var dataContext = new FakeDataContext();

    // Act
    var table = dataContext.GetTable<Movie>();
    table.InsertOnSubmit(new Movie("Lion King", "Disney"));
    table.InsertOnSubmit(new Movie("King Kong", "Jackson"));
    table.InsertOnSubmit(new Movie("Star Wars", "Lucas"));
    table.InsertOnSubmit(new Movie("Superman", "Spelling"));
    dataContext.SubmitChanges();

    // Assert
    var movies = from m in table where m.Title.StartsWith("S") select m;
    Assert.AreEqual(2, movies.Count());

}

Using the Fake DataContext

Let’s see how we can use the FakeDataContext object when building a simple Movie database application. The Home controller in Listing 4 contains actions for displaying movies, inserting new movies, and updating existing movies.

Listing 4 – HomeController.cs

using System.Linq;
using System.Web.Mvc;
using MvcFakes;
using Tip33.Models;

namespace Tip33.Controllers
{
    public class HomeController : Controller
    {
        private IDataContext _dataContext;

        public HomeController():this(new DataContextWrapper("dbcon", "~/Models/Movie.xml"))
        {}

        public HomeController(IDataContext dataContext)
        {
            _dataContext = dataContext;
        }

        public ActionResult Index()
        {
            var table = _dataContext.GetTable<Movie>();
            var movies = from m in table select m;
            return View("Index", movies.ToList());
        }

        public ActionResult Create()
        {
            return View("Create");
        }

        public ActionResult Insert(string title, string director)
        {
            var newMovie = new Movie(title, director);
            _dataContext.GetTable<Movie>().InsertOnSubmit(newMovie);
            _dataContext.SubmitChanges();

            return RedirectToAction("Index");
        }

        public ActionResult Edit(int Id)
        {
            var table = _dataContext.GetTable<Movie>();
            var movie = table.SingleOrDefault(m=>m.Id==Id);
            return View("Edit", movie);
        }

        public ActionResult Update(int id, string title, string director)
        {
            var table = _dataContext.GetTable<Movie>();
            var movie = table.SingleOrDefault(m => m.Id == id);
            movie.Title = title;
            movie.Director = director;
            _dataContext.SubmitChanges();
            return RedirectToAction("Index");
        }

    }
}

The Home controller in Listing 4 uses Dependency Injection. It has two constructors. One constructor is used when the application runs. One constructor is used when creating the controller in a unit test.

When the HomeController is used in production, the parameterless constructor is called. This contructor creates an instance of the DataContextWrapper class. It creates a DataContext by passing two values to the DataContextWrapper constructor. The first value represents a database connection string name (the name of a connection string in the web configuration file). The second value is the path to an XML mapping file that maps properties of the Movie class to columns in the database.

Notice that the second constructor does not accept a DataContextWrapper. Instead, it accepts any class that implements the IDataContext interface. Notice, furthermore, that only the IDataContext interface is used within the body of the Home controller.

Because both the real DataContext (the DataContextWrapper) and the FakeDataContext implements the IDataContext interface, the very same Home controller can be instantiated and executed with either the real DataContext or the fake DataContext. This makes the Home controller very testable.

Let’s examine how each of the methods of the Home controller can be tested. The Index() action returns all of the movies from the database and passes the movies to the Index view through view data (see Figure1). The unit test in Listing 5 verifies that the movie data is actually returned in view data.

Figure 1 – Viewing database records

clip_image002

Listing 5 – TestIndex()

private FakeDataContext _fakeDataContext;
 
[TestInitialize]
public void Initialize()
{
    // Create Fake DataContext
    _fakeDataContext = new FakeDataContext();
 
    // Add some fake data
    var table = _fakeDataContext.GetTable<Movie>();
    table.InsertOnSubmit(new Movie(1, "Star Wars", "Lucas"));
    table.InsertOnSubmit(new Movie(2, "Raiders of the Lost Ark", "Speilburg"));
    _fakeDataContext.SubmitChanges();
}
 
 
[TestMethod]
public void TestIndex()
{
    // Arrange
    var controller = new HomeController(_fakeDataContext);
 
    // Act
    ViewResult result = controller.Index() as ViewResult;
 
    // Assert
    ViewDataDictionary viewData = result.ViewData;
    var movies = (List<Movie>)viewData.Model;
    Assert.AreEqual("Star Wars", movies[0].Title);
}

The code in Listing 5 contains two methods. The first method, named Initialize(), is called once before any of the other unit tests. This method initializes the fake DataContext with two movie records.

The second method, named TestIndex(), tests the Home controller Index() action. This method creates an instance of the HomeController by passing the FakeDataContext to the HomeController class’s constructor. Next, the HomeController.Index() method is called. Finally, the view data returned by calling the Index() method is examined to check for at least the Star Wars movie record. If the record is there, then the Index() method is assumed to have worked.

The unit test in Listing 6 demonstrates how you can unit test code that inserts new data into a database (see Figure 2). This unit test is run against the HomeController.Insert() method.

Figure 2 – Inserting a new record

clip_image004

Listing 6 – TestInsert()

[TestMethod]
public void TestInsert()
{
    // Arrange
    var controller = new HomeController(_fakeDataContext);

    // Act
    controller.Insert("Batman", "Burton");

    // Assert
    var table = _fakeDataContext.GetTable<Movie>();
    var newMovie = table.SingleOrDefault(m => m.Title == "Batman");
    Assert.IsNotNull(newMovie);
}

The unit test in Listing 6 creates a new instance of the HomeController class passing the fake DataContext to its constructor. Next, the HomeController.Insert() method is called with the values Batman and Burton. If the HomeControler.Insert() method correctly inserts the new record, then we should be able to retrieve the new record from the fake DataContext. The test attempts to retrieve the movie with the title Batman. If the movie record exists (it is not null) then the test succeeds.

One last demonstration of how you can use the FakeDataContext when building unit tests. The test in Listing 7 tests the Home controller’s Update() method.

Listing 7 – TestUpdate()

[TestMethod]
public void TestUpdate()
{
    // Arrange
    var controller = new HomeController(_fakeDataContext);

    // Act
    controller.Update(1, "Batman", "Burton");

    // Assert
    var table = _fakeDataContext.GetTable<Movie>();
    var newMovie = table.SingleOrDefault(m => m.Id == 1);
    Assert.AreEqual("Batman", newMovie.Title);
}

The test in Listing 7 calls the HomeController.Update() method passing the new values 1, “Batman”, and “Burton”. Next, the test verifies whether or not the movie record with an Id of 1 has been updated with the new values. If the movie has a title of Batman (instead of its original value Star Wars) then the test succeeds.

Summary

In this tip, I’ve demonstrated a very easy way of testing your LINQ to SQL code. Notice that we did not need to resort to creating a separate Repository class. You can use the LINQ to SQL DataContext directly within your controller actions and you can still test your controllers by taking advantage of the FakeDataContext.

Download the Code

Published Saturday, August 16, 2008 6:00 PM by swalther
Filed under: , ,

Comments

# re: ASP.NET MVC Tip #33 – Unit Test LINQ to SQL

Saturday, August 16, 2008 10:36 PM by Daniel

I've been playing with a similar idea, but it gets significantly more difficult if you're dealing with a real-world data context that has relationships between tables, etc.  Let's see this example with an actors table...

# re: ASP.NET MVC Tip #33 – Unit Test LINQ to SQL

Sunday, August 17, 2008 7:30 AM by swalther

@Daniel -- You can do simple joins with the FakeDataContext like this:

  var results = from m in movies

      from c in categories

      where m.CategoryId == c.Id

      select new { c.Name, m.Title };

This LINQ to SQL query works with the fake DataContext. But, your general point is well taken. I haven't experimented with entity associations yet, so I don't know whether they can be faked.

# re: ASP.NET MVC Tip #33 – Unit Test LINQ to SQL

Tuesday, August 19, 2008 10:30 PM by swalther

@Steve -- You were one of the people who inspired me to write the next tip on Disposing the DataContext. See weblogs.asp.net/.../asp-net-mvc-tip-34-dispose-of-your-datacontext-or-don-t.aspx

# re: ASP.NET MVC Tip #33 – Unit Test LINQ to SQL

Friday, August 29, 2008 7:30 AM by Alexis Kennedy

Grand tip - thanks.

This a brittle sort of solution, but I find that if you use the SQLMetal autogenerated entities alongside a FakeDataContext, the entity association stuff more or less just works (there are quirks). I imagine someone cleverer than me can come up with a way of making it less brittle.

# re: ASP.NET MVC Tip #33 – Unit Test LINQ to SQL

Saturday, August 30, 2008 10:52 PM by swalther

@Alexis - cool! I'll have to investigae how the FakeDataContext works with associations more deeply.

# re: ASP.NET MVC Tip #33 – Unit Test LINQ to SQL

Friday, September 12, 2008 1:45 PM by Miguel Ludert

Where are you getting this ITable<TEntity> class?  All I see is the non-generic ITable interface in System.Data.Linq.

# re: ASP.NET MVC Tip #33 – Unit Test LINQ to SQL

Thursday, September 18, 2008 3:54 AM by Mike

I'm late to this post, hope you will still read my comment.

"This means that we can’t swap the DataContext object for a fake DataContext in our unit tests."

To me that implies we should'nt even bother testing it. You should/could have classes that _use_ the datacontext, like the repository pattern. Those classes should be faked and the fakes just don't bother with datacontext at all. You swap in these fake classes in your tests, and use the real ones in production code.

When we are creating wrappers and such to fake out something that's not even based on an interface, where will we end. That's like testing httpcontext before we had system.web.abstractions, possible but not intended.

Thanks!

# re: ASP.NET MVC Tip #33 – Unit Test LINQ to SQL

Friday, September 19, 2008 4:44 PM by What About Thad?

This is exactly what I've been looking for since adopting LINQ to SQL. I was bothered by being coupled to DataContext. Even worse, having overlooked the usefulness of GetTable<TEntity>() I coupled a lot of business logic to the more specific subclass of DataContext generated for my entities. The DataContextWrapper solves both problems with only a few minor discomforts.

I'm curious though how one might go about accessing stored procedures as methods on the DataContext instance. I generally eschew stored procedures, but have just one that is hard to get around. The derived subclass of DataContext one generates with the designer makes this easy, but I haven't found a good way to expose it as a method through the DataContextWrapper.