How do I correctly manage the disposing of a DataContext?

Question

I have a web service that is quite heavy on database access. It works fine in test, but as soon as I put it in production and ramp up the load it starts churning out errors that are raised when something calls a method in the DataContext. The error is normally one of these:

Object reference not set to an instance of an object

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

but not always.

Any single web service requests can result as many as 10 or 15 database queries, and 1 or 2 updates.

I've designed my application with a data access layer which is a bunch of objects that represent the tables in my database which hold all the business logic. The is a separate project to my web service as it's shared with a Web GUI.

The data access objects derive from a base class which has a GetDataContext() method to initiate an instance of the data context whenever it's needed.

All throughout my data access objects I've written this:

using (db = GetDataContext())
{
    // do some stuff
}

which happily creates/uses/disposes my DataContext (created by sqlmetal.exe) object for each and every database interaction.

After many hours of head scratching, I think I've decided that the cause of my errors is that under load the datacontext object is being created and disposed way too much, and I need to change things to share the same datacontext for the duration of the web service request.

I found this article on the internet which has a DataContextFactory that seems to do exactly what I need.

However, now that I've implemented this, and the DataContext is saved as an item in the HttpContext, I get...

Cannot access a disposed object.

Object name: 'DataContext accessed after Dispose.'

...whenever my datacontext is used more than once. This is because my using (...) {} code is disposing my datacontext after its first use.

So, my question is... before I go through my entire data access layer and remove loads of usings, what is the correct way to do this? I don't want to cause a memory leak by taking out the usings, but at the same time I want to share my datacontext across different data access objects.

Should I just remove the usings, and manually call the dispose method just before I return from the web service request? If so then how go I make sure I capture everything bearing in mind I have several try-catch blocks that could get messy.

Is there another better way to do this? Should I just forget about disposing and hope everything is implicitly cleaned up?

UPDATE

The problem doesn't appear to be a performance issue... requests are handled very quickly, no more than about 200ms. In fact I have load tested it by generating lots of fake requests with no problems.

As far as I can see, it is load related for one of two reasons:

• A high number of requests causes concurrent requests to affect each other
• The problem happens more frequently simply because there are a lot of requests.

When the problem does occur, the application pool goes into a bad state, and requires a recycle to get it working again.

Answer 1

Although I would prefer the unit-of-work approach using using, sometimes it doesn't always fit into your design. Ideally you'd want to ensure that you are freeing up your SqlConnection when you're done with it so that anothe request has a chance of grabbing that connection from the pool. If that is not possible, what you would need is some assurance that the context is disposed of after each request. This could be done a couple of ways:

1. If you're using WebForms, you can tie the disposal of the DataContext at the end of the page lifecycle. Make a check to the HttpContext.Items collection to determine if the last page had a data context, and if so, dispose of it.

2. Create a dedicated IHttpModule which attaches an event to the end of the request, where you do the same as above.

The problem with both of the above solutions, is that if you are under heavy load, you'll find that a lot of requests hang about waiting for a connection to be made available, likely timing out. You'll have to weigh up the risks.

All in all, the unit-of-work approach would still be favoured, as you are releasing the resource as soon as it is no longer required.

Answer 2

I managed to fix this myself...

I had a base class that had a method that would create the DataContext instance, like this:

public abstract class MyBase {

    protected static DataContext db = null;

    protected static DataContext GetDataContext() {
        return new DataContext("My Connection String");
    }

    // rest of class
}

And then, in the classes that inherited MyBase where I wanted to do my queries, I had statements like this:

using (db = GetDataContext()) { ... }

The thing is, I wanted to access the database from both static methods and non-static methods, and so in my base class, I'd declared the db variable as static... Big mistake!

If the DataContext variable is declared as static, during heavy loads when lots of things are happening at the same time the DataContext is shared among the requests, and if something happens on the DataContext at exactly the same time it screws up the instance of the DataContext, and the Database connection stored in the Application pool for all subsequent requested until it's recycled, and the database connection is refreshed.

So, the simple fix is to change this:

protected static DataContext db = null;

to this:

protected DataContext db = null;

...which will break all of the using statements in the static methods. But this can easily be fixed by declaring the DataContext variable in the using instead, like this:

using (DataContext db = GetDataContext()) { ... }