✨ Hands-On Tutorial

EF Core 8 Fundamentals: Modern Data Access with .NET 8

40 min read Intermediate

Entity Framework Core 8 pairs with .NET 8 LTS to deliver faster, safer data access through smarter batching, better compiled query and model support, and improved raw SQL mapping—all while keeping LINQ's type safety and productivity. In this guide, you'll build a Todo API from scratch, starting with code-first models and evolving toward efficient queries, transactions, concurrency handling, and comprehensive testability. You'll develop locally with SQLite for simplicity, then switch to PostgreSQL or SQL Server for production, learning provider-specific features like JSON column mapping along the way. By the end, you'll know how to model complex data relationships, write performant queries, diagnose performance hot paths, and ship production-ready data access code with confidence. Entity Framework Core 8 is Microsoft's modern, lightweight ORM that pairs with .NET 8 LTS to deliver type-safe, performant data access. It brings smarter batching for fewer database round trips, better compiled query support, and improved raw SQL mapping—all while keeping LINQ's expressiveness and productivity benefits.

This tutorial takes you from zero to production-ready data access. You'll build a Todo API from scratch, starting with code-first models and migrations, then progressing to complex relationships, efficient LINQ queries, transactions, and concurrency handling. You'll develop locally with SQLite for simplicity, then learn how to switch to PostgreSQL or SQL Server for production, including provider-specific features like JSON column mapping.

By the end, you'll confidently model complex data relationships, write performant queries, diagnose N+1 problems and other performance bottlenecks, implement proper transaction boundaries, and ship production-ready EF Core 8 applications with comprehensive test coverage.

What's New in EF Core 8

EF Core 8 brings focused improvements for productivity and performance, building on the solid foundation of EF Core 7. Here's what makes EF Core 8 worth adopting for your .NET 8 applications:

📦

JSON Columns

Map complex types to JSON columns with SQL Server and PostgreSQL providers. Store rich objects without separate tables for simpler schemas.

⚡

Smarter Batching

Automatic batching of multiple statements in SaveChanges reduces database round trips, improving throughput for bulk operations.

🔍

Raw SQL Improvements

Easier projection to DTOs with FromSqlRaw, better parameterization, and async support for stored procedures and custom queries.

🧩

Compiled Queries

Faster cold-start and repeated execution for complex queries through improved compiled query and model caching mechanisms.

🌐

Provider Enhancements

Better SQLite, PostgreSQL, and SQL Server integration with temporal tables, generated columns, and provider-specific optimizations.

Long-Term Support

EF Core 8 aligns with .NET 8 LTS, supported through November 2026. Consider upgrading for JSON-heavy models, high-throughput APIs, or when preparing for Native AOT deployment. Most EF Core 7 applications upgrade smoothly with minimal code changes.

Setup & Project Creation

You need the .NET 8 SDK and EF Core tools to build data-driven applications. EF Core works with multiple database providers through NuGet packages.

Install .NET 8 SDK and EF Tools

Download the SDK from dotnet.microsoft.com/download and install the EF Core CLI tools:

Terminal 
dotnet --version
# Should show 8.0.x or higher

# Install EF Core tools globally
dotnet tool install --global dotnet-ef

# Verify EF tools installation
dotnet ef --version

Create Your Project

Create Web API Project 
dotnet new webapi -n TodoApiEfCore
cd TodoApiEfCore
dotnet run

Add EF Core Packages

Install EF Core Packages 
# SQLite provider (for local development)
dotnet add package Microsoft.EntityFrameworkCore.Sqlite

# Design tools (for migrations)
dotnet add package Microsoft.EntityFrameworkCore.Design

# Optional: For PostgreSQL in production
# dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL

# Optional: For SQL Server in production
# dotnet add package Microsoft.EntityFrameworkCore.SqlServer

Configure DbContext in Program.cs

Program.cs - DbContext Setup 
var builder = WebApplication.CreateBuilder(args);

// Configure DbContext with SQLite
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlite(builder.Configuration.GetConnectionString("DefaultConnection")));

builder.Services.AddControllers();
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

var app = builder.Build();

if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

app.UseHttpsRedirection();
app.UseAuthorization();
app.MapControllers();

app.Run();

Add Connection String

appsettings.json 
{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning",
      "Microsoft.EntityFrameworkCore": "Information"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=todos.db"
  }
}
Tip: Sensitive Data Logging

Use EnableSensitiveDataLogging() only in development to see parameter values in SQL logs. Run dotnet ef database update to apply migrations and create your database schema.

Models & DbContext

Entity classes represent your domain model, while DbContext manages connections, change tracking, and queries. Configuration can be done through conventions, data annotations, or fluent API.

Define Your Entity Model

Todo.cs - Entity Model 
public class Todo
{
    public int Id { get; set; }
    
    public string Title { get; set; } = string.Empty;
    
    public bool IsDone { get; set; }
    
    public DateTime? DueDate { get; set; }
    
    public List<string> Tags { get; set; } = new();
    
    public DateTime CreatedAt { get; set; }
    
    public DateTime? UpdatedAt { get; set; }
}

Create DbContext

AppDbContext.cs 
using Microsoft.EntityFrameworkCore;

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

    public DbSet<Todo> Todos => Set<Todo>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Todo>(entity =>
        {
            // Primary key
            entity.HasKey(t => t.Id);
            
            // Required fields
            entity.Property(t => t.Title)
                .IsRequired()
                .HasMaxLength(200);
            
            // Indexes for common queries
            entity.HasIndex(t => t.IsDone);
            entity.HasIndex(t => t.DueDate);
            
            // Default value
            entity.Property(t => t.CreatedAt)
                .HasDefaultValueSql("datetime('now')");
            
            // Store Tags as JSON (SQLite doesn't support JSON columns natively)
            entity.Property(t => t.Tags)
                .HasConversion(
                    v => string.Join(',', v),
                    v => v.Split(',', StringSplitOptions.RemoveEmptyEntries).ToList());
        });
    }
}

Fluent API Configuration

For complex configuration, extract entity configuration into separate classes:

TodoConfiguration.cs 
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;

public class TodoConfiguration : IEntityTypeConfiguration<Todo>
{
    public void Configure(EntityTypeBuilder<Todo> builder)
    {
        builder.HasKey(t => t.Id);
        
        builder.Property(t => t.Title)
            .IsRequired()
            .HasMaxLength(200);
        
        builder.HasIndex(t => t.IsDone);
        builder.HasIndex(t => t.DueDate);
        
        builder.Property(t => t.CreatedAt)
            .HasDefaultValueSql("datetime('now')");
        
        builder.Property(t => t.Tags)
            .HasConversion(
                v => string.Join(',', v),
                v => v.Split(',', StringSplitOptions.RemoveEmptyEntries).ToList());
    }
}

// In DbContext
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.ApplyConfiguration(new TodoConfiguration());
}

Value Objects with Owned Types

Complex Types 
public class Address
{
    public string Street { get; set; } = string.Empty;
    public string City { get; set; } = string.Empty;
    public string ZipCode { get; set; } = string.Empty;
}

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public Address ShippingAddress { get; set; } = new();
}

// Configuration
modelBuilder.Entity<Customer>()
    .OwnsOne(c => c.ShippingAddress, address =>
    {
        address.Property(a => a.Street).HasMaxLength(200);
        address.Property(a => a.City).HasMaxLength(100);
    });
Configuration Best Practices

Prefer EF Core conventions for simple scenarios—they reduce configuration noise. Use fluent API for indexes, constraints, and relationships. Keep advanced configuration localized in IEntityTypeConfiguration classes for maintainability.

Migrations & Seeding

Migrations track schema changes and evolve your database safely. EF Core generates migration code from your model changes, applying them incrementally.

Create Your First Migration

Terminal - Create Migration 
# Generate migration from model changes
dotnet ef migrations add InitialCreate

# Apply migration to database
dotnet ef database update

# View migration SQL without applying
dotnet ef migrations script

Migration File Structure

Generated Migration 
public partial class InitialCreate : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "Todos",
            columns: table => new
            {
                Id = table.Column<int>(nullable: false)
                    .Annotation("Sqlite:Autoincrement", true),
                Title = table.Column<string>(maxLength: 200, nullable: false),
                IsDone = table.Column<bool>(nullable: false),
                DueDate = table.Column<DateTime>(nullable: true),
                Tags = table.Column<string>(nullable: false),
                CreatedAt = table.Column<DateTime>(nullable: false, 
                    defaultValueSql: "datetime('now')"),
                UpdatedAt = table.Column<DateTime>(nullable: true)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Todos", x => x.Id);
            });

        migrationBuilder.CreateIndex(
            name: "IX_Todos_IsDone",
            table: "Todos",
            column: "IsDone");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropTable(name: "Todos");
    }
}

Seeding Data

Seed initial data using HasData in migrations or through a seeding service:

Data Seeding Approaches 
// Approach 1: HasData in configuration (part of migrations)
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Todo>().HasData(
        new Todo { Id = 1, Title = "Learn EF Core", IsDone = false, CreatedAt = DateTime.UtcNow },
        new Todo { Id = 2, Title = "Build API", IsDone = false, CreatedAt = DateTime.UtcNow }
    );
}

// Approach 2: Seeding service (runs at app startup)
public class DatabaseSeeder
{
    private readonly AppDbContext _context;

    public DatabaseSeeder(AppDbContext context)
    {
        _context = context;
    }

    public async Task SeedAsync()
    {
        if (!await _context.Todos.AnyAsync())
        {
            await _context.Todos.AddRangeAsync(
                new Todo { Title = "Learn EF Core", CreatedAt = DateTime.UtcNow },
                new Todo { Title = "Build API", CreatedAt = DateTime.UtcNow }
            );
            await _context.SaveChangesAsync();
        }
    }
}

// Register and run in Program.cs
using (var scope = app.Services.CreateScope())
{
    var context = scope.ServiceProvider.GetRequiredService<AppDbContext>();
    var seeder = new DatabaseSeeder(context);
    await seeder.SeedAsync();
}

Migration Commands

Common Migration Commands 
# List all migrations
dotnet ef migrations list

# Remove last migration (if not applied)
dotnet ef migrations remove

# Update to specific migration
dotnet ef database update MigrationName

# Generate SQL script for specific migration range
dotnet ef migrations script InitialCreate AddTags

# Create migration bundle for deployment
dotnet ef migrations bundle --self-contained
Tip: Migration Best Practices

Treat migrations as code—commit them to source control. Never use --force on production databases. Test migrations on a staging environment first. For deployment, use migration bundles or SQL scripts rather than running migrations from application code.

Relationships & Navigation

EF Core models relationships between entities through navigation properties and foreign keys. Understanding loading strategies is crucial for query performance.

One-to-Many Relationship

One-to-Many Example 
public class Category
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    
    // Navigation property: one category has many todos
    public List<Todo> Todos { get; set; } = new();
}

public class Todo
{
    public int Id { get; set; }
    public string Title { get; set; } = string.Empty;
    
    // Foreign key
    public int? CategoryId { get; set; }
    
    // Navigation property
    public Category? Category { get; set; }
}

// Configuration (often not needed—conventions work)
modelBuilder.Entity<Todo>()
    .HasOne(t => t.Category)
    .WithMany(c => c.Todos)
    .HasForeignKey(t => t.CategoryId)
    .OnDelete(DeleteBehavior.SetNull);

Many-to-Many Relationship

Many-to-Many with Implicit Join 
public class Todo
{
    public int Id { get; set; }
    public string Title { get; set; } = string.Empty;
    
    // Navigation property
    public List<Tag> Tags { get; set; } = new();
}

public class Tag
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    
    // Navigation property
    public List<Todo> Todos { get; set; } = new();
}

// EF Core 5+ creates join table automatically
// Configuration only needed for custom join table or cascade rules
modelBuilder.Entity<Todo>()
    .HasMany(t => t.Tags)
    .WithMany(tag => tag.Todos);

Many-to-Many with Explicit Join Entity

Explicit Join Entity 
// Use explicit join when you need extra columns
public class TodoTag
{
    public int TodoId { get; set; }
    public Todo Todo { get; set; } = null!;
    
    public int TagId { get; set; }
    public Tag Tag { get; set; } = null!;
    
    // Additional columns
    public DateTime AssignedAt { get; set; }
    public string AssignedBy { get; set; } = string.Empty;
}

// Configuration
modelBuilder.Entity<TodoTag>()
    .HasKey(tt => new { tt.TodoId, tt.TagId });

modelBuilder.Entity<TodoTag>()
    .HasOne(tt => tt.Todo)
    .WithMany()
    .HasForeignKey(tt => tt.TodoId);

modelBuilder.Entity<TodoTag>()
    .HasOne(tt => tt.Tag)
    .WithMany()
    .HasForeignKey(tt => tt.TagId);

Loading Strategies

Eager Loading with Include 
// Load related data in the same query
var todos = await context.Todos
    .Include(t => t.Category)
    .Include(t => t.Tags)
    .ToListAsync();

// Multi-level include
var todos = await context.Todos
    .Include(t => t.Category)
        .ThenInclude(c => c.Owner)
    .ToListAsync();

// Filtered include (EF Core 5+)
var todos = await context.Todos
    .Include(t => t.Tags.Where(tag => tag.IsActive))
    .ToListAsync();
Explicit Loading 
// Load related data on demand
var todo = await context.Todos.FindAsync(id);

if (todo != null)
{
    // Load category if needed
    await context.Entry(todo)
        .Reference(t => t.Category)
        .LoadAsync();
    
    // Load collection
    await context.Entry(todo)
        .Collection(t => t.Tags)
        .LoadAsync();
    
    // Query collection
    var activeTags = await context.Entry(todo)
        .Collection(t => t.Tags)
        .Query()
        .Where(tag => tag.IsActive)
        .ToListAsync();
}
Lazy Loading (Optional) 
// Install: Microsoft.EntityFrameworkCore.Proxies

// Enable in DbContext
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseLazyLoadingProxies();
}

// Mark navigation properties as virtual
public class Todo
{
    public int Id { get; set; }
    public string Title { get; set; } = string.Empty;
    
    // Virtual enables lazy loading
    public virtual Category? Category { get; set; }
    public virtual List<Tag> Tags { get; set; } = new();
}

// Now navigation properties load automatically when accessed
var todo = await context.Todos.FirstAsync();
var categoryName = todo.Category?.Name; // Triggers DB query
Loading Strategy Guidance

Use eager loading with Include when you know you'll need related data—one query is better than N+1. Use explicit loading for conditional scenarios. Avoid lazy loading in most cases—it can cause N+1 query problems and doesn't work with AsNoTracking. Use an explicit join entity when you need extra columns or audit fields in many-to-many relationships.

LINQ in Practice

LINQ provides type-safe, composable queries that translate to SQL. Mastering LINQ patterns helps you write efficient, readable data access code.

Basic Filtering and Sorting

Common Query Patterns 
// Filter incomplete todos
var incompleteTodos = await context.Todos
    .Where(t => !t.IsDone)
    .ToListAsync();

// Order by due date
var sortedTodos = await context.Todos
    .OrderBy(t => t.DueDate)
    .ThenBy(t => t.Title)
    .ToListAsync();

// Find by ID
var todo = await context.Todos
    .FirstOrDefaultAsync(t => t.Id == id);

// Count
var totalTodos = await context.Todos.CountAsync();
var completedCount = await context.Todos.CountAsync(t => t.IsDone);

Pagination

Efficient Pagination 
public record PagedResult<T>(List<T> Items, int TotalCount, int Page, int PageSize);

public async Task<PagedResult<Todo>> GetPagedTodosAsync(int page, int pageSize)
{
    var query = context.Todos.Where(t => !t.IsDone);
    
    var totalCount = await query.CountAsync();
    
    var items = await query
        .OrderBy(t => t.DueDate)
        .Skip((page - 1) * pageSize)
        .Take(pageSize)
        .ToListAsync();
    
    return new PagedResult<Todo>(items, totalCount, page, pageSize);
}

Projection to DTOs

Select to DTOs 
public record TodoSummaryDto(int Id, string Title, bool IsDone, DateTime? DueDate);

// Project to DTO early to reduce data transfer
var todoSummaries = await context.Todos
    .Where(t => !t.IsDone)
    .Select(t => new TodoSummaryDto(
        t.Id,
        t.Title,
        t.IsDone,
        t.DueDate
    ))
    .ToListAsync();

// With navigation properties
public record TodoWithCategoryDto(int Id, string Title, string? CategoryName);

var todosWithCategories = await context.Todos
    .Select(t => new TodoWithCategoryDto(
        t.Id,
        t.Title,
        t.Category != null ? t.Category.Name : null
    ))
    .ToListAsync();

Advanced Queries

Grouping and Aggregation 
// Group by category
var todosByCategory = await context.Todos
    .GroupBy(t => t.CategoryId)
    .Select(g => new
    {
        CategoryId = g.Key,
        Count = g.Count(),
        CompletedCount = g.Count(t => t.IsDone)
    })
    .ToListAsync();

// Complex filtering with multiple conditions
var filteredTodos = await context.Todos
    .Where(t => 
        !t.IsDone &&
        t.DueDate.HasValue &&
        t.DueDate.Value <= DateTime.Now.AddDays(7) &&
        t.Tags.Any())
    .ToListAsync();

// Join queries
var todosWithCategories = await context.Todos
    .Join(
        context.Categories,
        todo => todo.CategoryId,
        category => category.Id,
        (todo, category) => new
        {
            TodoTitle = todo.Title,
            CategoryName = category.Name
        })
    .ToListAsync();

Dynamic Queries

Conditional Filtering 
public async Task<List<Todo>> SearchTodosAsync(
    string? searchTerm,
    bool? isDone,
    int? categoryId)
{
    var query = context.Todos.AsQueryable();
    
    if (!string.IsNullOrWhiteSpace(searchTerm))
    {
        query = query.Where(t => t.Title.Contains(searchTerm));
    }
    
    if (isDone.HasValue)
    {
        query = query.Where(t => t.IsDone == isDone.Value);
    }
    
    if (categoryId.HasValue)
    {
        query = query.Where(t => t.CategoryId == categoryId.Value);
    }
    
    return await query
        .OrderBy(t => t.DueDate)
        .ToListAsync();
}
Tip: Query Performance

Prefer AsNoTracking() for read-only queries to avoid change tracking overhead. Project to DTOs early with Select to reduce data transfer. Use Any() instead of Count() > 0 for existence checks. Always add indexes to columns used in WHERE, ORDER BY, and JOIN clauses.

Integrating EF Core with ASP.NET Core APIs

Wire EF Core into your API endpoints using dependency injection. DbContext is request-scoped by default, providing proper isolation and disposal.

Basic CRUD Endpoints

Program.cs - Minimal API Endpoints 
// GET /todos
app.MapGet("/todos", async (AppDbContext db) =>
{
    var todos = await db.Todos
        .AsNoTracking()
        .OrderBy(t => t.DueDate)
        .ToListAsync();
    
    return Results.Ok(todos);
});

// GET /todos/{id}
app.MapGet("/todos/{id}", async (int id, AppDbContext db) =>
{
    var todo = await db.Todos.FindAsync(id);
    return todo is not null ? Results.Ok(todo) : Results.NotFound();
});

// POST /todos
app.MapPost("/todos", async (CreateTodoRequest request, AppDbContext db) =>
{
    var todo = new Todo
    {
        Title = request.Title,
        DueDate = request.DueDate,
        CreatedAt = DateTime.UtcNow
    };
    
    db.Todos.Add(todo);
    await db.SaveChangesAsync();
    
    return Results.Created($"/todos/{todo.Id}", todo);
});

// PUT /todos/{id}
app.MapPut("/todos/{id}", async (int id, UpdateTodoRequest request, AppDbContext db) =>
{
    var todo = await db.Todos.FindAsync(id);
    if (todo is null) return Results.NotFound();
    
    todo.Title = request.Title;
    todo.IsDone = request.IsDone;
    todo.DueDate = request.DueDate;
    todo.UpdatedAt = DateTime.UtcNow;
    
    await db.SaveChangesAsync();
    return Results.NoContent();
});

// DELETE /todos/{id}
app.MapDelete("/todos/{id}", async (int id, AppDbContext db) =>
{
    var todo = await db.Todos.FindAsync(id);
    if (todo is null) return Results.NotFound();
    
    db.Todos.Remove(todo);
    await db.SaveChangesAsync();
    
    return Results.NoContent();
});

public record CreateTodoRequest(string Title, DateTime? DueDate);
public record UpdateTodoRequest(string Title, bool IsDone, DateTime? DueDate);

Pagination Support

Paginated Endpoint 
app.MapGet("/todos/paged", async (
    AppDbContext db,
    int page = 1,
    int pageSize = 20,
    bool? isDone = null) =>
{
    var query = db.Todos.AsQueryable();
    
    if (isDone.HasValue)
    {
        query = query.Where(t => t.IsDone == isDone.Value);
    }
    
    var totalCount = await query.CountAsync();
    
    var todos = await query
        .OrderBy(t => t.DueDate)
        .Skip((page - 1) * pageSize)
        .Take(pageSize)
        .AsNoTracking()
        .ToListAsync();
    
    return Results.Ok(new
    {
        items = todos,
        totalCount,
        page,
        pageSize,
        totalPages = (int)Math.Ceiling(totalCount / (double)pageSize)
    });
});

Error Handling

Validation and Error Handling 
app.MapPost("/todos", async (CreateTodoRequest request, AppDbContext db) =>
{
    // Validation
    if (string.IsNullOrWhiteSpace(request.Title))
    {
        return Results.ValidationProblem(new Dictionary<string, string[]>
        {
            { "title", new[] { "Title is required" } }
        });
    }
    
    if (request.Title.Length > 200)
    {
        return Results.ValidationProblem(new Dictionary<string, string[]>
        {
            { "title", new[] { "Title must be 200 characters or less" } }
        });
    }
    
    try
    {
        var todo = new Todo
        {
            Title = request.Title,
            DueDate = request.DueDate,
            CreatedAt = DateTime.UtcNow
        };
        
        db.Todos.Add(todo);
        await db.SaveChangesAsync();
        
        return Results.Created($"/todos/{todo.Id}", todo);
    }
    catch (DbUpdateException ex)
    {
        return Results.Problem(
            title: "Database error",
            detail: ex.Message,
            statusCode: 500);
    }
});
DbContext Lifetime Warning

Never register DbContext as Singleton—it's not thread-safe and holds database connections. Use the default scoped lifetime (one instance per request). Avoid long-lived contexts in background services; create a scope and resolve DbContext within that scope instead.

Transactions & Concurrency

Transactions ensure data consistency across multiple operations. Concurrency handling prevents lost updates when multiple users modify the same data.

Explicit Transactions

Transaction Example 
public async Task TransferTodoToNewCategoryAsync(int todoId, int newCategoryId)
{
    using var transaction = await context.Database.BeginTransactionAsync();
    
    try
    {
        var todo = await context.Todos.FindAsync(todoId);
        if (todo == null) throw new NotFoundException("Todo not found");
        
        var newCategory = await context.Categories.FindAsync(newCategoryId);
        if (newCategory == null) throw new NotFoundException("Category not found");
        
        // Update todo
        todo.CategoryId = newCategoryId;
        todo.UpdatedAt = DateTime.UtcNow;
        
        // Update category statistics
        newCategory.TodoCount++;
        
        await context.SaveChangesAsync();
        await transaction.CommitAsync();
    }
    catch
    {
        await transaction.RollbackAsync();
        throw;
    }
}

Optimistic Concurrency

Concurrency Token 
// Add RowVersion to entity
public class Todo
{
    public int Id { get; set; }
    public string Title { get; set; } = string.Empty;
    
    [Timestamp]
    public byte[] RowVersion { get; set; } = null!;
}

// Or use fluent API
modelBuilder.Entity<Todo>()
    .Property(t => t.RowVersion)
    .IsRowVersion();

// Handle concurrency conflicts
app.MapPut("/todos/{id}", async (int id, UpdateTodoRequest request, AppDbContext db) =>
{
    var todo = await db.Todos.FindAsync(id);
    if (todo is null) return Results.NotFound();
    
    todo.Title = request.Title;
    todo.IsDone = request.IsDone;
    
    try
    {
        await db.SaveChangesAsync();
        return Results.NoContent();
    }
    catch (DbUpdateConcurrencyException)
    {
        return Results.Conflict(new
        {
            error = "Todo was modified by another user",
            message = "Please refresh and try again"
        });
    }
});

Retry Policies with Polly

Retry Configuration 
// Install: Microsoft.Extensions.Http.Polly

builder.Services.AddDbContext<AppDbContext>((serviceProvider, options) =>
{
    options.UseSqlServer(
        connectionString,
        sqlOptions => sqlOptions.EnableRetryOnFailure(
            maxRetryCount: 3,
            maxRetryDelay: TimeSpan.FromSeconds(5),
            errorNumbersToAdd: null));
});

// Or use Polly directly
var retryPolicy = Policy
    .Handle<DbUpdateException>()
    .WaitAndRetryAsync(3, retryAttempt =>
        TimeSpan.FromSeconds(Math.Pow(2, retryAttempt)));

await retryPolicy.ExecuteAsync(async () =>
{
    await context.SaveChangesAsync();
});
Tip: Transaction Scope

SaveChanges wraps changes in a transaction automatically. Use explicit transactions only for multi-aggregate updates or when coordinating with external systems. Batching is enabled by default in EF Core 8, reducing round trips automatically.

Performance Optimization

Identify and fix performance bottlenecks through query analysis, proper indexing, and strategic use of EF Core features.

AsNoTracking for Read-Only Queries

Tracking vs No Tracking 
// With tracking (default) - slower but tracks changes
var todos = await context.Todos.ToListAsync();
todos[0].Title = "Modified";
await context.SaveChangesAsync(); // Detects and saves change

// Without tracking - faster for read-only scenarios
var todos = await context.Todos
    .AsNoTracking()
    .ToListAsync();

// Set no tracking as default for entire context
public class AppDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);
    }
}

Solving N+1 Query Problems

N+1 Problem (Bad) 
// BAD: Causes N+1 queries (1 for todos, N for categories)
var todos = await context.Todos.ToListAsync();

foreach (var todo in todos)
{
    // Each iteration triggers a separate query
    var category = await context.Categories
        .FindAsync(todo.CategoryId);
    Console.WriteLine($"{todo.Title} - {category?.Name}");
}

// Generated SQL:
// SELECT * FROM Todos
// SELECT * FROM Categories WHERE Id = 1
// SELECT * FROM Categories WHERE Id = 2
// SELECT * FROM Categories WHERE Id = 3
// ... (N more queries)
Solution: Use Include 
// GOOD: Single query with JOIN
var todos = await context.Todos
    .Include(t => t.Category)
    .ToListAsync();

foreach (var todo in todos)
{
    Console.WriteLine($"{todo.Title} - {todo.Category?.Name}");
}

// Generated SQL:
// SELECT t.*, c.*
// FROM Todos t
// LEFT JOIN Categories c ON t.CategoryId = c.Id
Solution: Split Query 
// For collections: split into separate queries to avoid cartesian explosion
var todos = await context.Todos
    .Include(t => t.Tags)
    .Include(t => t.Comments)
    .AsSplitQuery() // Splits into multiple queries
    .ToListAsync();

// Generated SQL:
// Query 1: SELECT * FROM Todos
// Query 2: SELECT * FROM Tags WHERE TodoId IN (...)
// Query 3: SELECT * FROM Comments WHERE TodoId IN (...)

Compiled Queries

Compiled Query Example 
// Define compiled query (reusable, pre-compiled)
private static readonly Func<AppDbContext, int, Task<Todo?>> _getTodoById =
    EF.CompileAsyncQuery((AppDbContext context, int id) =>
        context.Todos.FirstOrDefault(t => t.Id == id));

// Use compiled query (faster for repeated execution)
public async Task<Todo?> GetTodoByIdAsync(int id)
{
    return await _getTodoById(context, id);
}

// Compiled query with complex logic
private static readonly Func<AppDbContext, DateTime, IAsyncEnumerable<Todo>> 
    _getOverdueTodos = EF.CompileAsyncQuery(
        (AppDbContext context, DateTime date) =>
            context.Todos
                .Where(t => !t.IsDone && t.DueDate < date)
                .OrderBy(t => t.DueDate));

public IAsyncEnumerable<Todo> GetOverdueTodosAsync()
{
    return _getOverdueTodos(context, DateTime.Now);
}

Query Filtering with Interceptors

Soft Delete Interceptor 
public class SoftDeleteInterceptor : SaveChangesInterceptor
{
    public override InterceptionResult<int> SavingChanges(
        DbContextEventData eventData,
        InterceptionResult<int> result)
    {
        if (eventData.Context is null) return result;
        
        foreach (var entry in eventData.Context.ChangeTracker.Entries())
        {
            if (entry.State == EntityState.Deleted && entry.Entity is ISoftDelete entity)
            {
                entry.State = EntityState.Modified;
                entity.IsDeleted = true;
                entity.DeletedAt = DateTime.UtcNow;
            }
        }
        
        return result;
    }
}

public interface ISoftDelete
{
    bool IsDeleted { get; set; }
    DateTime? DeletedAt { get; set; }
}

// Register interceptor
builder.Services.AddDbContext<AppDbContext>((serviceProvider, options) =>
{
    options.UseSqlite(connectionString)
        .AddInterceptors(new SoftDeleteInterceptor());
});

ExecuteUpdate and ExecuteDelete

Bulk Operations (EF Core 7+) 
// Bulk update without loading entities
await context.Todos
    .Where(t => t.DueDate < DateTime.Now && !t.IsDone)
    .ExecuteUpdateAsync(setters => setters
        .SetProperty(t => t.IsDone, true)
        .SetProperty(t => t.UpdatedAt, DateTime.UtcNow));

// Bulk delete without loading entities
await context.Todos
    .Where(t => t.IsDone && t.UpdatedAt < DateTime.Now.AddMonths(-6))
    .ExecuteDeleteAsync();

// Generated SQL (efficient, single statement):
// UPDATE Todos SET IsDone = 1, UpdatedAt = @p0
// WHERE DueDate < @p1 AND IsDone = 0

Performance Best Practices

  1. Log generated SQL - Use LogTo or EnableSensitiveDataLogging in development to see actual queries
  2. Parameterize queries - Always use parameters, never string concatenation for security and plan caching
  3. Keep aggregates small - Don't load entire object graphs; use projection and filtering
  4. Cache read-mostly data - Use memory cache or distributed cache for rarely-changing reference data
  5. Prefer set-based updates - Use ExecuteUpdate/ExecuteDelete for bulk operations
  6. Add strategic indexes - Index foreign keys, WHERE clause columns, and ORDER BY fields
  7. Measure with real data - Profile queries with production-like data volumes using SQL Server Profiler or pg_stat_statements
Compiled Query Trade-offs

Compiled queries trade flexibility for speed—they're pre-compiled and can't change dynamically. Use them for hot-path queries that execute frequently with stable shapes. For dynamic queries with variable filtering, stick with standard LINQ for better maintainability.

Switching Database Providers

EF Core supports multiple database providers through NuGet packages. You can develop on SQLite and deploy to PostgreSQL or SQL Server without changing your code.

Multi-Provider Configuration

appsettings.json - Multiple Connections 
{
  "DatabaseProvider": "Sqlite",
  "ConnectionStrings": {
    "Sqlite": "Data Source=todos.db",
    "PostgreSQL": "Host=localhost;Database=todosdb;Username=postgres;Password=pass",
    "SqlServer": "Server=localhost;Database=TodosDB;Trusted_Connection=True;TrustServerCertificate=True"
  }
}
Program.cs - Provider Selection 
var provider = builder.Configuration["DatabaseProvider"] ?? "Sqlite";

builder.Services.AddDbContext<AppDbContext>(options =>
{
    switch (provider)
    {
        case "Sqlite":
            options.UseSqlite(builder.Configuration.GetConnectionString("Sqlite"));
            break;
        
        case "PostgreSQL":
            options.UseNpgsql(builder.Configuration.GetConnectionString("PostgreSQL"));
            break;
        
        case "SqlServer":
            options.UseSqlServer(builder.Configuration.GetConnectionString("SqlServer"));
            break;
        
        default:
            throw new InvalidOperationException($"Unsupported provider: {provider}");
    }
    
    if (builder.Environment.IsDevelopment())
    {
        options.EnableSensitiveDataLogging();
        options.EnableDetailedErrors();
    }
});

Provider-Specific Features

SQLite Features 
// SQLite date/time functions
modelBuilder.Entity<Todo>()
    .Property(t => t.CreatedAt)
    .HasDefaultValueSql("datetime('now')");

// Limited concurrency support (file-based)
// Good for: development, testing, small apps
// Not ideal for: high concurrency, production at scale

// Enable foreign keys (off by default)
options.UseSqlite(connectionString, sqliteOptions =>
{
    sqliteOptions.CommandTimeout(60);
});
PostgreSQL Features 
// JSON column support
modelBuilder.Entity<Todo>()
    .Property(t => t.Metadata)
    .HasColumnType("jsonb");

// Array types
public class Todo
{
    public string[] Tags { get; set; } = Array.Empty<string>();
}

// Full-text search
var todos = await context.Todos
    .Where(t => EF.Functions.ToTsVector("english", t.Title)
        .Matches(EF.Functions.ToTsQuery("english", "search term")))
    .ToListAsync();

// Case-insensitive comparison
var todos = await context.Todos
    .Where(t => EF.Functions.ILike(t.Title, "%search%"))
    .ToListAsync();
SQL Server Features 
// JSON columns (SQL Server 2016+)
modelBuilder.Entity<Todo>()
    .Property(t => t.Metadata)
    .HasColumnType("nvarchar(max)");

// Temporal tables (SQL Server 2016+)
modelBuilder.Entity<Todo>()
    .ToTable("Todos", t => t.IsTemporal());

// Query temporal data
var historicalTodos = await context.Todos
    .TemporalAsOf(DateTime.UtcNow.AddDays(-7))
    .ToListAsync();

// Memory-optimized tables
modelBuilder.Entity<Todo>()
    .ToTable("Todos", t => t.IsMemoryOptimized());

Environment-Based Provider Selection

Environment Configuration 
builder.Services.AddDbContext<AppDbContext>(options =>
{
    if (builder.Environment.IsDevelopment())
    {
        // Use SQLite for local development
        options.UseSqlite("Data Source=todos.db");
    }
    else if (builder.Environment.IsStaging())
    {
        // Use PostgreSQL for staging
        options.UseNpgsql(builder.Configuration.GetConnectionString("Staging"));
    }
    else
    {
        // Use SQL Server for production
        options.UseSqlServer(builder.Configuration.GetConnectionString("Production"));
    }
});
Provider Migration Strategy

Use environment-based provider selection for smooth transitions between development and production. Document provider-specific migrations and features. Test migrations on each target database before production deployment. Most LINQ queries work across providers, but test SQL generation for provider-specific optimizations.

Testing Data Access

Test your data access code without a full database server using in-memory providers. Choose between EF Core InMemory (fast but non-relational) or SQLite in-memory (slower but relational).

SQLite In-Memory Testing

SQLite In-Memory Setup 
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using Xunit;

public class TodoRepositoryTests : IDisposable
{
    private readonly SqliteConnection _connection;
    private readonly AppDbContext _context;

    public TodoRepositoryTests()
    {
        // Create in-memory SQLite connection
        _connection = new SqliteConnection("DataSource=:memory:");
        _connection.Open();

        var options = new DbContextOptionsBuilder<AppDbContext>()
            .UseSqlite(_connection)
            .Options;

        _context = new AppDbContext(options);
        _context.Database.EnsureCreated();
    }

    [Fact]
    public async Task CreateTodo_SavesSuccessfully()
    {
        // Arrange
        var todo = new Todo
        {
            Title = "Test Todo",
            CreatedAt = DateTime.UtcNow
        };

        // Act
        _context.Todos.Add(todo);
        await _context.SaveChangesAsync();

        // Assert
        var savedTodo = await _context.Todos.FirstOrDefaultAsync();
        Assert.NotNull(savedTodo);
        Assert.Equal("Test Todo", savedTodo.Title);
    }

    public void Dispose()
    {
        _context.Dispose();
        _connection.Dispose();
    }
}

Testing with Factory Pattern

Test Database Factory 
public class TestDatabaseFactory
{
    public static AppDbContext CreateInMemoryDatabase()
    {
        var connection = new SqliteConnection("DataSource=:memory:");
        connection.Open();

        var options = new DbContextOptionsBuilder<AppDbContext>()
            .UseSqlite(connection)
            .Options;

        var context = new AppDbContext(options);
        context.Database.EnsureCreated();

        return context;
    }

    public static async Task<AppDbContext> CreateSeededDatabase()
    {
        var context = CreateInMemoryDatabase();

        // Seed test data
        context.Todos.AddRange(
            new Todo { Title = "Todo 1", IsDone = false, CreatedAt = DateTime.UtcNow },
            new Todo { Title = "Todo 2", IsDone = true, CreatedAt = DateTime.UtcNow }
        );

        await context.SaveChangesAsync();
        return context;
    }
}

// Usage in tests
public class TodoServiceTests
{
    [Fact]
    public async Task GetIncompleteTodos_ReturnsOnlyIncomplete()
    {
        // Arrange
        await using var context = await TestDatabaseFactory.CreateSeededDatabase();
        var service = new TodoService(context);

        // Act
        var incompleteTodos = await service.GetIncompleteTodosAsync();

        // Assert
        Assert.Single(incompleteTodos);
        Assert.False(incompleteTodos[0].IsDone);
    }
}

Testing Concurrency

Concurrency Test 
[Fact]
public async Task UpdateTodo_ConcurrentModification_ThrowsConcurrencyException()
{
    // Arrange
    await using var context1 = TestDatabaseFactory.CreateInMemoryDatabase();
    await using var context2 = TestDatabaseFactory.CreateInMemoryDatabase();

    var todo = new Todo { Title = "Original", CreatedAt = DateTime.UtcNow };
    context1.Todos.Add(todo);
    await context1.SaveChangesAsync();

    // Load same todo in two contexts
    var todo1 = await context1.Todos.FindAsync(todo.Id);
    var todo2 = await context2.Todos.FindAsync(todo.Id);

    // Act
    todo1!.Title = "Modified by User 1";
    await context1.SaveChangesAsync();

    todo2!.Title = "Modified by User 2";

    // Assert
    await Assert.ThrowsAsync<DbUpdateConcurrencyException>(async () =>
    {
        await context2.SaveChangesAsync();
    });
}

InMemory Provider (Alternative)

InMemory Provider 
// Install: Microsoft.EntityFrameworkCore.InMemory

public class TodoServiceTests
{
    [Fact]
    public async Task GetAllTodos_ReturnsAllTodos()
    {
        // Arrange
        var options = new DbContextOptionsBuilder<AppDbContext>()
            .UseInMemoryDatabase(databaseName: "TestDatabase")
            .Options;

        await using var context = new AppDbContext(options);

        context.Todos.AddRange(
            new Todo { Title = "Todo 1", CreatedAt = DateTime.UtcNow },
            new Todo { Title = "Todo 2", CreatedAt = DateTime.UtcNow }
        );
        await context.SaveChangesAsync();

        var service = new TodoService(context);

        // Act
        var todos = await service.GetAllAsync();

        // Assert
        Assert.Equal(2, todos.Count);
    }
}
Tip: Testing Strategy

Use SQLite in-memory for tests requiring relational behavior (foreign keys, constraints, transactions). Reserve InMemory provider for fast unit tests where relational semantics don't matter. Avoid mocking DbSet—use real providers for more reliable tests.

Next Steps & Resources

You've learned EF Core 8 fundamentals. Here's where to continue your data access journey:

Related Tutorials on dotnet-guide.com

Official Documentation

GitHub Example Projects

Clone complete Todo API examples with basic and advanced implementations:

Clone Repository 
git clone https://github.com/dotnet-guide-com/tutorials.git
cd tutorials/ef-core

# Run basic Todo API (SQLite)
cd TodoApiEfCoreBasic
dotnet ef database update
dotnet run

# Run advanced version (PostgreSQL + transactions + compiled queries)
cd ../TodoApiEfCoreAdvanced
# Update connection string in appsettings.json
dotnet ef database update
dotnet run

Community Resources

NuGet Packages

  • Microsoft.EntityFrameworkCore.Sqlite - SQLite provider
  • Npgsql.EntityFrameworkCore.PostgreSQL - PostgreSQL provider
  • Microsoft.EntityFrameworkCore.SqlServer - SQL Server provider
  • Microsoft.EntityFrameworkCore.Design - Design-time tools for migrations
  • Microsoft.EntityFrameworkCore.Tools - Package Manager Console tools
View on GitHub

Frequently Asked Questions

EF Core 8 vs 7—what's meaningfully better?

EF Core 8 brings smarter batching that reduces database round trips in SaveChanges, improved raw SQL mapping to DTOs with better parameterization, enhanced compiled query support for faster repeated executions, and provider enhancements including JSON columns for SQL Server and PostgreSQL. It aligns with .NET 8 LTS through November 2026. Upgrade for performance gains in high-throughput APIs and better JSON handling. Most applications see 10-30% performance improvements in batch operations.

When to use repositories vs direct DbContext?

Use DbContext directly in simple applications—it already implements the Unit of Work pattern and provides a clean API. Add repository abstractions when you need isolation for testing without database dependencies, complex business rules that span multiple entities, or the ability to switch data stores (though this is rare). Over-abstracting EF Core often creates unnecessary layers that obscure LINQ's power. Start simple with DbContext in your services, refactor to repositories only when you have a concrete reason.

How to profile slow LINQ queries?

Enable sensitive data logging in development with EnableSensitiveDataLogging() and LogTo(Console.WriteLine) to see generated SQL with parameter values. Use SQL Server Profiler or Query Store for SQL Server, or pg_stat_statements extension for PostgreSQL to capture and analyze queries. Check for N+1 problems by looking for repeated similar queries—fix with Include for related data. Use AsNoTracking() for read-only queries. Measure with tools like MiniProfiler, Application Insights, or Datadog APM for production diagnostics.

Is InMemory provider reliable for relational behaviors?

No. The InMemory provider doesn't enforce referential integrity, foreign key constraints, or relational semantics like cascading deletes. It's designed for fast unit tests where relational behavior doesn't matter. Use SQLite in-memory mode with OpenConnection() for real relational behavior in tests—it's slightly slower but provides accurate database semantics including constraints, triggers, and transactions. Reserve InMemory for testing business logic that doesn't depend on database features.

When to prefer raw SQL over LINQ?

Use raw SQL for complex reports with multiple joins and aggregations where EF generates inefficient SQL, for stored procedures that encapsulate business logic in the database, for database-specific features like full-text search or JSON operations, or when you need precise control over query execution plans. Use FromSqlRaw or ExecuteSqlRaw with proper parameterization to prevent SQL injection. Keep LINQ for standard CRUD operations—type safety, refactoring support, and maintainability usually outweigh raw SQL's flexibility.

How to handle very large result sets?

Use pagination with Skip/Take on indexed columns to load data in manageable chunks—this is the most common approach for web APIs. Use AsAsyncEnumerable with await foreach to stream large datasets without loading everything into memory. Project to DTOs early with Select to reduce data transfer by selecting only needed columns. Consider read-only views, materialized data, or denormalized tables for heavy reporting queries. For very large exports, use background jobs with progress tracking rather than synchronous API endpoints.

EF Core vs Dapper—when to use which?

Use EF Core for full-stack CRUD applications with complex relationships, change tracking, migrations, and where developer productivity matters. EF Core excels at domain-driven design with rich entity models. Use Dapper for read-heavy APIs, microservices focused on queries, or when you need maximum query control and performance—Dapper is a thin wrapper over ADO.NET with minimal overhead. Consider a hybrid approach: EF Core for writes (benefit from change tracking and transactions) and Dapper for complex reads (hand-tuned SQL for performance).

Best database provider for cloud deployment?

Azure SQL Database integrates seamlessly with Azure services, offers managed scaling, automatic backups, and built-in high availability. It's the easiest choice if you're on Azure. PostgreSQL (via Azure Database for PostgreSQL, AWS RDS, or managed services) provides excellent performance, strong JSON support, lower costs, and works across all major clouds. Choose based on your cloud platform, budget, and feature needs. Both have mature EF Core providers. For serverless scenarios, consider Azure SQL Database Serverless or Aurora Serverless (PostgreSQL-compatible) for automatic scaling and pay-per-use pricing.

Back to Tutorials