Entity Framework Core: The Modern Way to Handle Databases

# Entity Framework Core: The Modern Way to Handle Databases

Entity Framework Core (EF Core) is the default ORM choice in many .NET backends. It helps teams move quickly while keeping strong control over schema evolution and query behavior. But using it effectively goes far beyond just calling `SaveChanges()`. In this article, I'll walk through how I set up, optimize, and maintain EF Core in real production systems.

Setting Up Your DbContext

The `DbContext` is the heart of EF Core. Getting it right from the start saves you from painful refactors later.

csharp

public class AppDbContext : DbContext
{
    public DbSet<Product> Products => Set<Product>();
    public DbSet<Order> Orders => Set<Order>();
    public DbSet<Customer> Customers => Set<Customer>();

    public AppDbContext(DbContextOptions<AppDbContext> options)
        : base(options) { }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        class=class="code-string">"code-comment">// Apply all configurations from the current assembly
        modelBuilder.ApplyConfigurationsFromAssembly(
            Assembly.GetExecutingAssembly());
    }
}

csharp

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(
        builder.Configuration.GetConnectionString(class="code-string">"Default"),
        sqlOptions =>
        {
            sqlOptions.EnableRetryOnFailure(
                maxRetryCount: class="code-number">3,
                maxRetryDelay: TimeSpan.FromSeconds(class="code-number">10),
                errorNumbersToAdd: null);
            sqlOptions.CommandTimeout(class="code-number">30);
        })
    .UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking));

In high-traffic APIs I've worked on, setting `NoTracking` as the default globally and opting in to tracking only where mutations happen has cut unnecessary memory allocations significantly.

Entity Configuration with Fluent API

I strongly prefer Fluent API over data annotations. It keeps domain entities clean and puts all persistence logic in dedicated configuration classes.

csharp

public class OrderConfiguration : IEntityTypeConfiguration<Order>
{
    public void Configure(EntityTypeBuilder<Order> builder)
    {
        builder.ToTable(class="code-string">"Orders");

        builder.HasKey(o => o.Id);

        builder.Property(o => o.OrderNumber)
            .IsRequired()
            .HasMaxLength(class="code-number">50);

        builder.Property(o => o.TotalAmount)
            .HasPrecision(class="code-number">18, class="code-number">2);

        builder.Property(o => o.Status)
            .HasConversion<string>()
            .HasMaxLength(class="code-number">20);

        builder.HasOne(o => o.Customer)
            .WithMany(c => c.Orders)
            .HasForeignKey(o => o.CustomerId)
            .OnDelete(DeleteBehavior.Restrict);

        builder.HasMany(o => o.OrderItems)
            .WithOne(oi => oi.Order)
            .HasForeignKey(oi => oi.OrderId)
            .OnDelete(DeleteBehavior.Cascade);

        class=class="code-string">"code-comment">// Indexes based on actual query patterns
        builder.HasIndex(o => o.OrderNumber).IsUnique();
        builder.HasIndex(o => o.CustomerId);
        builder.HasIndex(o => new { o.Status, o.CreatedAt });
    }
}

Owned Types for Value Objects

csharp

builder.OwnsOne(c => c.Address, address =>
{
    address.Property(a => a.Street).HasMaxLength(class="code-number">200);
    address.Property(a => a.City).HasMaxLength(class="code-number">100);
    address.Property(a => a.PostalCode).HasMaxLength(class="code-number">10);
    address.Property(a => a.Country).HasMaxLength(class="code-number">60);
});

This maps the `Address` value object into the same table as `Customer`, keeping domain purity without adding unnecessary joins.

Writing Effective LINQ Queries

Projecting to DTOs

Never return entities directly from API endpoints. Always project:

csharp

public async Task<List<OrderSummaryDto>> GetRecentOrdersAsync(
    int customerId, CancellationToken ct)
{
    return await _context.Orders
        .Where(o => o.CustomerId == customerId)
        .OrderByDescending(o => o.CreatedAt)
        .Take(class="code-number">20)
        .Select(o => new OrderSummaryDto
        {
            OrderNumber = o.OrderNumber,
            Total = o.TotalAmount,
            Status = o.Status,
            ItemCount = o.OrderItems.Count,
            CreatedAt = o.CreatedAt
        })
        .ToListAsync(ct);
}

Projections with `Select` let EF Core generate a targeted SQL query that only fetches the columns you need. This is one of the simplest and most impactful performance wins.

AsNoTracking for Read Paths

csharp

class=class="code-string">"code-comment">// When your DbContext defaults to tracking, opt out explicitly
var products = await _context.Products
    .AsNoTracking()
    .Where(p => p.IsActive)
    .ToListAsync(ct);

The change tracker holds references to every entity it loads. On endpoints that only read data, those references are pure overhead.

Compiled Queries for Hot Paths

For queries that execute thousands of times per minute, compiled queries skip the expression tree translation on each call:

csharp

private static readonly Func<AppDbContext, int, CancellationToken, Task<Product?>>
    GetProductById = EF.CompileAsyncQuery(
        (AppDbContext ctx, int id, CancellationToken ct) =>
            ctx.Products.FirstOrDefault(p => p.Id == id));

class=class="code-string">"code-comment">// Usage
var product = await GetProductById(_context, productId, ct);

In high-traffic APIs I've worked on, compiled queries on the most-hit endpoints shaved 2-3ms off each request -- small per call, but massive in aggregate.

The N+1 Problem and Solutions

The N+1 problem is arguably the most common performance killer in any ORM-based application. It happens when fetching a list triggers a separate query for each related entity.

The Problem

csharp

class=class="code-string">"code-comment">// BAD: This generates class="code-number">1 query for orders + N queries for customers
var orders = await _context.Orders.ToListAsync();

foreach (var order in orders)
{
    class=class="code-string">"code-comment">// Each access to order.Customer fires a separate SQL query
    Console.WriteLine($class="code-string">"Order {order.OrderNumber} by {order.Customer.Name}");
}

If you have 100 orders, this produces 101 SQL queries. With lazy loading enabled, it happens silently.

Solution 1: Eager Loading with Include

csharp

class=class="code-string">"code-comment">// GOOD: class="code-number">1 query (or class="code-number">2 with split query) that loads everything
var orders = await _context.Orders
    .Include(o => o.Customer)
    .Include(o => o.OrderItems)
    .ToListAsync();

Solution 2: Split Queries for Wide Includes

When you include multiple collections, a single query can produce a cartesian explosion. Split queries solve this:

csharp

var orders = await _context.Orders
    .Include(o => o.Customer)
    .Include(o => o.OrderItems)
        .ThenInclude(oi => oi.Product)
    .AsSplitQuery()
    .ToListAsync();

This generates multiple SQL statements but avoids the data duplication problem.

Solution 3: Projection (Best for Read-Only)

csharp

class=class="code-string">"code-comment">// BEST for API responses: only fetch what you need
var orders = await _context.Orders
    .Select(o => new OrderDetailDto
    {
        OrderNumber = o.OrderNumber,
        CustomerName = o.Customer.Name,
        Items = o.OrderItems.Select(oi => new OrderItemDto
        {
            ProductName = oi.Product.Name,
            Quantity = oi.Quantity,
            Price = oi.UnitPrice
        }).ToList()
    })
    .ToListAsync();

Projection avoids N+1 entirely because EF Core builds a single SQL query with JOINs at the database level.

Migration Best Practices

Migrations are version control for your database schema. Mismanaging them leads to deployment failures, data loss, and late-night incident calls.

Creating and Reviewing Migrations

bash

# Always name your migrations descriptively
dotnet ef migrations add AddOrderStatusIndex

# Review the generated code before applying
dotnet ef migrations script --idempotent

Always review the generated migration file. EF Core's diff algorithm is good but not perfect -- it can occasionally misinterpret renames as drop-and-create operations.

Safe Migration Patterns

csharp

public partial class AddOrderStatusIndex : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        class=class="code-string">"code-comment">// Create index concurrently to avoid table locks (PostgreSQL)
        migrationBuilder.Sql(
            class="code-string">"CREATE INDEX CONCURRENTLY IF NOT EXISTS " +
            class="code-string">"\"IX_Orders_Status\" ON \"Orders\" (\"Status\");");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropIndex(
            name: class="code-string">"IX_Orders_Status",
            table: class="code-string">"Orders");
    }
}

Migration Rules I Follow

**One concern per migration.** Don't mix schema changes and data migrations.

**Never edit a migration that has been applied to any environment.** Create a new one instead.

**Always write the `Down` method.** You will need to roll back eventually.

**Use idempotent scripts for production.** Generate with `--idempotent` so re-runs are safe.

**Separate data migrations from schema migrations.** Data transforms are harder to roll back and should be treated as their own deployment step.

Performance Optimization

Batching Operations

EF Core 7+ batches `SaveChanges` calls automatically, but you can control batch size:

csharp

options.UseSqlServer(connectionString, sqlOptions =>
{
    sqlOptions.MaxBatchSize(class="code-number">100);
});

Bulk Operations

For inserting thousands of rows, `SaveChanges` is too slow. Use `ExecuteUpdate` and `ExecuteDelete` (EF Core 7+):

csharp

class=class="code-string">"code-comment">// Update all inactive products in a single SQL statement
await _context.Products
    .Where(p => !p.IsActive && p.LastModified < cutoffDate)
    .ExecuteUpdateAsync(s =>
        s.SetProperty(p => p.IsArchived, true)
         .SetProperty(p => p.ArchivedAt, DateTime.UtcNow));

class=class="code-string">"code-comment">// Delete in bulk without loading entities
await _context.Products
    .Where(p => p.IsArchived && p.ArchivedAt < retentionDate)
    .ExecuteDeleteAsync();

These translate directly to SQL `UPDATE` and `DELETE` statements. No entities are loaded into memory.

Query Filters for Soft Delete

csharp

class=class="code-string">"code-comment">// In your entity configuration
builder.HasQueryFilter(p => !p.IsDeleted);

class=class="code-string">"code-comment">// This filter applies to every query automatically
var activeProducts = await _context.Products.ToListAsync();

class=class="code-string">"code-comment">// Bypass when you explicitly need deleted records
var allProducts = await _context.Products
    .IgnoreQueryFilters()
    .ToListAsync();

Connection Resiliency

In cloud environments, transient failures are inevitable:

csharp

options.UseSqlServer(connectionString, sqlOptions =>
{
    sqlOptions.EnableRetryOnFailure(
        maxRetryCount: class="code-number">5,
        maxRetryDelay: TimeSpan.FromSeconds(class="code-number">30),
        errorNumbersToAdd: null);
});

Monitoring with Query Tags

csharp

var orders = await _context.Orders
    .TagWith(class="code-string">"GetRecentOrders - OrdersController")
    .Where(o => o.CreatedAt > cutoff)
    .ToListAsync();

This embeds a comment in the generated SQL, making it easy to trace slow queries back to the exact C# code that produced them.

Common EF Core Mistakes

1. Returning Entities from API Endpoints

csharp

class=class="code-string">"code-comment">// BAD: Exposes internal structure, triggers lazy loading,
class=class="code-string">"code-comment">// causes serialization issues with circular references
[HttpGet]
public async Task<List<Order>> GetOrders()
    => await _context.Orders.Include(o => o.Customer).ToListAsync();

class=class="code-string">"code-comment">// GOOD: Project to a DTO
[HttpGet]
public async Task<List<OrderDto>> GetOrders()
    => await _context.Orders
        .Select(o => new OrderDto { class=class="code-string">"code-comment">/* ... */ })
        .ToListAsync();

2. Not Using CancellationTokens

csharp

class=class="code-string">"code-comment">// BAD: If the client disconnects, the query keeps running
await _context.Products.ToListAsync();

class=class="code-string">"code-comment">// GOOD: Query cancels when the HTTP request is aborted
await _context.Products.ToListAsync(cancellationToken);

3. Loading Entire Tables

csharp

class=class="code-string">"code-comment">// BAD: Loads everything, then filters in memory
var expensive = (await _context.Products.ToListAsync())
    .Where(p => p.Price > class="code-number">100);

class=class="code-string">"code-comment">// GOOD: Filter at the database level
var expensive = await _context.Products
    .Where(p => p.Price > class="code-number">100)
    .ToListAsync();

4. Long-Lived DbContext Instances

The `DbContext` is designed to be short-lived. In web applications, it should be scoped to a single request. Holding onto a DbContext across multiple requests causes the change tracker to bloat, stale data to persist, and concurrency issues to multiply.

5. Ignoring the Generated SQL

csharp

class=class="code-string">"code-comment">// Enable logging in development to see what EF Core actually sends
options.LogTo(Console.WriteLine, LogLevel.Information)
       .EnableSensitiveDataLogging()
       .EnableDetailedErrors();

In high-traffic APIs I've worked on, enabling SQL logging during development caught at least one hidden N+1 problem in every codebase I reviewed. Make it a habit.

Conclusion

EF Core is a powerful tool when used with discipline. The difference between a struggling data layer and a performant one almost always comes down to: projecting instead of loading entities, being explicit about your loading strategy, reviewing generated SQL, and treating migrations as first-class deployment artifacts.

The patterns I've shared here come from real production systems handling millions of requests. They're not theoretical -- they're battle-tested.

I can help audit your EF Core layer for correctness and performance.

Entity Framework Core: The Modern Way to Handle Databases

Setting Up Your DbContext

Entity Configuration with Fluent API

Owned Types for Value Objects

Writing Effective LINQ Queries

Projecting to DTOs

AsNoTracking for Read Paths

Compiled Queries for Hot Paths

The N+1 Problem and Solutions

The Problem

Solution 1: Eager Loading with Include

Solution 2: Split Queries for Wide Includes

Solution 3: Projection (Best for Read-Only)

Migration Best Practices

Creating and Reviewing Migrations

Safe Migration Patterns

Migration Rules I Follow

Performance Optimization

Batching Operations

Bulk Operations

Query Filters for Soft Delete

Connection Resiliency

Monitoring with Query Tags

Common EF Core Mistakes

1. Returning Entities from API Endpoints

2. Not Using CancellationTokens

3. Loading Entire Tables

4. Long-Lived DbContext Instances

5. Ignoring the Generated SQL

Conclusion

Related Articles

What is .NET? A Modern Backend Development Guide

Building RESTful APIs with ASP.NET Core

Clean Architecture in .NET: Building Scalable Project Structure

Have a Flutter Project?

Related Articles

What is .NET? A Modern Backend Development Guide
Learn what .NET is, how it works, and why enterprise teams choose it for backend development.

Building RESTful APIs with ASP.NET Core
Learn the fundamentals of building production-ready REST APIs with ASP.NET Core. Controllers, routing, and best practices.

Clean Architecture in .NET: Building Scalable Project Structure
Apply Clean Architecture in .NET projects. A guide to layers, dependency management, and testable code.