Entity Framework Core: Database Access in .NET

What is EF Core?

Entity Framework Core (EF Core) is .NET's official Object-Relational Mapper (ORM). It lets you work with your database using C# objects instead of raw SQL.

EF Core vs raw SQL:

EF Core: less code, type-safe, automatic change tracking, migrations
Raw SQL (Dapper): more control, better performance for complex queries

Most production apps use EF Core for standard CRUD and raw SQL (via FromSql or Dapper) for complex analytics queries.

Setup

Bash

dotnet add package Microsoft.EntityFrameworkCore
dotnet add package Microsoft.EntityFrameworkCore.SqlServer    # or Npgsql.EntityFrameworkCore.PostgreSQL
dotnet add package Microsoft.EntityFrameworkCore.Design        # for migrations
dotnet tool install --global dotnet-ef

Defining Entities

// Entities map to database tables
public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public string Email { get; set; } = string.Empty;
    public string Tier { get; set; } = "free";
    public DateTime CreatedAt { get; set; }
    public bool IsActive { get; set; } = true;

    // Navigation properties (relationships)
    public ICollection<Order> Orders { get; set; } = new List<Order>();
}

public class Order
{
    public int Id { get; set; }
    public int CustomerId { get; set; }
    public decimal Total { get; set; }
    public string Status { get; set; } = "pending";
    public DateTime CreatedAt { get; set; }

    // Navigation properties
    public Customer Customer { get; set; } = null!;
    public ICollection<OrderItem> Items { get; set; } = new List<OrderItem>();
}

public class OrderItem
{
    public int Id { get; set; }
    public int OrderId { get; set; }
    public int ProductId { get; set; }
    public int Quantity { get; set; }
    public decimal UnitPrice { get; set; }

    public Order Order { get; set; } = null!;
    public Product Product { get; set; } = null!;
}

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public string Sku { get; set; } = string.Empty;
    public decimal Price { get; set; }
    public int Stock { get; set; }
    public bool IsActive { get; set; } = true;
}

DbContext

The DbContext is the gateway to your database.

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

    public DbSet<Customer> Customers => Set<Customer>();
    public DbSet<Order> Orders => Set<Order>();
    public DbSet<OrderItem> OrderItems => Set<OrderItem>();
    public DbSet<Product> Products => Set<Product>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Apply all IEntityTypeConfiguration classes in this assembly
        modelBuilder.ApplyConfigurationsFromAssembly(Assembly.GetExecutingAssembly());
    }

    // Automatically set CreatedAt and UpdatedAt
    public override async Task<int> SaveChangesAsync(CancellationToken ct = default)
    {
        foreach (var entry in ChangeTracker.Entries())
        {
            if (entry.Entity is IAuditableEntity auditable)
            {
                if (entry.State == EntityState.Added)
                    auditable.CreatedAt = DateTime.UtcNow;
                if (entry.State is EntityState.Added or EntityState.Modified)
                    auditable.UpdatedAt = DateTime.UtcNow;
            }
        }
        return await base.SaveChangesAsync(ct);
    }
}

// Register in Program.cs
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("Default")));

Fluent API Configuration

Instead of data annotations on entities (which couples them to EF), use configuration classes.

public class CustomerConfiguration : IEntityTypeConfiguration<Customer>
{
    public void Configure(EntityTypeBuilder<Customer> builder)
    {
        builder.ToTable("Customers");
        builder.HasKey(c => c.Id);

        builder.Property(c => c.Name)
            .IsRequired()
            .HasMaxLength(100);

        builder.Property(c => c.Email)
            .IsRequired()
            .HasMaxLength(255);

        builder.HasIndex(c => c.Email)
            .IsUnique();

        builder.Property(c => c.Tier)
            .IsRequired()
            .HasDefaultValue("free");

        // One-to-many: customer has many orders
        builder.HasMany(c => c.Orders)
            .WithOne(o => o.Customer)
            .HasForeignKey(o => o.CustomerId)
            .OnDelete(DeleteBehavior.Restrict);  // don't cascade delete orders
    }
}

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

        builder.Property(o => o.Total)
            .HasColumnType("decimal(10,2)");

        builder.Property(o => o.Status)
            .HasConversion<string>()  // store enum as string
            .IsRequired();

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

        // Index for common queries
        builder.HasIndex(o => new { o.CustomerId, o.Status });
        builder.HasIndex(o => o.CreatedAt);
    }
}

Migrations

Migrations track schema changes as C# code.

Bash

# Create a migration
dotnet ef migrations add InitialCreate

# Apply to database
dotnet ef database update

# Create a migration for a specific change
dotnet ef migrations add AddProductSku

# View SQL that will be run (without applying)
dotnet ef migrations script

# Roll back to a previous migration
dotnet ef database update AddProductSku

Generated migration file:

public partial class InitialCreate : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "Customers",
            columns: table => new
            {
                Id = table.Column<int>(nullable: false)
                    .Annotation("SqlServer:Identity", "1, 1"),
                Name = table.Column<string>(maxLength: 100, nullable: false),
                Email = table.Column<string>(maxLength: 255, nullable: false),
                // ...
            },
            constraints: table => table.PrimaryKey("PK_Customers", x => x.Id));

        migrationBuilder.CreateIndex(
            name: "IX_Customers_Email",
            table: "Customers",
            column: "Email",
            unique: true);
    }

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

Querying

// Basic queries
var customers = await _context.Customers
    .Where(c => c.IsActive)
    .OrderBy(c => c.Name)
    .ToListAsync(ct);

// Find by primary key (uses cache first)
var customer = await _context.Customers.FindAsync(id);

// Single with navigation properties
var order = await _context.Orders
    .Include(o => o.Customer)
    .Include(o => o.Items)
        .ThenInclude(i => i.Product)
    .FirstOrDefaultAsync(o => o.Id == orderId, ct);

// Projection — only fetch what you need
var dtos = await _context.Orders
    .Where(o => o.CustomerId == customerId)
    .Select(o => new OrderSummaryDto
    {
        Id = o.Id,
        Total = o.Total,
        Status = o.Status,
        ItemCount = o.Items.Count,
        CreatedAt = o.CreatedAt,
    })
    .OrderByDescending(o => o.CreatedAt)
    .ToListAsync(ct);

// Pagination
var page = await _context.Orders
    .OrderByDescending(o => o.CreatedAt)
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize)
    .ToListAsync(ct);

// Count
var total = await _context.Orders
    .Where(o => o.CustomerId == customerId)
    .CountAsync(ct);

Writing Data

// Insert
var customer = new Customer
{
    Name = "Alice Smith",
    Email = "alice@example.com",
    CreatedAt = DateTime.UtcNow
};
_context.Customers.Add(customer);
await _context.SaveChangesAsync(ct);
// customer.Id is now populated

// Add range
var products = new List<Product> { product1, product2 };
_context.Products.AddRange(products);
await _context.SaveChangesAsync(ct);

// Update — change tracking handles the UPDATE SQL
var customer = await _context.Customers.FindAsync(id);
if (customer is not null)
{
    customer.Name = "Alice Johnson";
    customer.Tier = "pro";
    await _context.SaveChangesAsync(ct);
}

// Update without loading entity (saves a query)
await _context.Customers
    .Where(c => c.Id == id)
    .ExecuteUpdateAsync(s => s
        .SetProperty(c => c.Tier, "pro")
        .SetProperty(c => c.UpdatedAt, DateTime.UtcNow),
        ct);

// Delete
var customer = await _context.Customers.FindAsync(id);
_context.Customers.Remove(customer!);
await _context.SaveChangesAsync(ct);

// Delete without loading entity (EF Core 7+)
await _context.Customers
    .Where(c => c.Id == id)
    .ExecuteDeleteAsync(ct);

Transactions

// Using transactions for multi-step operations
await using var transaction = await _context.Database.BeginTransactionAsync(ct);
try
{
    var order = new Order { CustomerId = customerId, Status = "pending" };
    _context.Orders.Add(order);
    await _context.SaveChangesAsync(ct);

    // Deduct stock
    foreach (var item in orderItems)
    {
        await _context.Products
            .Where(p => p.Id == item.ProductId)
            .ExecuteUpdateAsync(s => s.SetProperty(
                p => p.Stock,
                p => p.Stock - item.Quantity), ct);
    }

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

Raw SQL

Use raw SQL when EF's LINQ can't express your query, or for performance.

// FromSqlRaw — returns entities
var orders = await _context.Orders
    .FromSqlRaw("SELECT * FROM Orders WHERE Status = {0}", "delivered")
    .Include(o => o.Customer)
    .ToListAsync(ct);

// NEVER do this (SQL injection risk!):
// .FromSqlRaw($"WHERE Status = '{status}'")

// Use parameters:
var orders = await _context.Orders
    .FromSqlInterpolated($"SELECT * FROM Orders WHERE Status = {status}")
    .ToListAsync(ct);

// ExecuteSqlRawAsync — for INSERT/UPDATE/DELETE that don't return entities
int rows = await _context.Database.ExecuteSqlInterpolatedAsync(
    $"UPDATE Products SET Stock = Stock - {qty} WHERE Id = {productId}",
    ct);

// Dapper alongside EF Core (for complex analytics)
using var connection = _context.Database.GetDbConnection();
var stats = await connection.QueryAsync<CustomerStats>(
    "SELECT customer_id, SUM(total) as total FROM orders GROUP BY customer_id");

N+1 Query Problem

The most common EF Core performance mistake.

// BAD: N+1 queries — 1 query for orders, then 1 per order for customer
var orders = await _context.Orders.ToListAsync();
foreach (var order in orders)
{
    Console.WriteLine(order.Customer.Name);  // lazy load triggers query each time!
}

// FIX 1: Eager loading
var orders = await _context.Orders
    .Include(o => o.Customer)  // JOIN in SQL
    .ToListAsync();

// FIX 2: Explicit load
var orders = await _context.Orders.ToListAsync();
var customerIds = orders.Select(o => o.CustomerId).Distinct();
var customers = await _context.Customers
    .Where(c => customerIds.Contains(c.Id))
    .ToDictionaryAsync(c => c.Id);

// FIX 3: Projection (best for read-only)
var dtos = await _context.Orders
    .Select(o => new { o.Id, CustomerName = o.Customer.Name })
    .ToListAsync();

Disable lazy loading (it's off by default in EF Core — keep it that way).

Soft Deletes

Don't physically delete records; mark them as deleted.

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

public class Customer : ISoftDeletable
{
    // ...
    public bool IsDeleted { get; set; }
    public DateTime? DeletedAt { get; set; }
}

// Override SaveChanges to convert Delete → Update
public override async Task<int> SaveChangesAsync(CancellationToken ct = default)
{
    foreach (var entry in ChangeTracker.Entries<ISoftDeletable>())
    {
        if (entry.State == EntityState.Deleted)
        {
            entry.State = EntityState.Modified;
            entry.Entity.IsDeleted = true;
            entry.Entity.DeletedAt = DateTime.UtcNow;
        }
    }
    return await base.SaveChangesAsync(ct);
}

// Global query filter — automatically excludes soft-deleted rows
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Customer>().HasQueryFilter(c => !c.IsDeleted);
    modelBuilder.Entity<Order>().HasQueryFilter(o => !o.IsDeleted);
    // Now: _context.Customers.ToList() NEVER includes deleted customers
}

// To include deleted records:
var allCustomers = await _context.Customers
    .IgnoreQueryFilters()
    .ToListAsync();

Value Objects

Encapsulate complex values in their own types.

// Value object: Money
[Owned]
public class Money
{
    public decimal Amount { get; private set; }
    public string Currency { get; private set; }

    public Money(decimal amount, string currency)
    {
        Amount = amount;
        Currency = currency;
    }

    public static Money Zero(string currency) => new(0, currency);
    public Money Add(Money other)
    {
        if (Currency != other.Currency) throw new InvalidOperationException("Currency mismatch");
        return new Money(Amount + other.Amount, Currency);
    }
}

// Use in entity
public class Order
{
    public int Id { get; set; }
    public Money Total { get; set; } = Money.Zero("GBP");  // stored as Amount + Currency columns
}

// Configuration
builder.OwnsOne(o => o.Total, m =>
{
    m.Property(x => x.Amount).HasColumnName("TotalAmount").HasColumnType("decimal(10,2)");
    m.Property(x => x.Currency).HasColumnName("TotalCurrency").HasMaxLength(3);
});

Performance Best Practices

// 1. AsNoTracking for read-only queries (no change tracker overhead)
var customers = await _context.Customers
    .AsNoTracking()
    .Where(c => c.IsActive)
    .ToListAsync();

// 2. AsNoTrackingWithIdentityResolution for read-only with navigation
var orders = await _context.Orders
    .AsNoTrackingWithIdentityResolution()
    .Include(o => o.Items)
    .ToListAsync();

// 3. Use projection to avoid fetching unnecessary columns
var names = await _context.Customers
    .Select(c => c.Name)  // only fetches Name column
    .ToListAsync();

// 4. Split queries for multiple collections (avoids cartesian explosion)
var orders = await _context.Orders
    .Include(o => o.Items)
    .Include(o => o.Tags)
    .AsSplitQuery()  // 3 separate queries instead of 1 JOIN with many rows
    .ToListAsync();

// 5. Compiled queries for hot paths
private static readonly Func<AppDbContext, int, Task<Order?>> GetOrderById =
    EF.CompileAsyncQuery((AppDbContext ctx, int id) =>
        ctx.Orders.Include(o => o.Items).FirstOrDefault(o => o.Id == id));

var order = await GetOrderById(_context, 42);

// 6. Check the SQL EF generates
var sql = _context.Orders.Where(o => o.CustomerId == 1).ToQueryString();
Console.WriteLine(sql);

// Or in development, log SQL
builder.Services.AddDbContext<AppDbContext>(options =>
    options
        .UseSqlServer(connectionString)
        .LogTo(Console.WriteLine, LogLevel.Information)
        .EnableSensitiveDataLogging());  // development only!

Repository Pattern with EF Core

public class OrderRepository : IOrderRepository
{
    private readonly AppDbContext _context;

    public OrderRepository(AppDbContext context) => _context = context;

    public async Task<Order?> GetByIdAsync(int id, CancellationToken ct = default)
        => await _context.Orders
            .Include(o => o.Customer)
            .Include(o => o.Items).ThenInclude(i => i.Product)
            .AsNoTracking()
            .FirstOrDefaultAsync(o => o.Id == id, ct);

    public async Task<PagedResult<Order>> GetPagedAsync(
        int page, int size, string? status, CancellationToken ct = default)
    {
        var query = _context.Orders
            .AsNoTracking()
            .Where(o => status == null || o.Status == status);

        var total = await query.CountAsync(ct);
        var items = await query
            .OrderByDescending(o => o.CreatedAt)
            .Skip((page - 1) * size)
            .Take(size)
            .ToListAsync(ct);

        return new PagedResult<Order>(items, page, size, total);
    }

    public async Task<int> CreateAsync(Order order, CancellationToken ct = default)
    {
        _context.Orders.Add(order);
        await _context.SaveChangesAsync(ct);
        return order.Id;
    }
}

What to Learn Next

Clean Architecture in .NET: How to structure your entire application
Advanced .NET Patterns: CQRS, MediatR, event sourcing
.NET Interview Questions (Mid-Level): EF Core gotchas, performance questions