Connection Timeout Using LINQ DataContext

For a while now, I’ve had a website that was sporadically encountering the following error:

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

My Research 
On the website in question, I use LINQ-to-SQL heavily to perform data operations. Naturally, I suspected that I wasn’t disposing of my DataContext objects correctly, causing me to exceed the number of connections available in the pool. After doing a little research on the subject, I read in several articles (some by folks who are much more knowledgeable than myself) that I shouldn’t have to dispose of them. They all said the following code is just fine:

CORDataContext mcon = new CORDataContext();
COR_Employee emp = mcon.COR_Employees.FirstOrDefault();

In fact, I should be able to put that into an infinite loop and never get the exception I was encountering. According to the experts, LINQ is not only smart enough to automatically open and close the connection for me, but also smart enough to dispose of the object when I’m done. No need for a “using” around my code or even a Connection.Close().

An Exception to the Rule

I noticed, though, that my code was slightly different. In my specific setup, I have a central database and a LOT of other databases I might want to connect to. So, to take advantage of connection pooling more, I always connect to the central database first and change to whatever catalog I need to access. In doing so, it is necessary to manually open the connection, like the following:

CORDataContext mcon = new CORDataContext();mcon.Connection.Open(); mcon.Connection.ChangeDatabase("OtherDB");
COR_Employee emp = mcon.COR_Employees.FirstOrDefault();

My Testing

I began to theorize that manually opening the connection in this way keeps the connection alive after I’ve run my query. I confirmed the belief by trying to run the following code:

for (int i = 0; i < 1000; i++) {

    CORDataContext mcon = new CORDataContext();
    COR_Employee emp = mcon.COR_Employees.FirstOrDefault();
    mcon.Connection.Open();
    mcon.Connection.ChangeDatabase("OtherDB");

    Console.WriteLine("" + (i + 1) + " / 1000");
}

I got the exception after 230 or so connections. By default there are 100 connections available in the pool, but the garbage collector kicked in and helped give me a buffer before I ran out. So, from that I can conclude that manually opened DataContext connections will stay open until the connection is closed or the DataContext is disposed.

The Solution

Once I fully understood the problem, the fix was common sense:

for (int i = 0; i < 1000; i++) {

    using (CORDataContext mcon = new CORDataContext()) {
        COR_Employee emp = mcon.COR_Employees.FirstOrDefault();
        mcon.Connection.Open();
        mcon.Connection.ChangeDatabase("OtherDB");

        Console.WriteLine("" + (i + 1) + " / 1000");
    }
}

Adding a “using” to automatically dispose of my DataContext did the trick, and this loop now finishes in about 3 seconds (even with the overhead of writing to the console). I hope this helps someone else, because it’s been plaguing me for weeks!