LINQ to SQL and SqlConnection

我能理解文章的意思，但是怕翻译不好，就直接贴过来，提供给需要的人！

I was talking to Ian and another chap at the UK Launch yesterday about LINQ to SQL and my use of the DataContext in some demo code where I always tend to write something like;

      using (DataContext ctx = new DataContext())
      {
      }

and whether I actually needed to do that dispose or not?

Now...I was pretty confident that I do need to do this for two reasons;

1. DataContext is disposable so I figure I should probably Dispose() of it - generally been my approach to anything IDisposable.
2. Someone had recently asked me whether DataContext.Dispose() actually did close the SQL connection and I'd done a little bit of reflecting on it and spotted that;
   1. DataContext.Dispose() -> SqlProvider.Dispose() -> SqlConnectionManager.DisposeConnection() -> SqlConnection.Close()

although the code paths are more complex than that and I haven't attempted to figure out under what conditions the various calls happen.

So, I'd kind of reasoned that;

1. If you don't dispose of the DataContext then you won't get the connection closed and back in the pool until finalisation time which is generally a bad idea.

but then I started to wonder whether that assumption was right and it turns out that it isn't really right but then it's not exactly wrong either :-)

So, to test this all out I did a bit of debugging and wrote;

 using (NorthwindDataContext ctx = new NorthwindDataContext())
      {
        (from c in ctx.Customers select c).ToList();

        (from c in ctx.Customers select c).ToList();

        (from c in ctx.Customers select c).ToList();
      }

and ran it under the debugger with breakpoints in SqlConnection.Open() and SqlConnection.Close().

I was foxed for a while because I see 5 calls to Open() and 5 calls to Close(). I was expecting either;

1. 1 call to Open and 1 to Close.
2. 3 calls to Open and 3 to Close.

A quick look at the call-stacks showed that it seems like when we hit the first query to be evaluated we first do;

which looks like we're opening the connection in order to test if it's SQLCE or not. Then we see a Close followed by;

So...a similar check to see if it's SQL 2000 or earlier followed by a Close.

So...following that I see an Open/Close pairing around each query that I execute which suggests that, in an ideal world, there'd be no need to dispose of the context because the connection is being opened/closed on demand every time we do a query. The Close calls that I see are coming from;

That is - they are not coming from Dispose on the DataContext but, instead, they are coming from the Dispose on the ObjectReaderCompiler.ObjectReader. Who he?

Well, it looks like he's the IEnumerator that's returned for my foreach iteration and he's IDisposable so if he gets disposed (i.e. when you've read all the data) then he closes the connection. He's getting disposed here because foreach is being friendly but it's not hard to come up with a situation where that might not happen;

So...I should be able to defeat that with something that leaves the enumerator kicking around like;

 using (NorthwindDataContext ctx = new NorthwindDataContext())
      {
        var query = from c in ctx.Customers
                    select c;

        var enumerator = query.GetEnumerator();
      }

and I finally see the DataContext stepping in and disposing and ultimately closing the connection;

Now, arguably I should be disposing of the enumerator here and that's solve the problem of the open connection again but really that just goes back to the original idea which is "if it's disposable, dispose it" so I've really just moved the decision from one disposable type to another.

Additionally, I'd imagine that there are lots of other places where not disposing the DataContext could mean that you wait until a finalizer runs in order to get a connection back into the pool so it feels like the right thing to do is always to Dispose() to be safe rather than sorry.

There's a FAQ up here about LINQ to SQL;

http://msdn2.microsoft.com/en-us/library/bb386929.aspx

which has a section in it about the lifetime of connections so that'd be the right place to go for a definitive answer.

I like simple things so I'm going to stick with my basic law of "If it's disposable, dispose of it" :-)