5

I'm using EFCore to steaming data to backend db, I call SaveChanges for every a certain number of new objects added to the dataset, I noticed from the EFCore debug log that it will close the connection and open a new one each time I call SaveChanges:

Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerConnection|DEBUG|Opening connection to database ...
Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerConnection|DEBUG|Beginning transaction with isolation level 'Unspecified'.
Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerConnection|DEBUG|Committing transaction
Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerConnection|DEBUG|Closing connection to database...
.... the logs repeats forever

So is there anyway to just use one connection for the entire lifecycle of a DbContext?

fluter
  • 13,238
  • 8
  • 62
  • 100
  • 2
    Question: why do you care about the connections? – DavidG Jul 26 '17 at 14:13
  • 1
    Answer: Performance. – fluter Jul 26 '17 at 14:18
  • And what makes you think it will be any faster? – DavidG Jul 26 '17 at 14:18
  • Establish an connection and close it takes time, right? Just like if I'm receive server pushed data, I open a WebSocket and wait on it for all the messages, I can't open a socket and receive a message then close it, and open a new socket to receive a second message. – fluter Jul 26 '17 at 14:20
  • It doesn't really work like that for SQL connections. The connection probably won't actually be closed but it will get put back in a pool. I seriously doubt you will see any perf benefit in keeping the connection open. And even if you did, it would be swallowed up by the time it takes do do all the inserts you are running. – DavidG Jul 26 '17 at 14:22
  • If connections are really a pooled policy, it would be fine. I just don't see anywhere in the dbcontext to indicate or configure the pool. – fluter Jul 26 '17 at 14:23
  • 1
    That's all done outside of EF, you shouldn't ever need to worry about it. If you want to configure it, you can set certain properties in the connection string. – DavidG Jul 26 '17 at 14:25
  • Connection pooling is configured through connection strings. There is nothing EF (Core) specific. – Ivan Stoev Jul 26 '17 at 14:25
  • I remember reading EF docs stating that a DB context is designed to be short-lived. Also there are already some questions on SO with the appropriate answers, for example: https://stackoverflow.com/a/28522272/3936440 – ViRuSTriNiTy Jul 26 '17 at 14:33

1 Answers1

9

There's no real need for you to change how this works, or even worry about it at all. By default, SQL Server connections are just put back into a connection pool so in reality it doesn't get closed. Opening a new one will just grab the next available on in the pool.

You can control the pool if you really want by setting values in the connection string, while I would advise against that unless you really know what you are doing, these are the main properties that are used (from MSDN):

Connection Lifetime: When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by Connection Lifetime. This is useful in clustered configurations to force load balancing between a running server and a server just brought online. A value of zero (0) will cause pooled connections to have the maximum time-out.

Connection Reset: Determines whether the database connection is reset when being removed from the pool. For Microsoft SQL Server version 7.0, setting to false avoids making an additional server round trip when obtaining a connection, but you must be aware that the connection state, such as database context, is not being reset.

Enlist: When true, the pooler automatically enlists the connection in the current transaction context of the creation thread if a transaction context exists.

Max Pool Size: The maximum number of connections allowed in the pool.

Min Pool Size: The minimum number of connections maintained in the pool.

Pooling: When true, the connection is drawn from the appropriate pool, or if necessary, created and added to the appropriate pool.

DavidG
  • 113,891
  • 12
  • 217
  • 223