6

The question is pretty clear. Does DbContext hold an open connection during its life-cycle? What about EF core?

mxmissile
  • 11,464
  • 3
  • 53
  • 79
amiry jd
  • 27,021
  • 30
  • 116
  • 215
  • 2
    No and no, unless you force it by starting a transaction and keeping it open. Otherwise it only opens a connection when loading or saving and closes it once it's done. You can verify that easily using SQL Server profiler or Extended events though – Panagiotis Kanavos Nov 13 '18 at 09:37
  • 1
    Have you heard of [connection-pooling](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling)? It applies also to `DbContext` and the connection-string specifies if it is used. See: [`ConnectionString`](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection.connectionstring?view=netframework-4.7.2) (further down at `pooling`) – Tim Schmelter Nov 13 '18 at 09:37
  • 1
    Why the question though? It sounds like you encountered a different problem and think connection management is at fault? Or think that pooled connections are somehow a problem? They aren't – Panagiotis Kanavos Nov 13 '18 at 09:37
  • @PanagiotisKanavos That's and answer. Post it to get accepted. Thank you. – amiry jd Nov 13 '18 at 09:38
  • @PanagiotisKanavos, in the past I saw several times issues with too many open connections because somebody used wrong pattern for opening/closing connections. So IMHO the question is perfectly valid for somebody like me that is only waiting for an issue to appear with every new project ;) It seems that with EF Core we no longer have to worry about? – Andrzej Martyna Aug 10 '20 at 11:53
  • 1
    @AndrzejMartyna we didn't have to worry about this with ADO.NET either - connection pooling exists since 2000 and DataSet/DataTable is meant to work disconnected. Problems only occurred due to bad coding practices. Unfortunately, people found ways to break EF and EF Core too, by opening and keeping transactions open for no real reason, typically after using the "generic" repository *anti*pattern – Panagiotis Kanavos Aug 12 '20 at 14:22
  • @PanagiotisKanavos, fair enough, you are right! but rarely frameworks support easy, elegant ways to do advanced usages and then troubles and risks come (for example I have a reason lately to open a single connection and feed it to several contexts to allow transactions - it was quite challenging to find a good, elegant solution) – Andrzej Martyna Aug 12 '20 at 14:46
  • @AndrzejMartyna no you don't - whatever reason you think you have for that single connection and transactions, you don't. If you think you need that for Unit-of-Work, you don't - a DbContext already is a Unit-of-Work, a DbSet already is a Repository, and `SaveChangesAsync` will store *all* pending changes in a single transaction. You can create a separate DbContext for each scenario/use-case/DDD bounded context, containing the entities needed for that scenario. Why do you have to use *multiple* DbContexts? – Panagiotis Kanavos Aug 12 '20 at 15:00
  • @AndrzejMartyna a common bug is to try to implement eg transaction-per-request in web applications using Java techniques. That transaction though is a *business* transaction, not a database transaction. When you already have a UoW, you don't need an explicit transaction to rollback changes- you just don't persist them in the first place! If you persist partial changes, you've broken the Unit-of-Work. – Panagiotis Kanavos Aug 12 '20 at 15:05
  • @PanagiotisKanavos, it is perfectly fine when you start a project from scratch but we have a legacy system with many decisions already made (consciously or not) with mixed approaches. It wasn't possible to implement UoW without rewriting like around a hundred of files so went a path of reusing existing dbcontexts – Andrzej Martyna Aug 12 '20 at 15:22

1 Answers1

3

As noted by others, no, it doesn't, unless you manually open the connection and pass it to DbContext constructor.

A concrete detailed answer can be found here https://stackoverflow.com/a/45330219/191148.

And the comment of @ajcvickers in https://github.com/aspnet/EntityFrameworkCore/issues/7810 clears it:

If EF creates the DbConnection object, then EF will ensure it is disposed when the DbContext is disposed. On the other hand, if some other code creates the DbConnection object and passes it to EF, then it is the responsibility of the other code to also dispose the connection appropriately.

Similar rules apply to opening and closing the connection. If EF opens the connection, then EF will close the connection when it is done with it. If your code opens the connection, then your code should close the connection.

Mohsen Afshin
  • 13,273
  • 10
  • 65
  • 90
  • 1
    Thanks a lot. I also found this link: https://www.brentozar.com/archive/2015/07/database-connection-hazards-with-entity-framework/ which is commented by Julie Lerman. She is describing what exactly happens to underlying connection. – amiry jd Nov 13 '18 at 17:44