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()

        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()

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"));

    // 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);

            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;
            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


Listing 5 – TestIndex()

private FakeDataContext _fakeDataContext;
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"));
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


Listing 6 – TestInsert()

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");

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()

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.


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


  1. http:// says:

    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…

  2. http:// says:

    @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.

  3. Hmm, I would probably not create the DataContextWrapper in the constructor for the Controller. Rather, I’d create it using an IoC container such as Unity right in your controller action and then have the wrapper implement IDisposable. While what I can find suggests not disposing of a Datacontext isn’t terrible, the fact is that DataContext does implement IDisposable, and as such you should make sure it’s properly disposed of.

    And as I delve into this more, and start thinking about how this would work, it starts to remind me of an article I read a few months back……/fixing-leaky-repository-ab…

  4. http:// says:

    @Steve — You were one of the people who inspired me to write the next tip on Disposing the DataContext. See…/…r-datacontext-or-don-t.aspx

  5. http:// says:

    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.

  6. http:// says:

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

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

  8. http:// says:

    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.


  9. http:// says:

    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() 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.

  10. Dean Nolan says:

    I have tried using this in my project and I can’t get the SqlMethods.Like method to work with it.

    I get the error:
    System.NotSupportedException: Method ‘Boolean Like(System.String, System.String)’ cannot be used on the client; it is only for translation to SQL.

    Do you have any information to solve this?

  11. MOV to DVD says:

    Great question! In Firefox, the __proto__ magic property is read/write as demonstrated by the following script:

    var proto1 = {doSomething: function(){alert(‘boom’)} };
    var proto2 = {doSomething: function(){alert(‘bang’)} };

  12. wow.. interesting post thanks!!!!

  13. Diet Pills says:

    very nice post thanks 😉

  14. San says:

    I am getting an error

    Value cannot be null.
    Parameter Name: fileOrServerOrConnection.

    Any ideas?

  15. #@ i prefer to use what is expert said….so thanks a lot stephen.

  16. very nice info thanks!!!!

  17. n53w I tried to mock a call to a Linq to SQL query, but I am struggling.

  18. Thats pretty much the same thing I’ve been looking for, thanks Admin!

  19. Best Dates says:

    I like this post, very informative

  20. Degrees says:

    Somebody could fake our version field and effective force a last one wins even if we don’t want this behavior.Hope we can do long-term business in near future.
    online phd degree | criminal justice degree | life experience degrees

  21. Degrees says:

    Dear friend We can offer all kinds of cigarettes. We offer safe delivery and best service,low price and good quality.
    nursing degree | business management degree

  22. ED tratments says:

    Hi Im few months ago was really worried about my sex life but now Im using some ED treatments that i find online and I fell great

  23. hello viagra it give an erection for hours and now is more easy to get you can get it on line

  24. h;, It looks like DataContextExtensions.cs line 45 of the Save method should pass the primaryKeyName through to Update.

  25. Balakrishna Chede says:


    Now can’t we use the FakeContext Object in Unit Tests, if we have associations in our Entities. ?

    Please suggest.

  26. Hello
    cool this blog is great very good my friend keep it going. thanks for sharing the information about Unit Test LINQ to SQL.

  27. Balakrishna Chede says:


    Is there any workaround to Unit Test the entities that have associations.


  28. Buy Viagra says:

    I hope it serves as helped me, this blog is too good and useful for you to leave your comments on the subject pair to see if I lie.

  29. HD Video Converter says:

    As the users of HD Camcorders like Sony, Canon, Panasonic, this HD Converter is necessary to help us convert hd Video easily and quickly. The Converter for HD provides several practical editing functions to help you achieve ideal output effect. Trim function is to cut videos into clips which you can just convert and transfer to your player. Crop function helps you remove black bars around the movie. You could use Effect function to adjust video brightness, contrast, saturation and more parameters. More powerful and considerate functions are waiting for you to explore.MKV Converter l FLV Converter l DVD Ripper !