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:

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:

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:

1:using (IEnumerator<User>
enumerator =

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

3: {

4:

5:while (enumerator.MoveNext())

6: {

7:

8://
Do something here

9:

10: }

11:

12: }

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:

1:using (IEnumerator<User>
enumerator =

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

3: {

4:while (enumerator.MoveNext())

5: {

6:

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

8: enumerator.Current.id,

9: enumerator.Current.firstName);

10:

11://
Stop iterating after this record.

12:break;

13:

14: }

15:

16: }

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

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:

1: IEnumerator<User>
enumerator =

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

3:

4:while (enumerator.MoveNext())

5: {

6:

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

8: enumerator.Current.id,

9: enumerator.Current.firstName);

10:

11://
Stop iterating after this record.

12:break;

13:

14: }

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:

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);

18:

19:break;

20: }

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):