Entity Framework Core, DbContext, Migrations & LINQ Masterclass

# Install EF Core for SQL Server
dotnet add package Microsoft.EntityFrameworkCore.SqlServer

# Install EF Core for SQLite (lightweight, good for dev)
dotnet add package Microsoft.EntityFrameworkCore.Sqlite

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

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

using Microsoft.EntityFrameworkCore;
using System.ComponentModel.DataAnnotations;

// 1. Entity Class (maps to database table)
public class Product
{
    public int Id { get; set; }                             // PK (auto-detected by convention)

    [Required, MaxLength(100)]
    public string Name { get; set; } = "";

    [Required, Range(0.01, double.MaxValue)]
    public decimal Price { get; set; }

    public int Stock { get; set; }
    public string Category { get; set; } = "";
    public DateTime CreatedAt { get; set; } = DateTime.UtcNow;

    // Navigation property (one Product has many OrderItems)
    public ICollection<OrderItem> OrderItems { get; set; } = new List<OrderItem>();
}

// 2. DbContext — the central connection hub to the database
public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }

    public DbSet<Product> Products { get; set; }
    public DbSet<Order> Orders { get; set; }
    public DbSet<OrderItem> OrderItems { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Fluent API configuration (alternative to Data Annotations)
        modelBuilder.Entity<Product>()
            .Property(p => p.Price)
            .HasColumnType("decimal(10,2)");

        modelBuilder.Entity<Product>()
            .HasIndex(p => p.Category); // Add database index on Category
    }
}

# Step 1: Create first migration (generates C# migration class)
dotnet ef migrations add InitialCreate

# Step 2: Apply migration to database (executes SQL ALTER TABLE scripts)
dotnet ef database update

# After adding new entity property (e.g., adding ImageUrl to Product):
dotnet ef migrations add AddProductImageUrl
dotnet ef database update

# Rollback to previous migration
dotnet ef database update PreviousMigrationName

# Remove last unapplied migration
dotnet ef migrations remove

// READ — Get all products (async LINQ query)
List<Product> products = await context.Products
    .Where(p => p.Stock > 0)
    .OrderBy(p => p.Name)
    .ToListAsync();

// READ — Find by primary key (most efficient)
Product? product = await context.Products.FindAsync(1);

// ADD — Create new entity
var newProduct = new Product { Name = "Tablet", Price = 35000m, Stock = 20, Category = "Electronics" };
context.Products.Add(newProduct);
await context.SaveChangesAsync(); // Executes INSERT SQL

// UPDATE — Modify existing entity
var existing = await context.Products.FindAsync(1);
if (existing != null)
{
    existing.Price = 72000m;
    existing.Stock -= 1;
    await context.SaveChangesAsync(); // Executes UPDATE SQL
}

// DELETE — Remove entity
var toDelete = await context.Products.FindAsync(5);
if (toDelete != null)
{
    context.Products.Remove(toDelete);
    await context.SaveChangesAsync(); // Executes DELETE SQL
}

// Eager Loading — Load related entities in a single SQL query
var orders = await context.Orders
    .Include(o => o.Customer)          // JOIN Customers table
    .Include(o => o.Items)             // JOIN OrderItems table
        .ThenInclude(i => i.Product)   // JOIN Products table
    .ToListAsync();

// Generic Repository Pattern
public interface IRepository<T> where T : class
{
    Task<List<T>> GetAllAsync();
    Task<T?> GetByIdAsync(int id);
    Task AddAsync(T entity);
    Task UpdateAsync(T entity);
    Task DeleteAsync(int id);
}

public class ProductRepository : IRepository<Product>
{
    private readonly AppDbContext _context;
    public ProductRepository(AppDbContext context) { _context = context; }

    public async Task<List<Product>> GetAllAsync() => await _context.Products.ToListAsync();
    public async Task<Product?> GetByIdAsync(int id) => await _context.Products.FindAsync(id);
    public async Task AddAsync(Product entity) { _context.Products.Add(entity); await _context.SaveChangesAsync(); }
    public async Task UpdateAsync(Product entity) { _context.Products.Update(entity); await _context.SaveChangesAsync(); }
    public async Task DeleteAsync(int id) { var p = await _context.Products.FindAsync(id); if (p != null) { _context.Products.Remove(p); await _context.SaveChangesAsync(); } }
}