Should the LINQ context be closed every time?

Question

Hello,

In LINQ to SQL, is it necessary to close the context after performing a select on the database (and of course, after consuming the data)? if I leave it open, doesn't it mean that the connection to the server is left open?

Thanks, Lucian

Answer 1

The underlying connection is not closed and disposed until you close the DataContext, using Dispose method. You should always call Dispose after using the DataContext. Think of the DataContext as a traditional Connection object, that is more or less what it is under the hood.

Answer 2

I am a bit uncertain about the names of everything in LiNQ. In entity framework it means that the object context is trying to do change tracking which in case of a disconnected scenario causes alot of problems.

ASP.NET is unfortunately not very compatible with change tracking today so closing the context after usage is the best way to handle entity framework in asp.net. If I try to leave tyhe context open I sometimes have problems updating entities I believe it should be the same issue in Linq.

In a Windows Forms application with the database on the same machine or in a local network I would reuse the object context through a facade (less code duplication) to be able to benefit from the change tracking and direct connection to the database.

Answer 3

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(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).

---

from MSDN LINQ to SQL FAQ

Answer 4

The short answer is yes, you should open a datacontext only when it's needed and close the it as soon as possible. If you don't dispose of it, the underlying database connection may stay open.

There have been many other people asking similar questions already. You'll find some pretty good discussions if you check some of them out.