dotnet add package Microsoft.EntityFrameworkCore.SqlServer  # or .Npgsql for PostgreSQL
dotnet add package Microsoft.EntityFrameworkCore.Tools       # migrations CLI

public class Blog
{
    public int Id { get; set; }
    public string Title { get; set; } = string.Empty;
    public string? Url { get; set; }
    public DateTime CreatedAt { get; set; } = DateTime.UtcNow;

    // Navigation property — one blog has many posts
    public List<Post> Posts { get; set; } = [];
}

public class Post
{
    public int Id { get; set; }
    public string Title { get; set; } = string.Empty;
    public string Body { get; set; } = string.Empty;
    public bool Published { get; set; }

    // Foreign key + navigation to parent
    public int BlogId { get; set; }
    public Blog Blog { get; set; } = null!;
}

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

    public DbSet<Blog> Blogs => Set<Blog>();
    public DbSet<Post> Posts => Set<Post>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Optional configuration — EF Core has sensible conventions
        modelBuilder.Entity<Post>()
            .Property(p => p.Title)
            .HasMaxLength(200)
            .IsRequired();
    }
}

// For PostgreSQL:
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseNpgsql(builder.Configuration.GetConnectionString("Default")));

// For SQL Server:
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("Default")));

// appsettings.json
{
  "ConnectionStrings": {
    "Default": "Host=localhost;Database=myapp;Username=postgres;Password=password"
  }
}

# Create migration (generates SQL from your entities)
dotnet ef migrations add InitialCreate

# Apply to database (creates tables)
dotnet ef database update

# After changing entities — create a new migration
dotnet ef migrations add AddPublishedDate
dotnet ef database update

# Remove last migration (if not yet applied)
dotnet ef migrations remove

# View generated SQL without applying
dotnet ef migrations script

public class BlogService(AppDbContext db)
{
    // Create
    public async Task<Blog> CreateAsync(string title, string url)
    {
        var blog = new Blog { Title = title, Url = url };
        db.Blogs.Add(blog);
        await db.SaveChangesAsync();
        return blog;
    }

    // Read
    public async Task<Blog?> GetByIdAsync(int id)
        => await db.Blogs
            .Include(b => b.Posts)
            .FirstOrDefaultAsync(b => b.Id == id);

    public async Task<List<Blog>> GetAllAsync()
        => await db.Blogs.OrderBy(b => b.Title).ToListAsync();

    // Update
    public async Task UpdateAsync(int id, string newTitle)
    {
        var blog = await db.Blogs.FindAsync(id);
        if (blog is null) return;
        blog.Title = newTitle;
        await db.SaveChangesAsync();
    }

    // Delete
    public async Task DeleteAsync(int id)
    {
        var blog = await db.Blogs.FindAsync(id);
        if (blog is null) return;
        db.Blogs.Remove(blog);
        await db.SaveChangesAsync();
    }
}

// Filter
var published = await db.Posts
    .Where(p => p.Published)
    .ToListAsync();

// Include related data
var blogsWithPosts = await db.Blogs
    .Include(b => b.Posts)
    .ToListAsync();

// Pagination
var page = await db.Posts
    .OrderByDescending(p => p.Id)
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize)
    .ToListAsync();

// Projection — only load what you need
var titles = await db.Posts
    .Where(p => p.Published)
    .Select(p => new { p.Id, p.Title })
    .ToListAsync();