44

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?

pb2q
  • 58,613
  • 19
  • 146
  • 147
Daniel Magliola
  • 30,898
  • 61
  • 164
  • 243

2 Answers2

33

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)

anon
  • 4,578
  • 3
  • 35
  • 54
Kelly Robins
  • 7,168
  • 6
  • 43
  • 66
8

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

Rory
  • 40,559
  • 52
  • 175
  • 261
  • 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. – aaaaaa Sep 20 '17 at 02:15
  • @aaaaaa - 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. – Rory Sep 20 '17 at 22:47
  • "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) – aaaaaa Sep 20 '17 at 23:57
  • 1
    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. – Rory Sep 21 '17 at 00:01
  • You can always Close() the connection manually if you don't like the asymmetry of the implicit closing. – Rory Sep 21 '17 at 00:02
  • 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! – aaaaaa Sep 21 '17 at 00:03
  • Opening the EF connection later yourself seems to bypass the `IDbConnectionInterceptor.Opened` method if you are using an `IDbConnectionInterceptor` – xr280xr Mar 04 '21 at 05:09