1. Installation and Setup

First, install the required NuGet packages for PostgreSQL support in Entity Framework Core:

dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL
dotnet add package Microsoft.EntityFrameworkCore.Design

2. DbContext Configuration

using Microsoft.EntityFrameworkCore;

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

   public DbSet<User> Users { get; set; }
   public DbSet<Post> Posts { get; set; }

   protected override void OnModelCreating(ModelBuilder modelBuilder) {
     // Configure PostgreSQL-specific mappings
    modelBuilder.Entity<User>(entity => {
       entity.HasKey(e => e.Id);
       entity.Property(e => e.Id).ValueGeneratedOnAdd();
       entity.Property(e => e.Username).HasMaxLength(50);
       entity.Property(e => e.Email).HasMaxLength(100);
       entity.HasIndex(e => e.Email).IsUnique();
     });

     modelBuilder.Entity<Post>(entity => {
       entity.HasKey(e => e.Id);
       entity.Property(e => e.CreatedAt).HasDefaultValueSql("NOW()");
     });
  }
}

Example: Repository Pattern Implementation

A repository layer helps abstract the database operations from the service logic:

public interface IUserRepository {
   Task<User> GetByIdAsync(int id);
   Task<List<User>> GetAllAsync();
   Task AddAsync(User user);
   Task UpdateAsync(User user);
   Task DeleteAsync(int id);
}

public class UserRepository : IUserRepository {
   private readonly ApplicationDbContext _context;

   public UserRepository(ApplicationDbContext context) {
     _context = context;
   }

   public async Task<User> GetByIdAsync(int id) {
     return await _context.Users.Include(u => u.Posts).FirstOrDefaultAsync(u => u.Id == id);
   }
}