EF Core

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;

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

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

    protected override void OnModelCreating(ModelBuilder b)
    {
        b.Entity<Customer>(e =>
        {
            e.HasIndex(c => c.Email).IsUnique();
            e.Property(c => c.Email).HasMaxLength(190).IsRequired();
            e.Property(c => c.Name).HasMaxLength(120).IsRequired();
        });

        b.Entity<Order>(e =>
        {
            e.HasOne(o => o.Customer).WithMany(c => c.Orders)
              .HasForeignKey(o => o.CustomerId).OnDelete(DeleteBehavior.Restrict);
            e.Property(o => o.Status).HasConversion<string>().HasMaxLength(16);
            e.HasIndex(o => new { o.CustomerId, o.Status, o.CreatedAt });
        });
    }
}

public class Customer
{
    public long Id { get; set; }
    public string Email { get; set; } = string.Empty;
    public string Name  { get; set; } = string.Empty;
    public DateTime CreatedAt { get; set; } = DateTime.UtcNow;
    public List<Order> Orders { get; set; } = new();
}

public class Order
{
    public long Id { get; set; }
    public long CustomerId { get; set; }
    public Customer? Customer { get; set; }
    public long TotalCents { get; set; }
    public OrderStatus Status { get; set; } = OrderStatus.New;
    public DateTime CreatedAt { get; set; } = DateTime.UtcNow;
    public DateTime? PaidAt { get; set; }
}

public enum OrderStatus { New, Paid, Shipped, Cancelled }

// ============================================================
// DI registration in Program.cs (minimal API)
// ============================================================
var builder = Host.CreateApplicationBuilder(args);

builder.Services.AddDbContext<ShopDb>(opt =>
    opt.UseNpgsql(builder.Configuration.GetConnectionString("Default"))
       .EnableSensitiveDataLogging(builder.Environment.IsDevelopment())
       .EnableDetailedErrors(builder.Environment.IsDevelopment()));

var app = builder.Build();

// ============================================================
// Migrations (run on the CLI, NOT at startup in prod)
// ============================================================
// dotnet ef migrations add Initial
// dotnet ef database update
// dotnet ef migrations script             # generate SQL for review
// dotnet ef migrations remove             # drop the last unmapped migration

// In CI, apply with:
//   dotnet ef database update --connection $CONN --no-build

// At app start, optionally call db.Database.Migrate() — only in dev or if
// you accept the deploy-coupled-to-app-boot tradeoff.

// ============================================================
// Querying
// ============================================================
public static class OrderQueries
{
    // a) Read by id — no tracking for read-only paths
    public static Task<Order?> ByIdAsync(ShopDb db, long id, CancellationToken ct)
        => db.Orders.AsNoTracking().FirstOrDefaultAsync(o => o.Id == id, ct);

    // b) Paginated list with projection (only fetch fields you need)
    public static Task<List<OrderListItem>> RecentAsync(ShopDb db, int limit, CancellationToken ct)
        => db.Orders.AsNoTracking()
            .OrderByDescending(o => o.CreatedAt)
            .Take(limit)
            .Select(o => new OrderListItem(o.Id, o.CustomerId, o.TotalCents, o.Status.ToString()))
            .ToListAsync(ct);

    // c) Include for related data — avoid N+1
    public static Task<List<Customer>> WithOrdersAsync(ShopDb db, CancellationToken ct)
        => db.Customers.AsNoTracking()
            .Include(c => c.Orders)
            .ToListAsync(ct);

    // d) Filtering + grouping
    public static Task<List<RevenueByCustomer>> RevenueAsync(ShopDb db, CancellationToken ct)
        => db.Orders.AsNoTracking()
            .Where(o => o.Status == OrderStatus.Paid || o.Status == OrderStatus.Shipped)
            .GroupBy(o => o.CustomerId)
            .Select(g => new RevenueByCustomer(g.Key, g.Sum(o => o.TotalCents)))
            .ToListAsync(ct);
}

public record OrderListItem(long Id, long CustomerId, long TotalCents, string Status);
public record RevenueByCustomer(long CustomerId, long TotalCents);

// ============================================================
// Writes — change tracking + SaveChanges
// ============================================================
public static class PlaceOrder
{
    public static async Task<long> ExecuteAsync(ShopDb db, long customerId, long totalCents, CancellationToken ct)
    {
        var customer = await db.Customers.FindAsync(new object[] { customerId }, ct)
                       ?? throw new InvalidOperationException("missing");

        var order = new Order { CustomerId = customer.Id, TotalCents = totalCents };
        db.Orders.Add(order);
        await db.SaveChangesAsync(ct);
        return order.Id;
    }
}

// ============================================================
// Raw SQL when needed (parameterised)
// ============================================================
// var orders = await db.Orders
//     .FromSqlInterpolated($"SELECT * FROM orders WHERE created_at >= {since}")
//     .AsNoTracking()
//     .ToListAsync(ct);

// ============================================================
// Pitfalls
// ============================================================
// - Singleton DbContext: NEVER. It is request-scoped.
// - Sync calls (.ToList()) on async endpoints: blocks the thread pool.
// - Missing AsNoTracking() on read-only queries: needless change tracking.
// - SELECT * via .Include() pulling huge graphs: project to DTOs instead.
// - Migrations applied at startup in production: race with multiple instances.
// - Forgetting to pass CancellationToken: requests do not honour cancellation.

public class AppDb : DbContext {
    public DbSet<User> Users => Set<User>();
}
var adults = db.Users.Where(u => u.Age >= 18).ToList();