Linq DataContext and Dispose

A question many developers have with Linq and databases is this:

How important is it to call Dispose on a DataContext object?

Normally a quick web search gives an easy answer to common questions like this.  But sometimes Google fails us.  It turns out so many people have attempted to write about this with wrong or incomplete information that a web search returns mostly junk.

Here is a definitive answer, which I have finally learned after reading down to comment one hundred and twenty seven :) on a blog post by ScottGu, who runs the ASP.NET team at Microsoft.

Short answer:  It is generally not critical to call Dispose on Datacontext.

Longer answer: 
All objects in .NET are eventually disposed automatically by the automatic garbage collector.  The reason developers are paranoid about calling it explicitly is that if an object contains an expensive resource like an open database connection, then we can't afford to wait around for garbage collection, it would have a big impact on scalability.

The good news is that DataContext objects do not keep open database connections like some ADO.Net objects do, so it doesn't really hurt to let the garbage collector do it for you.

There is one caveat here though:  The DataContext does track change states for the data, and by disposing it you would release this memory more quickly.  In almost all apps this difference wont be significant.  However if you have a web site that is using all memory available this is one of many optimizations you could make.

Btw, a gigabyte of expensive ECC server memory is going for as little as 30 bucks now, so if your server is strained you can help it out pretty cheaply.

Comments

[Raj]

Nice article,
I still face one problem. Sometime i get error is, Data Reader is already open. Im using LINQ to SQL. and im using static object of DataContext. is it right approch to use Static Datacontext Variable?

[Raj]

Invalid attempt to call Read when reader is closed.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: Invalid attempt to call Read when reader is closed.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[InvalidOperationException: Invalid attempt to call Read when reader is closed.]
System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult) +812
System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries) +129
System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query) +878
System.Data.Linq.DataQuery`1.System.Linq.IQueryProvider.Execute(Expression expression) +106
System.Linq.Queryable.FirstOrDefault(IQueryable`1 source) +267
Xpertz.BLL.cCity.GetCityById(Int32 cityId) in F:\Hill\Xpertz\BLL\cCity.cs:88
Xpertz.BLL.cMenu.GetMenuForCity(Int32 cityId) in F:\Hill\Xpertz\BLL\cMenu.cs:10
Hill.City.Page_Load(Object sender, EventArgs e) in F:\Hill\Hill\City.aspx.cs:21
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +15
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +33
System.Web.UI.Control.OnLoad(EventArgs e) +99
System.Web.UI.Control.LoadRecursive() +47
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1436

[John Rusk]

Thanks for digging into the details of this issue, and documenting the results.

I had a look for the ScottGu post. Was this the one: http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx ?

[Pieter Jouber]

There is one error in your logic.
You say the garbage collector always eventually dispose objects.
This is incorrect, it only does so most of the time.

If the process (program) is removed from memory, (E.G you close a windows app) the whole program is removed from memory without going through normal garbage collections. If the only recources is memory recources it is no problem, but SQL server connections can be left open.

A bigger problem is if you use ASP.Net each request is treated like this and you can end up using the maximum number of connections very quickly if the connections is not disposed.

To mitigate this with linq, linq to entities and linq to sql will usually open the connection when it needs it and close it immediatly afterwards. So your SQL connections don't leak even when you don't dispose the context.

There is one more problem with the linq scenario, sometimes the connection is not closed immediatly after a call to a quary, because the result object is still available, now this will be disposed by the garbage collector, but if the request close before it is finally disposed there can still be a leak of connections.

The moral of the story, it will probably be ok not to dispose a data context, but to be save you need to dispose it.

[Mike]

The information given here is incorrect.

See this post:
http://stackoverflow.com/questions/1094443/is-mixing-ado-net-and-linq-to-sql-bad-my-data-layer-isnt-working

and this one:
http://stackoverflow.com/questions/318716/asp-net-mvc-iis6-error-on-high-traffic-specified-cast-is-not-valid

and this one:
http://stackoverflow.com/questions/780447/is-it-possible-to-gets-sql-connection-leaks-using-linq/780669#780669

[Matt]

This information is fine! Read the comments at this answer: http://stackoverflow.com/questions/1094443/is-mixing-ado-net-and-linq-to-sql-bad-my-data-layer-isnt-working#answer-1094607