Traditionally, when we use SQL string to done some work, we have to close the sql connection before the page being closed, I was wondering if I use Linq to do the data operations do I still need to close the connection manually?
A:
In code you need not open and close connections. However, IMO a LinqDataContext should be treated as a resource like SqlConnection or SqlCommand and should be a part of the using block so that you dispose it when not using. Even though I have read on MSDN that this is not necessary but it is a good practice.
Perpetualcoder
2008-12-30 17:14:21
While treating it as a resource is a good idea, there are situations when you can't Dispose it for deferred loading. That you can leave it without Disposing and not face harsh penalties is very useful, IMO.
Druid
2010-07-01 12:46:10
+3
A:
This is very similar to (but not quite a duplicate of) this question.
LINQ to SQL will open and close connections when it needs to - you don't really have to dispose of the DataContext. However, there are times when you can "trick" the context into leaving a connection open when you shouldn't - I personally like to dispose of it as a matter of course. See my answer to the other question for a bit more detail from Matt Warren of the LINQ to SQL team.
I don't know about the Entity Framework, however.
Jon Skeet
2008-12-30 17:17:12
You meant LinQ will close the connection before page closing? Or it closes connect when it done a query, I think if LinQ does it this way, the SQL connection could be open, close and re-open many times and it will slow down the page.
2008-12-30 17:26:44
LINQ to SQL closes the connection each time it does a query. Don't forget that this is only returning it to the connection pool - I suspect it will make *no* significant difference to the speed of your page. As ever, benchmark!
Jon Skeet
2008-12-30 18:23:00
+1
A:
I answered in detail a question about closing connections that you might find interesting here.
Microsoft has answered this question here:
Q. How long does my database connection remain open?
A. A connection typically remains open until you consume the query results. If you expect to take time to process all the results and are not opposed to caching the results, apply ToList<(Of <(TSource>)>) to the query. In common scenarios where each object is processed only one time, the streaming model is superior in both DataReader and LINQ to SQL.
The exact details of connection usage depend on the following:
Connection status if the DataContext is constructed with a connection object.
Connection string settings (for example, enabling Multiple Active Result Sets (MARS). For more information, see Multiple Active Result Sets (MARS).
More detail can be found here:
You can supply an existing ADO.NET connection when you create a LINQ to SQL DataContext. All operations against the DataContext (including queries) use this provided connection. If the connection is already open, LINQ to SQL leaves it as is when you are finished with it.
David B
2008-12-30 18:27:37