LINQ: When are my database connections closed?

Greg Varveris — Fri, 23 Jul 2010 07:02:43 GMT

So, I was working with a customer who is writing their first application using LINQ. They had previously been bitten by the failure to close and dispose their SqlConnection objects. This is actually a fairly common problem and usually leads to those pesky SqlExceptions detailing that there are no connections left in the pool.

So, since LINQ to SQL abstracts out much of the direct database interaction, they were concerned about when the underlying SqlConnections are closed. I will walk through how I answered their question using a few of my favorite tools:

To start off, I created a simple SQL Table called Users:

Then, I created a simple LINQ to SQL dbml:

Now that the plumbing is in place, I can write some simple code to return the data from the table and display it to the console window:

 1: LinqConnectionSampleDataContext
db = 

 2: new LinqConnectionSampleDataContext();

 3:  

 4: Table<User>
users = db.GetTable<User>();

 5:  

 6: IQueryable<User>
userQuery =

 7: from
user in users

 8: orderby
user.firstName

 9: select
user;

 10: 

 11:  

 12: foreach (User
user in userQuery)

 13: {

 14:  

 15: Console.WriteLine("ID={0},
First Name={1}", 

 16: user.id, 

 17: user.firstName);

So, now when the application is executed, the output is as follows:

So, since Linq to Sql uses an underlying SqlConnection to do its work, we can set a breakpoint on the Close() method of that class in WinDBG. If you are unfamiliar with this great debugging tool, you can find a simple walkthrough on how to set it up here.

There are a number of ways to set a breakpoint in managed code in WinDBG. Here are the steps that I followed:

Step 1. Launch WinDBG and attach to the process in question.

Step 2. Load the SOS extension into WinDBG by executing:

.loadby sos mscorwks

Step 3. Set the managed breakpoint using the !bpmd command. For this step, the !bpmd command accepts a variety of parameters. Basically, you can pass it either:

MethodDescr address.
Combination of Module Name and Managed Function Name

I chose the latter method because it’s relatively quick and I knew exactly what I wanted. So, the syntax for this method is:

!bpmd <module name> <managed function name>

You can get the module name from visiting the SqlConnection page up on MSDN. On this page, we can get the module name and the namespace to the class:

From this, we can get both parameters necessary:

Module Name: System.Data.dll
Managed Function Name: System.Data.SqlClient.SqlConnection.Close

So, our command in WinDBG becomes:

 1: !bpmd
System.Data.dll 

 2: System.Data.SqlClient.SqlConnection.Close

Once you enter in this command, you should get output similar to the following in the WinDBG window:

0:014> !bpmd System.Data.dll System.Data.SqlClient.SqlConnection.Close
Found 1 methods...
MethodDesc = 544a0418
Setting breakpoint: bp 5455DC80 [System.Data.SqlClient.SqlConnection.Close()]

Step 4. “Go” in the debugger and wait for your breakpoint to be hit.

For this, the command is simply “g”.

0:014> g

Eventually, your breakpoint will be hit in the debugger and you should get output similar to the following:

Breakpoint 0 hit
eax=5457da68 ebx=04d7e9dc ecx=0185cd30 edx=018e56b0 esi=01870d80 edi=04d7e9a4
eip=5455dc80 esp=04d7e860 ebp=04d7e868 iopl=0         nv up ei pl nz na po nc
cs=001b  ss=0023  ds=0023  es=0023  fs=003b  gs=0000             efl=00000202
System_Data_ni+0xcdc80:
5455dc80 55              push    ebp

Step 5. Print out the call-stack.

The command to print out the call stack in SOS and WinDBG is “!clrstack”:

0:008> !clrstack

This will print out the managed call stack, which turns out to be:

OS Thread Id: 0x1d70 (8)
ESP       EIP     
04d7e860 5455dc80 System.Data.SqlClient.SqlConnection.Close()
04d7e864 77e20586 System.Data.Linq.SqlClient.SqlConnectionManager
                    .CloseConnection()
04d7e870 77e20554 System.Data.Linq.SqlClient.SqlConnectionManager
                    .ReleaseConnection(...)
04d7e87c 77e1da35 System.Data.Linq.SqlClient.
        ObjectReaderCompiler+ObjectReaderSession`1[...].Dispose()
04d7e888 77e1ddac System.Data.Linq.SqlClient.
        ObjectReaderCompiler+ObjectReaderSession`1[...].CheckNextResults()
04d7e894 77e1df2c System.Data.Linq.SqlClient.
        ObjectReaderCompiler+ObjectReaderBase`1[...].Read()
04d7e8a0 77e1ea2d System.Data.Linq.SqlClient.
        ObjectReaderCompiler+ObjectReader`2[...].MoveNext()
04d7e8ac 004f1a12 LINQ.SqlConnection.Program.Main(System.String[])

So, if you’re having trouble parsing this, the take away here is that when you iterate through a Linq resultset and you get to the end, the ObjectReaderSession will automatically close the Connection to the database.

Now, this is a simple HelloWorld code sample for retrieving a result-set and there are obviously a number of ways to do the same thing. The customer’s code was closer to the following:

using (IEnumerator<User>
enumerator = 

context.ExecuteQuery<User>(sqlStatement).GetEnumerator())

{

  

while (enumerator.MoveNext())

{

  

//
Do something here

  

}

  

}

In this situation, we get an IEnumerator<T> back from the database call and iterate through it. Now, this part is very important. If you are iterating through the result set to completion – the connection will be closed the same as the above. However, if you do something like this:

using (IEnumerator<User>
enumerator = 

db.ExecuteQuery<User>(sqlStatement).GetEnumerator())

{

while (enumerator.MoveNext())

{

  

Console.WriteLine("ID={0},
First Name={1}", 

enumerator.Current.id, 

enumerator.Current.firstName);



//
Stop iterating after this record.

break;

  

}

  

}

Please note the “break” statement. Essentially, if you are NOT iterating through to completion, the call stack looks like:

OS Thread Id: 0x251c (11)
ESP       EIP     
0522e73c 5455dc80 System.Data.SqlClient.SqlConnection.
                    Close()
0522e740 77e20586 System.Data.Linq.SqlClient.SqlConnectionManager.
                    CloseConnection()
0522e74c 77e20554 System.Data.Linq.SqlClient.SqlConnectionManager.
                    ReleaseConnection(...)
0522e758 77e1da35 System.Data.Linq.SqlClient.ObjectReaderCompiler+
                    ObjectReaderSession`1[...].Dispose()
0522e764 77e1ea12 System.Data.Linq.SqlClient.ObjectReaderCompiler+
                    ObjectReader`2[...].Dispose()
0522e768 00691bde LINQ.SqlConnection.Program.Main(System.String[])

The connection will NOT be closed until you call Dispose() on the ObjectReader (IEnumerable) object. This means that if you happen to write some code without the Using… statement when returning data like this:

IEnumerator<User>
enumerator =

db.ExecuteQuery<User>(sqlStatement).GetEnumerator();

  

while (enumerator.MoveNext())

{

  

Console.WriteLine("ID={0},
First Name={1}",

enumerator.Current.id,

enumerator.Current.firstName);

  

//
Stop iterating after this record.

break;

  

}

The SqlConnection.Close() method will NOT be called. This is because you have full control over the lifetime of the IEnumerator<T> object and you should know when you are done with it.

Now, along those lines, you may be asking yourself – what if I did something like this:

LinqConnectionSampleDataContext
db = 

new LinqConnectionSampleDataContext();

  

Table<User>
users = db.GetTable<User>();

  

IQueryable<User>
userQuery =

from
user in users

orderby
user.firstName

select
user;



  

foreach (User
user in userQuery)

{

  

Console.WriteLine("ID={0},
First Name={1}", 

user.id, 

user.firstName);

  

break;

}

Where you break before you iterate through to completion? In that situation, Dispose() will still be called on the IQueryable<T> object. How? Because of a compile-time optimization we do. We insert a finally statement after the userQuery has been used. This compiles down to (in IL):

 try{

L_005d: br.s L_0084 L_005f: ldloc.s CS$5$0002 L_0061: callvirt instance !0...get_Current()
L_0066: stloc.3 L_0067: ldstr "ID={0}, First Name={1}" L_006c: ldloc.3 L_006d:
callvirt instance int32 LINQ.SqlConnection.User::get_id() L_0072: box int32 L_0077:
ldloc.3 L_0078: callvirt instance string LINQ.SqlConnection.User::get_firstName()
L_007d: call void [mscorlib]System.Console::WriteLine(string, object, object) L_0082:
br.s L_008d L_0084: ldloc.s CS$5$0002 L_0086: callvirt instance bool [mscorlib]System.Collections.IEnumerator::MoveNext()
L_008b: brtrue.s L_005f L_008d: leave.s L_009b

}finally{

L_008f: ldloc.s CS$5$0002 L_0091: brfalse.s L_009a L_0093: ldloc.s CS$5$0002 L_0095:
callvirt instance void [mscorlib]System.IDisposable::Dispose() L_009a: endfinally

} L_009b: ret .try L_005d to L_008f finally handler L_008f to L_009b

The text in red is my emphasis. So, the moral of this story, when you take control of the data yourself, you MUST call dispose on the IEnumerable<T> object when you are done with it.

Enjoy!

Samurai Programmer.com - LINQ

LINQ: When are my database connections closed?

Step 1. Launch WinDBG and attach to the process in question.

Step 2. Load the SOS extension into WinDBG by executing:

Step 4. “Go” in the debugger and wait for your breakpoint to be hit.

Step 5. Print out the call-stack.