EFCore use a single dbconnect for the lifetime of

Question

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?

Answer 1

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.