When does Entity Framework open and close Database Connections?

Question

When I instance my "Entities" object in Entity Framework, and make a couple of queries using that Entities object, what happens to connections?

• Does it open a connection when I instance the object, and close it when I dispose of it?
• Or does it open and close a connection for each single query I execute?

In either case, is it possible to change it so that it does the other thing?

Allman

Wow @pb2q that was a very important edit to a 6-year old question! Nicely done! – Allman

dMagiola: no problem. Happy to help with any improvements: important or otherwise, old or new. Keep on asking buddy! – Hahnert

Answer 1

Basically it opens when a request is called and closes once the results have been disposed or consumed. You can manually open/close or use the same connection using the object context...

This article has a more complete explanation - http://msdn.microsoft.com/en-us/library/bb738582.aspx (archive.org)

Here is the How To on using an entity Connection - http://msdn.microsoft.com/en-us/library/bb738461.aspx (archive.org)

Lail

Both links are dead – Dissuade

Answer 2

The behaviour changed somewhat in EF6, allowing you to pass in open connections or to open the EF connection yourself later. Check out https://msdn.microsoft.com/en-us/library/dn456849(v=vs.113).aspx

Marcus

Thanks so much for the link - is there no way to declare an open connection via c#'s using clause? e.g. using(var conn = openAConnection()). The link you share shows a manual opening of the connection that gets closed when the related context is disposed, which just seems so unintuitive and hacky. – Perla

@Perla - maybe you're looking at the EF5 and previous example? In the second code example is the using clause like you want. Note that you only have to worry about this if you actually want to do something manually with the connection, e.g. to execute statements outside EF. If you don't need that then you don't need to think about opening & closing the connection at all. – Marcus

"Behavior in ES6 and future versions": conn.Open();. That's the line that irks me. And yes I need it apparently to get multiple result sets from a stored procedure (trying to keep the db requests to one per view) – Perla

Ah I see what you mean. Well I guess it's an ADO thing that you always have to open a connection in a separate method call. On the face of it it seems it'd be easier to have a constructor that opens it too (maybe there is one?) ... but it's not really that bad and it does make it nice and explicit. When you're working manually with DB connections I think explicit is good as it's easier to see exactly what's going on, which is often necessary to not introduce subtle bugs. – Marcus

You can always Close() the connection manually if you don't like the asymmetry of the implicit closing. – Marcus

Yeah - I just always thought the using statement was exactly for those scenarios - to prevent a connection being manually open, an error occurring, then the connection not being closed. Your explanation is good enough for me though. I'm not versed in .net much, more of a js guy. Thanks for your time! – Perla

Opening the EF connection later yourself seems to bypass the IDbConnectionInterceptor.Opened method if you are using an IDbConnectionInterceptor – Mayhap