Using Entity Framework Core with SQL Server: Best Practices

Entity Framework Core gives .NET applications a productive way to work with SQL Server without turning every feature into hand-written data-access code. Its change tracker, LINQ provider, migrations, and support for dependency injection fit naturally into ASP.NET Core applications, background services, and APIs.

That convenience still requires sound database design. An Australian retail platform serving customers in Sydney, Melbourne, and Perth can quickly expose inefficient queries, connection-pool limits, or poor handling of time zones. The strongest results come from treating EF Core as a tool for expressing deliberate data-access decisions rather than as a replacement for understanding SQL Server.

Configure The DbContext For Its Lifetime

A DbContext represents a short unit of work. In a web application, register it with the default scoped lifetime so one context is generally used during a single HTTP request:

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(
        builder.Configuration.GetConnectionString("DefaultConnection")));

A context is not thread-safe, and it should not be shared between concurrent operations. Avoid placing a DbContext in a singleton service or keeping one alive for the entire application. Long-lived contexts accumulate tracked entities, consume memory, and can make later updates behave in surprising ways. For background jobs, create a scope for each job or use IDbContextFactory<TContext> when independent contexts are needed.

Keep connection strings outside source control. Local development might use SQL Server Developer Edition or a container, while production could use Azure SQL Database in the Australia East region. Configuration should come from environment variables, Azure Key Vault, or another secrets manager. A service based in Brisbane may have acceptable latency to Australia East, whereas a workload communicating with a distant overseas database can turn a modest query delay into a noticeable customer experience problem.

Use provider-specific options deliberately. Connection resiliency can help with transient Azure SQL failures:

options.UseSqlServer(connectionString, sqlOptions =>
{
    sqlOptions.EnableRetryOnFailure(
        maxRetryCount: 5,
        maxRetryDelay: TimeSpan.FromSeconds(10),
        errorNumbersToAdd: null);
});

Retries should be paired with transaction awareness. Repeating a read is usually safe, but repeating a non-idempotent operation can create duplicate side effects if the first attempt reached the database before the connection failed. Keep application-level actions, such as sending an email or charging a card, separate from database retry behaviour.

Design Models And Migrations Carefully

Entity classes should represent application concepts, while the database schema should represent durable relational data. Use explicit configuration for keys, lengths, indexes, required fields, decimal precision, and relationships. Data annotations are suitable for simple rules, but the Fluent API gives a clearer home for database-specific decisions in larger projects.

For example:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Order>(entity =>
    {
        entity.HasKey(order => order.Id);

        entity.Property(order => order.Total)
            .HasPrecision(12, 2);

        entity.HasIndex(order => new { order.CustomerId, order.CreatedUtc });
    });
}

Avoid relying on conventions for important financial or operational fields. A decimal column with unsuitable precision can round values unexpectedly, and an unconstrained string can become an unnecessarily large nvarchar(max) column. Define maximum lengths and use Unicode intentionally. Australian addresses, names, and business data can contain accented characters, so defaulting to Unicode is generally safer than assuming ASCII input.

Migrations should be treated as versioned production assets. Review generated migrations before applying them, particularly when a change renames a property. EF Core may interpret a rename as “drop the old column and add a new one”, which can destroy data unless the migration is edited to use RenameColumn.

For large tables, use an expand-and-contract approach. Add a nullable column first, deploy code that can write both old and new values, backfill in manageable batches, then enforce constraints and remove the old column later. This approach reduces lock duration and allows a busy service to keep operating while a migration progresses. It is especially useful for Australian organisations with strict maintenance windows or customers working across different time zones.

Make LINQ Produce Efficient SQL

LINQ is expressive, but the resulting SQL still determines performance. Project only the fields required by the operation instead of loading complete entities:

var summaries = await dbContext.Orders
    .AsNoTracking()
    .Where(order => order.CustomerId == customerId)
    .OrderByDescending(order => order.CreatedUtc)
    .Select(order => new OrderSummary(
        order.Id,
        order.CreatedUtc,
        order.Total,
        order.Status))
    .ToListAsync(cancellationToken);

AsNoTracking is a useful choice for read-only queries because EF Core does not need to maintain snapshots for changes. It can reduce memory and CPU use for reporting screens, search endpoints, and catalogue pages. Do not apply it automatically to workflows that load an entity, modify it, and save it.

Be alert for the N+1 query problem. Loading a list of customers and then querying orders separately for each customer creates many round trips. Prefer a projection, a carefully chosen Include, or a grouped query. Include is convenient for related data, but it can produce very large joins when multiple collections are included. Split queries may be preferable for complex object graphs:

var customers = await dbContext.Customers
    .AsSplitQuery()
    .Include(customer => customer.Orders)
    .ToListAsync(cancellationToken);

Pagination should be consistent and index-friendly. Offset pagination with Skip and Take is easy to implement, but becomes less efficient on deep pages. Keyset pagination, based on a stable combination such as CreatedUtc and Id, avoids scanning all preceding rows. This matters for an online store whose product or order history has grown over years rather than weeks.

Inspect generated SQL during development and use SQL Server execution plans for important queries. ToQueryString() can show the SQL produced by a LINQ expression, while Application Insights, SQL Server Query Store, and structured logging help identify slow production paths. Avoid calling ToList() before filters or projections, because that moves work from SQL Server into application memory.

Handle Transactions Concurrency And Time

SaveChangesAsync wraps changes made through one context in a transaction when the provider supports it. When several operations must succeed or fail together, make that boundary explicit and keep it short:

await using var transaction =
    await dbContext.Database.BeginTransactionAsync(cancellationToken);

try
{
    dbContext.Orders.Add(order);
    await dbContext.SaveChangesAsync(cancellationToken);

    auditEntries.Add(new AuditEntry(order.Id, "Created"));
    await dbContext.SaveChangesAsync(cancellationToken);

    await transaction.CommitAsync(cancellationToken);
}
catch
{
    await transaction.RollbackAsync(cancellationToken);
    throw;
}

Do not hold a database transaction open while waiting for an external HTTP call, a file upload, or a user action. Those delays increase lock contention and can exhaust the connection pool. If a workflow spans multiple systems, consider an outbox pattern: save the business change and an event record together, then publish the event from a separate worker.

Optimistic concurrency is often a better fit than locking rows for long periods. Add a SQL Server rowversion property to detect an update made after an entity was loaded:

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public byte[] Version { get; set; } = [];
}

Configure it as a concurrency token, catch DbUpdateConcurrencyException, and decide whether to reload, merge, or reject the change. This is valuable for administration systems where staff in Melbourne and Perth may edit the same stock record.

Store timestamps in UTC, commonly with datetime2, and convert to a user’s local time at the display boundary. Australia has several time zones and daylight-saving rules vary by state. A booking made in Sydney should not be interpreted using a fixed offset when daylight saving changes, while a customer in Queensland follows different seasonal rules. DateTimeOffset can be useful when the original offset has business meaning, but it is still important to define whether the system’s source of truth is an instant, a local date, or a recurring local time.

Build A Testable Data Access Layer

A clean data-access design keeps EF Core close to the application boundary. Inject the context into services rather than scattering database calls through controllers. Keep business decisions in application services and use projections or dedicated query methods for read models. A generic repository layered over every DbSet can hide useful EF Core features and add little value, so introduce abstractions where they support a genuine boundary or testing need.

Test important queries against a real SQL Server-compatible database. The EF Core InMemory provider does not behave like SQL Server: it does not reproduce relational constraints, SQL translation, collations, transactions, or server-side query behaviour. SQLite can be useful for some tests, but it also differs from SQL Server in types and SQL features. Testcontainers or a disposable SQL Server container can provide more realistic automated coverage in CI.

Seed only the data needed for a test and keep test databases isolated. Verify migrations as part of deployment checks, and run integration tests that cover unique constraints, foreign keys, concurrency conflicts, and transaction rollback. A query that passes with an empty local database may fail under the case-insensitive collation, realistic row counts, or indexing rules used in production.

Pay attention to operational details in Australian hosting environments. Azure SQL metrics, Query Store, backup retention, firewall rules, private endpoints, and data residency requirements should be part of the deployment design. Organisations handling health, financial, or government information may need to consider the Privacy Act 1988, contractual controls, and whether data must remain in an Australian region. Technical correctness includes knowing where the data lives and who can access it.

Practical Habits For Reliable EF Core Projects

The following habits keep an EF Core and SQL Server application predictable as its schema, traffic, and team grow:

Create a short-lived DbContext, pass cancellation tokens, and avoid concurrent use of one context.
Review every migration manually, especially destructive changes, renames, indexes, and large table updates.
Use projections and AsNoTracking for read-only endpoints, then inspect SQL and execution plans for expensive paths.
Add indexes based on real filtering, joining, sorting, and uniqueness requirements rather than indexing every column.
Use UTC instants consistently, with explicit handling for Australian time zones and daylight-saving transitions.
Test against SQL Server-compatible infrastructure and monitor query duration, deadlocks, retries, and connection-pool usage.

These practices also make code reviews more useful. A reviewer can ask whether an index matches the query, whether a migration will lock a busy table, and whether a retry could duplicate an external action. Those are more valuable checks than simply confirming that the code compiles or that a repository method has been added.

EF Core works best when its abstractions remain connected to SQL Server’s actual behaviour. Learn to read the generated SQL, understand transaction boundaries, and measure representative workloads before choosing a convenient pattern. That discipline supports applications from a small Stratford-based project to a national platform serving customers across Australia.

Apply these practices to one real query, migration, or background job in your codebase today. Inspect its SQL, test its failure modes, and record the operational assumptions alongside the code so the next developer can extend it safely.