Do I need to force a Dispose after a LINQ query?

Question

My DBA says that there are way too many connection open and he thinks it is my code in .net that is leaving them open.

I am using LINQ querys and EF code first.

Example Method:

 public List<Stuff> GetStuff()
 {
      var db = new DBContext();

      var results =  db.stuff.toList();

      return results;
 }

Do I need to dispose the db var once I am done? My understanding is that I didn't need to in EF and LINQ. Please point me to a Microsoft documentation about managing connection in code or best practices for LINQ/EF and db connections

Update:

I added

db.Connection.Close();
db.Dispose();

and I still see the open connection in SQL after the two lines were executed. Is there a reason why it wouldn't close when I force it to close?

Answer 1

You should listen to your DBA! Yes, use a using. Do not leave connections open unnecessarily. You should connect, do your business with the db, and close that connection, freeing it up for another process. This is especially true in high volume systems.

Edit. Let me further explain with my own experiences here. In low volume processing, it probably isn't an issue, but it's a bad habit not to dispose of something explicitly or not-wrap it in a using when it clearly implements IDisposable.

In high-volume situations, this is just asking for disaster. Sql server will allot so many connections per application (can be specified in the connection string). What happens is processes will spend time waiting for connections to free up if they're not promptly closed. This generally leads to timeouts or deadlocks in some situations.

Sure, you can tweak Sql server connection mgmt and such, but everytime you tweak a setting, you're making a compromise. You must consider backups running, other jobs running, etc. This is why a wise developer will listen to their DBA's warnings. It's not always all about the code...

Answer 2

I just asked this same question over on Programmers.SE. Robert Harvey gave a great answer.

In general, you don't need to use Using statements with Entity Framework data contexts. Lazy collections is one of the reasons why.

I encourage you to read the entire answer on Programmers.SE as well as the links Robert provides in the answer.