Entity Framework Core

Setting Up EF Core

Entity Framework Core is .NET’s official object-relational mapper (ORM). It lets you work with a database using C# classes and LINQ instead of raw SQL, and it generates and applies the database schema from your model through a migration system. This means your database schema is version-controlled alongside your code, and changes to the model produce incremental, reviewable migration files.

dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Tools
dotnet add package Microsoft.EntityFrameworkCore.Design

Defining the Model

The model is the heart of EF Core — plain C# classes (entities) whose properties map to database columns. Navigation properties express relationships between entities: a Customer has many Orders, and each Order has many OrderLines. EF Core infers most of the schema from conventions (e.g., a property named Id becomes the primary key), and you override the defaults in OnModelCreating when you need constraints, indexes, or non-default column types.

// Entities — plain C# classes, no base class required
public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public string Email { get; set; } = "";
    public bool IsActive { get; set; } = true;
    public DateTime CreatedAt { get; set; }

    // Navigation property — EF Core uses this to build the JOIN in queries
    public List<Order> Orders { get; set; } = new();
}

public class Order
{
    public int Id { get; set; }
    public int CustomerId { get; set; }   // foreign key — convention: {NavigationName}Id
    public decimal Total { get; set; }
    public string Status { get; set; } = "Pending";
    public DateTime OrderDate { get; set; }

    // Navigation properties — back-reference and child collection
    public Customer Customer { get; set; } = null!;
    public List<OrderLine> Lines { get; set; } = new();
}

public class OrderLine
{
    public int Id { get; set; }
    public int OrderId { get; set; }
    public string ProductName { get; set; } = "";
    public int Quantity { get; set; }
    public decimal UnitPrice { get; set; }
    public decimal Total => Quantity * UnitPrice;  // computed — not stored in DB

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

DbContext

DbContext is the unit of work and the entry point for all database operations. Each DbSet<T> property represents a database table. OnModelCreating is where you apply explicit configuration: required fields, max lengths, indexes, column types, relationships, and delete behaviors. Registering AppDbContext with the DI container makes it available throughout your application with the correct lifetime (scoped per HTTP request by default).

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

    // Each DbSet maps to a table — the name of the DbSet becomes the table name
    public DbSet<Customer> Customers => Set<Customer>();
    public DbSet<Order> Orders => Set<Order>();
    public DbSet<OrderLine> OrderLines => Set<OrderLine>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Customer — explicit constraints and a unique index on email
        modelBuilder.Entity<Customer>(e =>
        {
            e.HasKey(c => c.Id);
            e.Property(c => c.Name).IsRequired().HasMaxLength(200);
            e.Property(c => c.Email).IsRequired().HasMaxLength(254);
            e.HasIndex(c => c.Email).IsUnique();  // enforce unique emails at DB level
            e.Property(c => c.CreatedAt)
             .HasDefaultValueSql("GETUTCDATE()");  // set by the database on insert
        });

        // Order — decimal precision and relationship to Customer
        modelBuilder.Entity<Order>(e =>
        {
            e.HasKey(o => o.Id);
            e.Property(o => o.Total).HasColumnType("decimal(18,2)");
            e.Property(o => o.Status).HasMaxLength(50);

            // Restrict delete — can't delete a customer who has orders
            e.HasOne(o => o.Customer)
             .WithMany(c => c.Orders)
             .HasForeignKey(o => o.CustomerId)
             .OnDelete(DeleteBehavior.Restrict);
        });

        // OrderLine — cascade delete (deleting an order removes its lines)
        modelBuilder.Entity<OrderLine>(e =>
        {
            e.HasKey(l => l.Id);
            e.Property(l => l.UnitPrice).HasColumnType("decimal(18,2)");
            e.Ignore(l => l.Total);   // computed property — do not create a column

            e.HasOne(l => l.Order)
             .WithMany(o => o.Lines)
             .HasForeignKey(l => l.OrderId)
             .OnDelete(DeleteBehavior.Cascade);
        });
    }
}

// Register in DI — scoped lifetime, one instance per HTTP request
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("Default")));

Migrations

Migrations are the version control system for your database schema. Each migration is a C# file describing a forward (Up) and rollback (Down) operation. You commit migration files to source control so every developer and deployment environment can apply the same incremental schema changes without manual SQL scripts.

# Create a migration — captures the difference between your model and the last migration
dotnet ef migrations add InitialCreate

# Apply all pending migrations to the database
dotnet ef database update

# Roll back to a specific migration (runs the Down method of migrations after it)
dotnet ef database update PreviousMigrationName

# Generate a SQL script for production deployments — review before running
dotnet ef migrations script --output migration.sql

# Remove the last migration if it has not been applied yet
dotnet ef migrations remove

Apply migrations automatically at startup during development:

// In Program.cs — convenient for development, use CI/CD scripts in production
using var scope = app.Services.CreateScope();
var db = scope.ServiceProvider.GetRequiredService<AppDbContext>();
await db.Database.MigrateAsync();

LINQ Queries

EF Core translates LINQ expressions into SQL at runtime. The key habit is to keep the query as an IQueryable<T> — applying filters, projections, and ordering — and only materialize it (with ToListAsync, FirstOrDefaultAsync, etc.) at the very end. This ensures the full query is sent to the database as a single SQL statement, rather than loading rows into memory and filtering them in C#. Use AsNoTracking() on all read-only queries to skip the change-tracking overhead.

public class CustomerRepository
{
    private readonly AppDbContext _db;
    public CustomerRepository(AppDbContext db) => _db = db;

    // Read-only query — AsNoTracking skips change tracking for better performance
    public async Task<List<Customer>> GetActiveAsync()
        => await _db.Customers
                    .Where(c => c.IsActive)
                    .OrderBy(c => c.Name)
                    .AsNoTracking()
                    .ToListAsync();

    // Projection — SELECT only the columns you need, not the full entity
    public async Task<List<CustomerSummary>> GetSummariesAsync()
        => await _db.Customers
                    .Where(c => c.IsActive)
                    .Select(c => new CustomerSummary(
                        c.Id, c.Name, c.Email,
                        c.Orders.Count(o => o.Status != "Cancelled"),
                        c.Orders.Sum(o => o.Total)))
                    .AsNoTracking()
                    .ToListAsync();

    // Eager loading — Include pulls related entities in one JOIN query
    // Avoids N+1: without Include, accessing c.Orders would fire one query per customer
    public async Task<Customer?> GetWithOrdersAsync(int id)
        => await _db.Customers
                    .Include(c => c.Orders)
                    .ThenInclude(o => o.Lines)  // include the grandchild collection too
                    .AsNoTracking()
                    .FirstOrDefaultAsync(c => c.Id == id);

    // Paging — always apply ordering before Skip/Take for deterministic results
    public async Task<(List<Customer> Items, int Total)> GetPageAsync(
        int page, int pageSize, string? search = null)
    {
        var query = _db.Customers.Where(c => c.IsActive);

        if (!string.IsNullOrWhiteSpace(search))
            query = query.Where(c => c.Name.Contains(search)
                                  || c.Email.Contains(search));

        int total = await query.CountAsync();  // single COUNT query
        var items = await query
            .OrderBy(c => c.Name)
            .Skip((page - 1) * pageSize)
            .Take(pageSize)
            .AsNoTracking()
            .ToListAsync();

        return (items, total);
    }
}

Async CRUD Operations

All EF Core operations have async counterparts — always use them in async methods to avoid blocking thread pool threads while the database query is in flight. EF 7 added ExecuteDeleteAsync and ExecuteUpdateAsync for bulk operations that modify rows directly in the database without loading entities first, which is dramatically more efficient for large datasets.

public class OrderService
{
    private readonly AppDbContext _db;
    public OrderService(AppDbContext db) => _db = db;

    // Create — add entity, then call SaveChangesAsync to persist
    public async Task<Order> CreateAsync(int customerId, List<(string product, int qty, decimal price)> lines)
    {
        var order = new Order
        {
            CustomerId = customerId,
            OrderDate  = DateTime.UtcNow,
            Status     = "Pending",
        };
        order.Lines = lines.Select(l => new OrderLine
        {
            ProductName = l.product,
            Quantity    = l.qty,
            UnitPrice   = l.price
        }).ToList();
        order.Total = order.Lines.Sum(l => l.Quantity * l.UnitPrice);

        _db.Orders.Add(order);
        await _db.SaveChangesAsync();  // generates INSERT statements for order + lines
        return order;
    }

    // Update — load the entity, modify it, then SaveChanges (change tracking detects the diff)
    public async Task<bool> ShipAsync(int orderId)
    {
        var order = await _db.Orders.FindAsync(orderId);
        if (order is null || order.Status != "Pending") return false;

        order.Status = "Shipped";      // change tracker marks this property as modified
        await _db.SaveChangesAsync();  // generates UPDATE Orders SET Status='Shipped' WHERE Id=...
        return true;
    }

    // Delete without loading — EF 7+ ExecuteDeleteAsync sends DELETE directly to the DB
    public async Task DeleteAsync(int orderId)
    {
        await _db.Orders
                 .Where(o => o.Id == orderId)
                 .ExecuteDeleteAsync();
    }

    // Bulk update without loading entities — EF 7+ ExecuteUpdateAsync
    public async Task CancelOldPendingOrdersAsync(DateTime cutoff)
    {
        await _db.Orders
                 .Where(o => o.Status == "Pending" && o.OrderDate < cutoff)
                 .ExecuteUpdateAsync(s =>
                     s.SetProperty(o => o.Status, "Cancelled"));
    }
}

Raw SQL

LINQ covers the vast majority of queries, but some operations — complex aggregations, full-text search, stored procedures, or database-specific features — are easier or only possible in raw SQL. EF Core supports raw SQL while still mapping results to entities, and FromSqlInterpolated handles parameterization automatically to prevent SQL injection.

// FromSqlRaw — use when the query returns the full entity shape
var customers = await _db.Customers
    .FromSqlRaw("SELECT * FROM Customers WHERE DATEDIFF(day, CreatedAt, GETUTCDATE()) <= {0}", 30)
    .AsNoTracking()
    .ToListAsync();

// FromSqlInterpolated — uses C# string interpolation but parameterizes safely
string name = "Alice";
var results = await _db.Customers
    .FromSqlInterpolated($"SELECT * FROM Customers WHERE Name LIKE {name + "%"}")
    .ToListAsync();

// SqlQuery<T> — for non-entity result shapes (aggregations, custom projections)
var stats = await _db.Database
    .SqlQuery<OrderStats>($"SELECT Status, COUNT(*) AS Count, SUM(Total) AS Total FROM Orders GROUP BY Status")
    .ToListAsync();

Transactions

By default, each SaveChangesAsync call wraps its changes in a database transaction. When you need multiple SaveChanges calls to succeed or fail together — for example, transferring funds between accounts — you manage the transaction explicitly. The await using pattern ensures the transaction is disposed (and rolled back if not committed) even if an exception is thrown.

public async Task TransferFundsAsync(int fromAccountId, int toAccountId, decimal amount)
{
    // Begin an explicit transaction — both SaveChanges calls join the same transaction
    await using var transaction = await _db.Database.BeginTransactionAsync();
    try
    {
        var from = await _db.Accounts.FindAsync(fromAccountId);
        var to   = await _db.Accounts.FindAsync(toAccountId);

        if (from!.Balance < amount)
            throw new InvalidOperationException("Insufficient funds");

        from.Balance -= amount;
        to!.Balance  += amount;

        await _db.SaveChangesAsync();   // writes both changes
        await transaction.CommitAsync(); // makes them permanent
    }
    catch
    {
        await transaction.RollbackAsync();  // undoes everything if anything fails
        throw;
    }
}