A Simple Analogy

EF Core is like a translator between C# and SQL. You write C# queries that become SQL automatically.

---

Why EF Core + PostgreSQL?

• Type-safe: Compile-time query checking
• LINQ: Natural query syntax
• Relationships: Handle foreign keys automatically
• Migrations: Version control for schema
• Performance: Lazy loading and optimization

---

Setup & Configuration

public class AppDbContext : DbContext
{
    public DbSet<Product> Products { get; set; }
    public DbSet<Category> Categories { get; set; }
    
    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
        options.UseNpgsql("Host=localhost;Database=myapp;Username=user;Password=pass");
    }
}

// Register in DI
services.AddDbContext<AppDbContext>(options =>
    options.UseNpgsql(configuration.GetConnectionString("Default")));

---

Entity Configuration

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
    public int CategoryId { get; set; }
    public Category Category { get; set; }
}

public class Category
{
    public int Id { get; set; }
    public string Name { get; set; }
    public List<Product> Products { get; set; }
}

// Fluent API configuration
public class ProductEntityConfiguration : IEntityTypeConfiguration<Product>
{
    public void Configure(EntityTypeBuilder<Product> builder)
    {
        builder.HasKey(p => p.Id);
        builder.Property(p => p.Name).HasMaxLength(200).IsRequired();
        builder.Property(p => p.Price).HasPrecision(10, 2);
        builder.HasIndex(p => p.Name).IsUnique();
    }
}

---

LINQ Queries

// Select
var products = await _context.Products.ToListAsync();

// Filter
var expensive = await _context.Products
    .Where(p => p.Price > 100)
    .ToListAsync();

// Join
var withCategory = await _context.Products
    .Include(p => p.Category)
    .ToListAsync();

// Pagination
var page = await _context.Products
    .OrderBy(p => p.Name)
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize)
    .ToListAsync();

// Aggregate
var count = await _context.Products.CountAsync();
var total = await _context.Products.SumAsync(p => p.Price);

---

Migrations

# Create migration
dotnet ef migrations add AddProductTable

# Update database
dotnet ef database update

# Rollback
dotnet ef database update PreviousMigration

# Generate SQL script
dotnet ef migrations script

---

Best Practices

1. Async: Always use async methods
2. Projection: Select only needed fields
3. Eager loading: Use Include for relationships
4. Indexes: Add for frequently queried columns
5. Transactions: Group related operations

---

Related Concepts

• Dapper
• Prisma
• Sequelize
• Raw SQL

---

Summary

EF Core with PostgreSQL provides type-safe, LINQ-based data access with automatic schema migration and relationship management.