How do you implement database connection management and connection pooling in EF Core?
6 minadvancedEF-Coreconnection-poolingperformance
Quick Answer
EF Core relies on ADO.NET connection pooling, which reuses physical connections keyed by connection string — so opening/closing a `DbContext`'s connection is cheap. Manage it by using short-lived contexts (scoped per request), keeping a single consistent connection string, tuning pool size (`Max/Min Pool Size`), and considering `DbContext` pooling (`AddDbContextPool`) to reuse context instances under high load. Avoid long-lived contexts and connection leaks.
Detailed Answer
Database connection management and pooling in EF Core are crucial for performance and scalability. EF Core uses ADO.NET connection pooling by default, but you can configure and optimize it for your specific needs.
1. Basic Connection String Configuration
// appsettings.json
{
"ConnectionStrings": {
"DefaultConnection": "Server=localhost;Database=MyApp;Trusted_Connection=true;TrustServerCertificate=true;",
"ProductionConnection": "Server=prod-server;Database=MyApp;User Id=appuser;Password=securepassword;TrustServerCertificate=true;"
}
}
// Program.cs
var builder = WebApplication.CreateBuilder(args);
builder.Services.AddDbContext<AppDbContext>(options =>
options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));
2. Connection Pooling Configuration
// Connection string with pooling settings
var connectionString = "Server=localhost;Database=MyApp;Trusted_Connection=true;" +
"Min Pool Size=5;" + // Minimum connections in pool
"Max Pool Size=100;" + // Maximum connections in pool
"Connection Lifetime=300;" + // Connection lifetime in seconds
"Connection Timeout=30;" + // Connection timeout
"Command Timeout=60;" + // Command timeout
"Pooling=true;"; // Enable pooling (default: true)
builder.Services.AddDbContext<AppDbContext>(options =>
options.UseSqlServer(connectionString));
3. Advanced Connection Configuration
public class AppDbContext : DbContext
{
public AppDbContext(DbContextOptions<AppDbContext> options) : base(options)
{
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
if (!optionsBuilder.IsConfigured)
{
optionsBuilder.UseSqlServer(connectionString, sqlOptions =>
{
sqlOptions.CommandTimeout(60);
sqlOptions.EnableRetryOnFailure(
maxRetryCount: 3,
maxRetryDelay: TimeSpan.FromSeconds(30),
errorNumbersToAdd: null);
});
}
}
}
4. Multiple Database Contexts with Different Pools
// Program.cs
builder.Services.AddDbContext<AppDbContext>(options =>
options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));
builder.Services.AddDbContext<AuditDbContext>(options =>
options.UseSqlServer(builder.Configuration.GetConnectionString("AuditConnection")));
builder.Services.AddDbContext<ReportingDbContext>(options =>
options.UseSqlServer(builder.Configuration.GetConnectionString("ReportingConnection")));
// Usage in services
public class OrderService
{
private readonly AppDbContext _context;
private readonly AuditDbContext _auditContext;
public OrderService(AppDbContext context, AuditDbContext auditContext)
{
_context = context;
_auditContext = auditContext;
}
}
5. Connection Pool Monitoring
public class ConnectionPoolMonitor
{
private readonly ILogger<ConnectionPoolMonitor> _logger;
private readonly AppDbContext _context;
public ConnectionPoolMonitor(ILogger<ConnectionPoolMonitor> logger, AppDbContext context)
{
_logger = logger;
_context = context;
}
public async Task MonitorConnectionPoolAsync()
{
try
{
// Get connection pool statistics
var connection = _context.Database.GetDbConnection();
if (connection is SqlConnection sqlConnection)
{
_logger.LogInformation("Connection Pool Statistics:");
_logger.LogInformation("Connection String: {ConnectionString}",
sqlConnection.ConnectionString);
_logger.LogInformation("Connection State: {State}",
sqlConnection.State);
_logger.LogInformation("Server Version: {Version}",
sqlConnection.ServerVersion);
}
// Test connection
await _context.Database.OpenConnectionAsync();
_logger.LogInformation("Database connection successful");
}
catch (Exception ex)
{
_logger.LogError(ex, "Database connection failed");
}
finally
{
await _context.Database.CloseConnectionAsync();
}
}
}
6. Custom Connection Factory
public class CustomConnectionFactory : IDbConnectionFactory
{
private readonly IConfiguration _configuration;
private readonly ILogger<CustomConnectionFactory> _logger;
public CustomConnectionFactory(IConfiguration configuration, ILogger<CustomConnectionFactory> logger)
{
_configuration = configuration;
_logger = logger;
}
public DbConnection CreateConnection(string connectionString)
{
var connection = new SqlConnection(connectionString);
// Add connection event handlers
connection.StateChange += OnConnectionStateChange;
connection.InfoMessage += OnConnectionInfoMessage;
return connection;
}
private void OnConnectionStateChange(object sender, StateChangeEventArgs e)
{
_logger.LogInformation("Connection state changed from {OriginalState} to {CurrentState}",
e.OriginalState, e.CurrentState);
}
private void OnConnectionInfoMessage(object sender, SqlInfoMessageEventArgs e)
{
_logger.LogInformation("SQL Info: {Message}", e.Message);
}
}
// Register custom connection factory
builder.Services.AddSingleton<IDbConnectionFactory, CustomConnectionFactory>();
7. Connection Resilience and Retry Policies
// Program.cs
builder.Services.AddDbContext<AppDbContext>(options =>
{
options.UseSqlServer(connectionString, sqlOptions =>
{
// Retry policy for transient failures
sqlOptions.EnableRetryOnFailure(
maxRetryCount: 3,
maxRetryDelay: TimeSpan.FromSeconds(30),
errorNumbersToAdd: null);
// Connection timeout
sqlOptions.CommandTimeout(60);
});
});
// Custom retry policy
public class ResilientDbContext : AppDbContext
{
public ResilientDbContext(DbContextOptions<AppDbContext> options) : base(options)
{
}
public override async Task<int> SaveChangesAsync(CancellationToken cancellationToken = default)
{
var retryPolicy = Policy
.Handle<SqlException>(ex => IsTransientError(ex))
.WaitAndRetryAsync(
retryCount: 3,
sleepDurationProvider: retryAttempt => TimeSpan.FromSeconds(Math.Pow(2, retryAttempt)),
onRetry: (outcome, timespan, retryCount, context) =>
{
Console.WriteLine($"Retry {retryCount} after {timespan} seconds");
});
return await retryPolicy.ExecuteAsync(async () =>
{
return await base.SaveChangesAsync(cancellationToken);
});
}
private static bool IsTransientError(SqlException ex)
{
// SQL Server transient error numbers
var transientErrors = new[] { 2, 53, 121, 1205, 1222, 8645, 8651 };
return transientErrors.Contains(ex.Number);
}
}
8. Connection Pool Optimization
public class ConnectionPoolOptimizer
{
private readonly IConfiguration _configuration;
public ConnectionPoolOptimizer(IConfiguration configuration)
{
_configuration = configuration;
}
public string GetOptimizedConnectionString(string baseConnectionString)
{
var builder = new SqlConnectionStringBuilder(baseConnectionString);
// Optimize for high-throughput scenarios
builder.MinPoolSize = 10; // Keep more connections ready
builder.MaxPoolSize = 200; // Allow more concurrent connections
builder.ConnectionLifetime = 600; // 10 minutes connection lifetime
builder.ConnectionTimeout = 15; // Faster connection timeout
builder.CommandTimeout = 30; // Reasonable command timeout
// Enable connection pooling
builder.Pooling = true;
// Enable multiple active result sets
builder.MultipleActiveResultSets = true;
// Optimize for read-heavy workloads
builder.ApplicationIntent = ApplicationIntent.ReadOnly;
return builder.ConnectionString;
}
public string GetOptimizedConnectionStringForWrites(string baseConnectionString)
{
var builder = new SqlConnectionStringBuilder(baseConnectionString);
// Optimize for write-heavy scenarios
builder.MinPoolSize = 5; // Fewer connections for writes
builder.MaxPoolSize = 50; // Limit concurrent writes
builder.ConnectionLifetime = 300; // Shorter connection lifetime
builder.ConnectionTimeout = 30; // Longer connection timeout for writes
builder.CommandTimeout = 60; // Longer command timeout for writes
// Enable connection pooling
builder.Pooling = true;
// Optimize for write workloads
builder.ApplicationIntent = ApplicationIntent.ReadWrite;
return builder.ConnectionString;
}
}
9. Environment-Specific Connection Management
// Program.cs
public static void ConfigureDatabase(WebApplicationBuilder builder)
{
var environment = builder.Environment.EnvironmentName;
switch (environment)
{
case "Development":
ConfigureDevelopmentDatabase(builder);
break;
case "Staging":
ConfigureStagingDatabase(builder);
break;
case "Production":
ConfigureProductionDatabase(builder);
break;
}
}
private static void ConfigureDevelopmentDatabase(WebApplicationBuilder builder)
{
builder.Services.AddDbContext<AppDbContext>(options =>
{
options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection"));
options.EnableSensitiveDataLogging();
options.EnableDetailedErrors();
options.LogTo(Console.WriteLine, LogLevel.Information);
});
}
private static void ConfigureProductionDatabase(WebApplicationBuilder builder)
{
builder.Services.AddDbContext<AppDbContext>(options =>
{
var connectionString = builder.Configuration.GetConnectionString("ProductionConnection");
options.UseSqlServer(connectionString, sqlOptions =>
{
sqlOptions.EnableRetryOnFailure(
maxRetryCount: 3,
maxRetryDelay: TimeSpan.FromSeconds(30));
sqlOptions.CommandTimeout(60);
});
// Disable sensitive data logging in production
options.EnableSensitiveDataLogging(false);
options.EnableDetailedErrors(false);
});
}
10. Connection Health Checks
public class DatabaseHealthCheck : IHealthCheck
{
private readonly AppDbContext _context;
public DatabaseHealthCheck(AppDbContext context)
{
_context = context;
}
public async Task<HealthCheckResult> CheckHealthAsync(
HealthCheckContext context,
CancellationToken cancellationToken = default)
{
try
{
// Test database connection
await _context.Database.OpenConnectionAsync(cancellationToken);
// Test simple query
await _context.Database.ExecuteSqlRawAsync("SELECT 1", cancellationToken);
// Get connection pool info
var connection = _context.Database.GetDbConnection();
var connectionState = connection.State;
return HealthCheckResult.Healthy($"Database is healthy. Connection state: {connectionState}");
}
catch (Exception ex)
{
return HealthCheckResult.Unhealthy("Database connection failed", ex);
}
finally
{
await _context.Database.CloseConnectionAsync();
}
}
}
// Register health check
builder.Services.AddHealthChecks()
.AddCheck<DatabaseHealthCheck>("database");
Key Points:
- Default Pooling: EF Core uses ADO.NET connection pooling by default
- Connection String: Configure pool size, timeouts, and lifetime
- Multiple Contexts: Each context can have its own connection pool
- Resilience: Implement retry policies for transient failures
- Monitoring: Track connection pool health and performance
- Environment-Specific: Different configurations for different environments
- Resource Management: Proper disposal and connection lifecycle management
Best Practices:
- Configure appropriate pool sizes based on your workload
- Use connection timeouts to prevent hanging connections
- Implement retry policies for transient failures
- Monitor connection pool health and performance
- Use different connection strings for read vs write operations
- Test connection resilience under load
- Implement proper error handling and logging
- Use health checks to monitor database connectivity