LeeDumond.com

It seems there is a lot of confusion amongst .NET developers about whether or not to dispose the DataContext in LINQ to SQL. That being the case, I'll throw in my two cents, and in the process hopefully not confuse the issue even more.

As you probably know, it’s considered good programming practice that anything that implements IDisposable should be disposed explicitly, either by calling Dispose() or by wrapping in a using block, like this:

   1: public int GetThingCount()

   2: {

   3:    using (MyDataContext context = new MyDataContext())

   4:    {

   5:       return context.Things.Count();

   6:    }

   7: }

However, as you seek out examples around the 'net, you are just as likely to see something like this:

   1: public int GetThingCount()

   2: {

   3:    MyDataContext context = new MyDataContext();

   4:    return context.Things.Count();  

   5: }

So, what’s the deal—should we dispose or not? As I said, there's a lot of debate and confusion about this. One reason is that we must be very particular about explicitly disposing “expensive” resources like open database connections, like this:

   1: public int GetThingCount()

   2: {

   3:    using (SqlConnection cn = new SqlConnection(this.ConnectionString))

   4:    {

   5:       SqlCommand cmd = 

   6:          new SqlCommand("sproc_GetThingCount", cn);

   7:          cmd.CommandType = CommandType.StoredProcedure;

   8:          cn.Open();

   9:          return (int)cmd.ExecuteScalar();

  10:    }

  11: }

Remember that all objects are eventually disposed by the .NET garbage collector anyway. But because there’s no way to determine how long that might take, failing to explicitly dispose of a manually-opened database connection could keep it open longer that we want, drastically affecting how many connections are available to our users.

However, the DataContext is smart about the way it manages database connections. It only connects when the query is actually enumerated, or when SubmitChanges() is called (in the case of a database update), and closes the connection immediately upon completion of its task. As a result, DataContext objects are very light, so it really isn’t that critical to dispose them manually. This is especially true for read-only operations.

Keep in mind, however, that the DataContext isn't merely a connection object -- it also tracks changes we make to our entities for updating purposes. In that case, wrapping your DataContext in a using block could free up a little bit of memory a bit sooner.

Bottom line: don't sweat it too much.

Disposing and Deferred Loading

Personally, I generally make a practice of wrapping the DataContext in a using block because, hey, that's just how I roll. If you do this, however, be aware of any scenarios in which deferred loading might take place.

Don't forget, LINQ to SQL is smart -- it only gets the data your application actually requests, nothing more. LINQ to SQL classes map the foreign key associations among your database tables using special properties. When a query is executed and data is returned, by default only the “native” fields are actually fetched from the database. If you later request information from a related table, LINQ to SQL enumerates the query again, issuing a separate command to the database. This is what is meant by deferred loading.

If you have a scenario in which your application code calls for data from a related table after the fact, your app will attempt to return to the original DataContext to fetch the new data. If you haven't disposed the DataContext, deferred loading kicks in (this takes place transparently behind the scenes, of course). But if you've wrapped that rascal in a using block, guess what happens? Yep, that's right:

System.ObjectDisposedException: Cannot access a disposed object.
Object name: 'DataContext accessed after Dispose.'.

Ouch. In this case, you have two options. Option 1 is to take the DataContext out of the using block and let it die a natural death at the hands of the garbage collector. That way, when your code needs that related table data, the DataContext will still be available. This is the easier solution.

Option 2 is to to tell the DataContext to “forget about all that deferred loading stuff this time, and just get me my main table and its related table data at the same time." In other words, disable deferred loading as part of the original query.

   1: public static IEnumerable<Thing> GetThings()

   2: {

   3:    using (MyDataContext context = new MyDataContext())

   4:    {

   5:       context.DeferredLoadingEnabled = false;

   6:       DataLoadOptions loadOptions = new DataLoadOptions();

   7:       loadOptions.LoadWith<Thing>(t => t.RelatedThing);

   8:       context.LoadOptions = loadOptions;

   9:  

  10:       return context.Things.ToList();

  11:    }                  

  12: }

This way, the main table and the related table data are all fetched at once, Thus we can be assured that the DataContext won't be needed again, in which case we can safely dispose it.

For More Information

You will find a lot more information about safely handling the DataContext, and how deferred loading and deferred execution work  (including detailed explanations and working examples) in my Wrox Blox eBook: LINQifying TheBeerHouse: An N-Tier LINQ Web Application with ASP.NET 2.0 Website Programming Problem - Design - Solution  (ISBN: 978-0-470-41569-6). It's available for download direct from Wrox for $6.99.

Subscribe to this blog for more cool content like this!

Bookmark / Share