Connecting .NET Core to Azure SQL: A Practical EF Core Data Layer

Getting a .NET application connected to Azure SQL is the easy part. The decisions that follow—how connections are authenticated, how transient failures are handled, and how schema changes reach production—are what make the data layer dependable.

This walkthrough focuses on those choices with Entity Framework Core and Azure SQL. It is about the database boundary of an application, not a general Azure deployment guide.

Start with a deliberate connection setup

Keep the connection string outside source code. For local development, store it in user secrets or use environment variables. In Azure, prefer an application identity over a SQL username and password. With a current Microsoft.Data.SqlClient version, Authentication=Active Directory Default can use the developer's signed-in identity locally and the app's managed identity when hosted in Azure, provided those identities have been granted access to the database.

For example, a local configuration value might look like this:

Server=tcp:myserver.database.windows.net,1433;Initial Catalog=orders;
Encrypt=True;TrustServerCertificate=False;Authentication=Active Directory Default;

In production, assign the application identity only the database permissions it needs. A managed identity avoids storing a long-lived database password in app settings, but it does not grant database access automatically: the identity still needs a database user and appropriate roles.

Configure EF Core for Azure SQL

Azure SQL can briefly reject a connection or interrupt a command during maintenance, failover, or a network disruption. A bounded retry policy helps with those transient faults. It is not a substitute for handling errors your application cannot recover from, such as invalid data or a broken query.

using Microsoft.EntityFrameworkCore;

var connectionString =
    builder.Configuration.GetConnectionString("Orders")
    ?? throw new InvalidOperationException("The Orders connection string is missing.");

builder.Services.AddDbContext<OrdersDbContext>(options =>
{
    options.UseSqlServer(connectionString, sqlOptions =>
    {
        // Retry short-lived Azure SQL connectivity failures with a bounded delay.
        sqlOptions.EnableRetryOnFailure(
            maxRetryCount: 5,
            maxRetryDelay: TimeSpan.FromSeconds(10),
            errorNumbersToAdd: null);

        // Keep individual commands from waiting indefinitely.
        sqlOptions.CommandTimeout(30);
    });
});

The retry policy is useful for occasional faults, but repeated retries can increase latency when the database is genuinely unavailable. Keep the retry count and delay sensible for the request timeouts used by your API. Also be careful when retrying a transaction that includes external side effects, such as sending a message or charging a card. A database retry cannot undo an action that already happened outside the database.

Understand connection pooling before tuning it

Azure SQL connection pooling is handled by the SQL client driver. EF Core typically opens a connection when it needs one and returns it to the pool when the operation is done. In most applications, you should let the driver manage this rather than opening a new connection for every query or trying to keep one connection alive for the lifetime of the app.

Problems tend to appear when connections are held longer than necessary—for example, while making an HTTP request inside a database transaction—or when code fails to dispose manually created connections. If requests begin timing out under load, investigate connection usage and database capacity before simply increasing the pool size. A larger pool can move pressure onto Azure SQL rather than fix the underlying bottleneck.

Keep queries focused as the application grows

EF Core makes it convenient to load entities, but reading an entire entity graph for a screen that needs three fields wastes database work and memory. Project directly into a result type, and use AsNoTracking for read-only queries:

var recentOrders = await dbContext.Orders
    .AsNoTracking()
    .Where(order => order.CustomerId == customerId)
    .OrderByDescending(order => order.CreatedAt)
    // Fetch only the fields needed by this response.
    .Select(order => new OrderSummary(
        order.Id,
        order.CreatedAt,
        order.Total))
    .Take(50)
    .ToListAsync(cancellationToken);

For a small result set, offset pagination is straightforward. On a large, frequently updated table, deep offsets can become expensive and rows may shift between pages. Keyset pagination in EF Core uses the last seen sort key to ask for the next page, which is often a better fit for scrolling through recent records. Make the ordering deterministic—for example, sort by creation time and then by a unique ID—and add an index that supports the filter and sort pattern.

Indexes should follow real query patterns, not guesses. Check the generated SQL and execution plan when a query becomes slow. Azure SQL Query Store can help identify queries whose performance has changed over time, making it easier to distinguish a new application issue from a workload or plan change.

Move schema changes out of application startup

Calling Database.Migrate() every time an app instance starts can seem convenient, but it creates deployment risks. Multiple instances may race to apply migrations, and the application identity may end up with schema permissions it should not have during normal operation.

For a controlled release, create and review an EF Core migration, then apply it as a separate deployment step. A database migration bundle packages the migration work so a release pipeline can run it deliberately before or alongside the application rollout. Review generated SQL for important production changes, especially operations that rebuild large tables or alter columns used by live traffic. For changes that cannot be applied instantly, use an expand-and-contract approach: add compatible schema first, deploy code that can work with it, then remove the old schema in a later release.

Know what to inspect when things slow down

When database calls are slow, capture more than the overall request duration. Record which operation is slow, how long it spends waiting for a connection, and whether the database reports resource pressure. EF Core logging can help during diagnosis, but avoid logging sensitive parameter values in production. Pair application telemetry with Azure SQL metrics and Query Store data so you can see whether the delay comes from the application, the network, or a particular query.

A solid Azure SQL data layer usually comes down to a few unglamorous habits: use identity-based access, keep retries bounded, release connections promptly, fetch only what a request needs, and treat migrations as a release activity. Those choices give an EF Core application room to handle ordinary database hiccups without hiding persistent problems.