ASP.NET MVC Tip #34 – Dispose of Your DataContext (or Don’t)

In this tip, I demonstrate how you can dispose of a DataContext within an ASP.NET MVC controller. Next, I argue that there is no compelling reason to do this.

Several people have emailed me recently with the same question about whether or not it is important to call Dispose() on the DataContext object. In all of the code samples that I write, I create an instance of a DataContext object, but I never properly dispose of it. Is this wrong?

The DataContext class implements the IDisposable interface. In general, if a class implements the IDisposable interface, then that is good evidence that you should call Dispose(). But keep reading.

Classes that implement the IDisposable interface typically use resources that cannot be cleaned up by the .NET framework garbage collector. Calling the IDisposable.Dispose() method executes code that explicitly releases a precious resource back into the world.

A prime example of a class that implements the IDisposable interface is the SqlConnection class. A SqlConnection class uses a Microsoft SQL Server database connection. Because SQL Server supports a limited number of connections, it is important to release a connection as quickly as possible.

Typically, you do not call the Dispose() method directly. Typically, you take advantage of a Using statement in your code like this:

C# Code

using (var con = new SqlConnection(conString))
{
    var cmd = new SqlCommand("SELECT * FROM Products");
    var reader = cmd.ExecuteReader();
}

VB.NET Code

Using con = New SqlConnection(conString)
    Dim cmd = New SqlCommand("SELECT * FROM Products")
    Dim reader = cmd.ExecuteReader()
End Using

A Using statement calls the Dispose() method at the end of the Using block automatically. The Using statement calls the Dispose() method even if there was an error in the code.

Dispose of the DataContext

Because the DataContext implements IDisposable, it would seem like the right way to use the DataContext in a controller is like Listing 1. In Listing 1, a DataContext is used within a Using statement.

Listing 1 – BadController.cs

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

namespace Tip34.Controllers
{
    public class BadController : Controller
    {
        private IDataContext _dataContext;

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

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

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

    }
}

The controller in Listing 1 is taking advantage of the IDataContext, DataContextWrapper, and FakeDataContext objects discussed in the previous tip:

/blog/archive/2008/08/16/asp-net-mvc-tip-33-unit-test-linq-to-sql.aspx

By taking advantage of these objects, you can easily test your MVC application.

Within the Index() method, the DataContext is used within a Using statement. The Using statement causes the Dispose() method to be called on the DataContext object (The DataContextWrapper.Dispose() method delegates to the DataContext.Dispose() method).

You can use the controller in Listing 1 with a typed view. The typed view casts the ViewData.Model property to an instance of IQueryable. The code-behind for the typed view is contained in Listing 2.

Listing 2 — ViewsBadIndex.aspx.cs

using System.Linq;
using System.Web.Mvc;
using Tip34.Models;

namespace Tip34.Views.Bad
{
    public partial class Index : ViewPage<IQueryable<Movie>>
    {
    }
}

Unfortunately, however, the code in Listing 1 does not work. If you try to execute this code, you get the error displayed in Figure 1. You receive the error: “Cannot access a disposed object”. The problem is that the DataContext has already been disposed before the DataContext can be used in the view.

Figure 1 – Error from early disposal

One of the big benefits of using LINQ to SQL is that it supports deferred execution. A LINQ to SQL statement is not actually executed against the database until you start iterating through the results. Because the movie records are not accessed until they are displayed in the Index view, the DataContext is disposed before the records are retrieved from the database and you get the error.

One easy way to fix the problem with the controller in Listing 1 is to pass the movies as a list instead of as an IQueryable. The modified Index() action method in Listing 3 does not cause an error:

Listing 3 – HomeController.cs

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

namespace Tip34.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()
        {
            using (_dataContext)
            {
                var table = _dataContext.GetTable<Movie>();
                var movies = from m in table select m;
                return View("Index", movies.ToList());
            }
        }

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

The only difference between the Index() method in Listing 3 and the Index() method in Listing 4 is that the ToList() method is called on the movies before the movies are passed to the view.

The controller in Listing 3 also contains a Details() action that displays details for a particular movie. You do not need to do anything special when retrieving a single database record. LINQ to SQL does not used deferred execution when you retrieve a single record.

If you change the typed view so that it expects a list instead of an IQueryable, then everything works fine:

using System.Collections.Generic;
using System.Web.Mvc;
using Tip34.Models;

namespace Tip34.Views.Home
{
    public partial class Index : ViewPage<List<Movie>>
    {
    }
}

The modified controller now works because the ToList() method forces the LINQ to SQL statement to be executed against the database within the controller and not within the view.

You can unit test the Home controller in Listing 3 just fine. The class in Listing 4 contains a unit test that verifies that the Index() method returns a set of database records.

Listing 4 — ControllersHomeControllerTest.cs

using System.Collections.Generic;
using System.Web.Mvc;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using MvcFakes;
using Tip34.Controllers;
using Tip34.Models;

namespace Tip34Tests.Controllers
{
    [TestClass]
    public class HomeControllerTest
    {
        [TestMethod]
        public void Index()
        {
            // Create Fake DataContext
            var context = new FakeDataContext();
            var table = context.GetTable<Movie>();
            table.InsertOnSubmit(new Movie(1, "Star Wars"));
            table.InsertOnSubmit(new Movie(2, "King Kong"));
            context.SubmitChanges();

            // Create Controller
            var controller = new HomeController(context);

            // Act
            ViewResult result = controller.Index() as ViewResult;

            // Assert
            var movies = (List<Movie>)result.ViewData.Model;
            Assert.AreEqual("Star Wars", movies[0].Title);
        }

    }
}

This unit test is taking advantage of the FakeDataContext class described in the previous tip to create an in-memory version of a DataContext. First, the unit test adds some fake database records to the FakeDataContext. Next, the Index() action is invoked and a result is returned. Finally, the ViewData contained in the result is compared against the fake database records. If the ViewData contains the first fake database record then the Index() action works correctly and success is achieved.

Don’t Dispose of the DataContext

In the previous section, I demonstrated how you can Dispose() of a DataContext within an MVC controller action. In this section, I question the necessity of doing this.

When you call the DataContext Dispose() method, the DataContext delegates the call to the SqlProvider class. The SqlProvider class does two things. First, the SqlProvider calls the ConnectionManager.DisposeConnection() method which closes any open database connection associated with the DataContext. Second, the SqlProvider sets several objects to null (including the ConnectionManager).

So, the most important consequence of calling the DataContext.Dispose() method is that any open connections associated with the DataContext get closed. This might seem really important, but it’s not. The reason that it is not important is that the DataContext class already manages its connections. By default, the DataContext class opens and closes a connection automatically.

The DataContext object translates a LINQ to SQL query into a standard SQL database query and executes it. The DataContext object opens a database connection right before executing the SQL database query and closes the connection right after the query is executed (the connection is closed in the Finally clause of a Try..Catch block).

If you prefer, you can manage the opening and closing of the database connection used by a DataContext yourself. You can use the DataContext.Connection property to explicitly open a connection and explicitly close the connection. Normally, however, you don’t do this. You let the DataContext take care of itself.

Most often, when you call the Dispose() method on the DataContext object, any database connection associated with the DataContext object is already closed. The DataContext object has closed the database connection right after executing the database query. So, the Dispose() method really doesn’t have anything to do.

Therefore, you really don’t get any huge benefits from calling Dispose() on the DataContext object. The only slight benefit is that the Dispose() method sets several objects to null so that they can be collected earlier by the garbage collector. Unless you are worried about every byte used by your application, or you are tracking a huge number of objects with your DataContext, I wouldn’t worry too much about the memory overhead of the DataContext object.

Summary

In this tip, I demonstrated how you can call the Dispose() method when working with a DataContext object within an MVC controller. Next, I argued that there really isn’t a very compelling reason to want to call the Dispose() method. I plan to continue to be lazy about disposing any DataContext objects that I used within my MVC controllers.