Background jobs are common in ASP.NET Core applications. They process queued orders, generate reports, synchronize data, send notifications, import files, and perform scheduled maintenance.
Unlike ordinary HTTP requests, some background jobs can run for several minutes or even hours. During that time, they may perform hundreds of database operations while communicating with external services or processing large amounts of data.
This creates an important database connection management challenge.
A job that opens a database connection and keeps it active throughout its entire execution can unnecessarily occupy connection pool resources. If multiple jobs run concurrently, the application may eventually run out of available connections. Other requests then wait for a connection or fail after reaching the connection timeout.
The problem is not necessarily that the database cannot handle the workload. Sometimes the application simply holds connections longer than necessary.
Entity Framework Core, ADO.NET connection pooling, dependency injection scopes, and asynchronous programming provide the building blocks for handling this correctly.
The goal is to keep database connections available when they are needed, release them when database work is complete, and ensure that long-running operations do not exhaust the connection pool.
Why Long Running Jobs Can Exhaust Database Connections
Consider an ASP.NET Core application that processes customer orders in the background.
The job performs the following operations:
- Retrieves pending orders.
- Loads customer information.
- Calls an external payment service.
- Updates order status.
- Sends a notification.
- Records processing results.
Suppose the job takes 10 minutes to complete.
If the application opens a database connection at the beginning and keeps it open until the entire job finishes, that connection remains unavailable to other operations even when the job is waiting for an external API response.
The problem becomes more serious when multiple jobs execute concurrently.
For example, suppose an application has a SQL Server connection pool with a maximum size of 100 connections. If 80 long-running jobs each hold one connection, only 20 connections remain available to other application operations within that pool.
The application may experience connection acquisition delays even though many of those jobs are not actively querying the database.
A simplified execution model looks like this:
Background Job 1
|
+---- Open Connection
|
+---- Read Orders
|
+---- Call External API (2 minutes)
|
+---- Update Database
|
+---- Close Connection
Background Job 2
|
+---- Open Connection
|
+---- Process Records
|
+---- Wait for Another Service
|
+---- Update Database
|
+---- Close ConnectionThe important question is when the connection is opened and when it is released.
A database connection should generally be open only while the application is performing database operations.
It should not remain occupied while the application performs unrelated work.
Understand Connection Pooling in ADO.NET
Opening a physical database connection involves network communication, authentication, and session initialization. Repeating these operations for every database query would introduce unnecessary overhead.
ADO.NET uses connection pooling to reduce this cost.
Connection pooling maintains a collection of reusable physical connections.
When an application opens a connection, the database driver checks whether an appropriate connection is available in the pool.
If one is available, it is reused. Otherwise, the driver may create a new physical connection, provided the pool has not reached its configured maximum.
When the application closes or disposes the logical connection, the physical connection is normally returned to the pool.
Consider this example using Microsoft.Data.SqlClient:
using Microsoft.Data.SqlClient;
public async Task UpdateOrderAsync(
string connectionString,
int orderId,
CancellationToken cancellationToken)
{
await using var connection =
new SqlConnection(connectionString);
await connection.OpenAsync(cancellationToken);
await using var command = connection.CreateCommand();
command.CommandText = """
UPDATE Orders
SET Status = @Status
WHERE Id = @OrderId
""";
command.Parameters.AddWithValue("@Status", "Completed");
command.Parameters.AddWithValue("@OrderId", orderId);
await command.ExecuteNonQueryAsync(cancellationToken);
}When the method finishes, the connection is disposed and returned to the pool.
This does not necessarily close the underlying physical database connection. The driver can reuse it for another operation.
This is why opening and disposing logical connections around individual database operations is generally a good practice.
Connection pooling is enabled by default in Microsoft.Data.SqlClient.
It is important to distinguish connection pooling from keeping a connection permanently open. A properly disposed connection can still benefit from pooling without remaining checked out for the entire lifetime of a background job.
Avoid Holding a Database Connection During External Operations
One of the most common mistakes in background processing is combining database operations and external API calls inside a single connection lifetime.
Consider the following implementation:
public async Task ProcessOrderAsync(
int orderId,
CancellationToken cancellationToken)
{
await using var connection =
new SqlConnection(_connectionString);
await connection.OpenAsync(cancellationToken);
var order = await LoadOrderAsync(
connection,
orderId,
cancellationToken);
var paymentResult = await _paymentService
.ProcessPaymentAsync(order, cancellationToken);
await UpdateOrderStatusAsync(
connection,
orderId,
paymentResult,
cancellationToken);
}The connection remains open while the payment service executes.
If the external service takes 30 seconds to respond, the database connection remains checked out for those 30 seconds.
This is unnecessary because the application does not need the database connection while waiting for the payment service.
A better implementation separates the database operations:
public async Task ProcessOrderAsync(
int orderId,
CancellationToken cancellationToken)
{
var order = await LoadOrderAsync(
orderId,
cancellationToken);
var paymentResult = await _paymentService
.ProcessPaymentAsync(order, cancellationToken);
await UpdateOrderStatusAsync(
orderId,
paymentResult,
cancellationToken);
}Each database method manages its own connection.
For example:
private async Task<Order> LoadOrderAsync(
int orderId,
CancellationToken cancellationToken)
{
await using var connection =
new SqlConnection(_connectionString);
await connection.OpenAsync(cancellationToken);
// Execute the order query and return the result.
return await GetOrderFromDatabaseAsync(
connection,
orderId,
cancellationToken);
}The connection is released before the external payment operation begins.
The application then opens another logical connection when it needs to update the order.
Because connection pooling is enabled, the second operation can reuse a physical connection without requiring a completely new database login.
Use Short-Lived DbContext Instances in Background Services
Entity Framework Core introduces another important lifetime consideration.
In ASP.NET Core applications, AddDbContext registers a DbContext as a scoped service by default.
This works well for ordinary HTTP requests because a request typically represents one unit of work.
Background services have a different lifecycle.
A BackgroundService is generally registered as a singleton. Injecting a scoped DbContext directly into a singleton service can create lifetime problems.
It can also cause the same DbContext instance to remain alive for much longer than intended.
Consider this registration:
builder.Services.AddDbContext<ApplicationDbContext>(
options =>
options.UseSqlServer(connectionString));
builder.Services.AddHostedService<OrderProcessor>();The following implementation should be avoided:
public class OrderProcessor : BackgroundService
{
private readonly ApplicationDbContext _context;
public OrderProcessor(ApplicationDbContext context)
{
_context = context;
}
protected override async Task ExecuteAsync(
CancellationToken stoppingToken)
{
while (!stoppingToken.IsCancellationRequested)
{
var orders = await _context.Orders
.Where(x => x.Status == "Pending")
.ToListAsync(stoppingToken);
// Process orders...
await Task.Delay(
TimeSpan.FromMinutes(1),
stoppingToken);
}
}
}The problem is that the DbContext is retained by the long-lived background service.
Its change tracker can accumulate entities, and its lifetime no longer represents a short database unit of work.
A better approach is to create a dependency injection scope for each processing iteration or use IDbContextFactory to create individual context instances.
Use IDbContextFactory for Long Running Jobs
EF Core provides IDbContextFactory for scenarios where the application needs explicit control over DbContext creation and disposal.
Register the factory in Program.cs:
builder.Services.AddDbContextFactory<ApplicationDbContext>(
options =>
options.UseSqlServer(connectionString));
builder.Services.AddHostedService<OrderProcessor>();The DbContext must accept DbContextOptions:
public class ApplicationDbContext : DbContext
{
public ApplicationDbContext(
DbContextOptions<ApplicationDbContext> options)
: base(options)
{
}
public DbSet<Order> Orders => Set<Order>();
}The background service can now create a context whenever it needs to perform database work.
public class OrderProcessor : BackgroundService
{
private readonly IDbContextFactory<ApplicationDbContext>
_contextFactory;
private readonly ILogger<OrderProcessor> _logger;
public OrderProcessor(
IDbContextFactory<ApplicationDbContext> contextFactory,
ILogger<OrderProcessor> logger)
{
_contextFactory = contextFactory;
_logger = logger;
}
protected override async Task ExecuteAsync(
CancellationToken stoppingToken)
{
while (!stoppingToken.IsCancellationRequested)
{
try
{
List<Order> orders;
await using (var context =
await _contextFactory.CreateDbContextAsync(
stoppingToken))
{
orders = await context.Orders
.AsNoTracking()
.Where(x => x.Status == "Pending")
.OrderBy(x => x.Id)
.Take(100)
.ToListAsync(stoppingToken);
}
foreach (var order in orders)
{
await ProcessOrderAsync(
order,
stoppingToken);
}
await Task.Delay(
TimeSpan.FromSeconds(10),
stoppingToken);
}
catch (OperationCanceledException)
when (stoppingToken.IsCancellationRequested)
{
break;
}
catch (Exception ex)
{
_logger.LogError(
ex,
"Error while processing pending orders.");
await Task.Delay(
TimeSpan.FromSeconds(15),
stoppingToken);
}
}
}
private async Task ProcessOrderAsync(
Order order,
CancellationToken cancellationToken)
{
// Perform external or application processing here.
await Task.CompletedTask;
}
}
Join the conversation! Your thoughts help the community grow.