.NET: Entity Framework Core

EF Core is the official ORM for .NET. It supports PostgreSQL (Npgsql), SQL Server, SQLite, MySQL, and others. Uses LINQ for queries and Code First migrations.

DbContext & Models

// Models/User.cs
public class User
{
    public int Id { get; set; }
    public required string Name { get; set; }
    public required string Email { get; set; }
    public DateTime CreatedAt { get; set; } = DateTime.UtcNow;
    public List<Post> Posts { get; set; } = [];  // navigation property
}

public class Post
{
    public int Id { get; set; }
    public required string Title { get; set; }
    public required string Body { get; set; }
    public int UserId { get; set; }
    public User User { get; set; } = null!;  // navigation property
}

// Data/AppDbContext.cs
public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) {}

    public DbSet<User> Users => Set<User>();
    public DbSet<Post> Posts => Set<Post>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>(entity => {
            entity.HasIndex(u => u.Email).IsUnique();
            entity.Property(u => u.Name).HasMaxLength(100).IsRequired();
            entity.HasMany(u => u.Posts)
                  .WithOne(p => p.User)
                  .HasForeignKey(p => p.UserId)
                  .OnDelete(DeleteBehavior.Cascade);
        });
    }
}

Migrations

# Install EF CLI tools
dotnet tool install --global dotnet-ef

# Create migration
dotnet ef migrations add InitialCreate

# Apply migrations to database
dotnet ef database update

# Revert last migration
dotnet ef database update PreviousMigrationName

# Generate SQL script (for production)
dotnet ef migrations script --idempotent -o migration.sql

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

Querying

// Injected via DI
public class UserRepository
{
    private readonly AppDbContext _db;

    public UserRepository(AppDbContext db) { _db = db; }

    // Basic queries
    public async Task<List<User>> GetAllAsync() =>
        await _db.Users.ToListAsync();

    // Filter, order, project
    public async Task<List<UserDto>> GetAdminsAsync() =>
        await _db.Users
            .Where(u => u.Role == "admin")
            .OrderBy(u => u.Name)
            .Select(u => new UserDto(u.Id, u.Name, u.Email, u.CreatedAt))
            .ToListAsync();

    // Include related data (eager loading)
    public async Task<User?> GetWithPostsAsync(int id) =>
        await _db.Users
            .Include(u => u.Posts)
            .FirstOrDefaultAsync(u => u.Id == id);

    // Pagination
    public async Task<List<User>> GetPagedAsync(int page, int pageSize) =>
        await _db.Users
            .OrderBy(u => u.Id)
            .Skip((page - 1) * pageSize)
            .Take(pageSize)
            .ToListAsync();

    // Insert
    public async Task<User> CreateAsync(User user) {
        _db.Users.Add(user);
        await _db.SaveChangesAsync();
        return user;
    }

    // Update
    public async Task UpdateAsync(User user) {
        _db.Users.Update(user);
        await _db.SaveChangesAsync();
    }

    // Delete
    public async Task DeleteAsync(int id) {
        await _db.Users.Where(u => u.Id == id).ExecuteDeleteAsync();  // EF Core 7+
    }

    // Raw SQL (when LINQ isn't enough)
    public async Task<List<User>> SearchAsync(string term) =>
        await _db.Users
            .FromSqlRaw("SELECT * FROM users WHERE name ILIKE {0}", $"%{term}%")
            .ToListAsync();
}