Entity Framework Core

// Install: dotnet add package Microsoft.EntityFrameworkCore.SqlServer
// dotnet add package Microsoft.EntityFrameworkCore.Tools

// Models (C# classes = database tables)
public class Product {
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public decimal Price { get; set; }
    public int CategoryId { get; set; }
    public Category Category { get; set; } = null!;  // Navigation property
    public ICollection<OrderItem> OrderItems { get; set; } = [];
}

public class Category {
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public ICollection<Product> Products { get; set; } = [];
}

// DbContext
public class AppDbContext : DbContext {
    public DbSet<Product> Products { get; set; }
    public DbSet<Category> Categories { get; set; }

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

    protected override void OnModelCreating(ModelBuilder modelBuilder) {
        // Fluent API configuration
        modelBuilder.Entity<Product>(entity => {
            entity.Property(p => p.Name).HasMaxLength(200).IsRequired();
            entity.Property(p => p.Price).HasPrecision(18, 2);
            entity.HasIndex(p => p.Name);
        });
    }
}

// Program.cs registration
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection"))
           .EnableSensitiveDataLogging()  // Dev only
           .EnableDetailedErrors());      // Dev only

// Migrations
// dotnet ef migrations add InitialCreate
// dotnet ef database update

// Repository usage
public class ProductService {
    private readonly AppDbContext _db;
    public ProductService(AppDbContext db) => _db = db;

    // LINQ queries
    public async Task<List<Product>> GetCheapProducts(decimal maxPrice) =>
        await _db.Products
            .Include(p => p.Category)          // Eager load (join)
            .Where(p => p.Price <= maxPrice)
            .OrderBy(p => p.Price)
            .Take(10)
            .AsNoTracking()                     // Read-only: faster
            .ToListAsync();

    // Raw SQL for complex queries
    public async Task<List<Product>> GetTopSellers() =>
        await _db.Products
            .FromSqlRaw("SELECT p.* FROM Products p JOIN ... ORDER BY sales DESC TAKE 10")
            .ToListAsync();
}