On this page

Entity Framework Core 10: the complete .NET ORM

15 min read TextCh. 4 — ASP.NET Core

What is Entity Framework Core?

EF Core is .NET's official ORM (Object-Relational Mapper). It allows working with relational databases using C# objects instead of raw SQL.

Available providers:

  • SQL Server — Microsoft.EntityFrameworkCore.SqlServer
  • PostgreSQL — Npgsql.EntityFrameworkCore.PostgreSQL
  • SQLite — Microsoft.EntityFrameworkCore.Sqlite
  • MySQL — Pomelo.EntityFrameworkCore.MySql
  • In-Memory — for testing: Microsoft.EntityFrameworkCore.InMemory

Installing EF Core

# Packages for PostgreSQL
dotnet add package Microsoft.EntityFrameworkCore
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL
dotnet add package Microsoft.EntityFrameworkCore.Design

# Migrations CLI (install globally)
dotnet tool install --global dotnet-ef

# Verify
dotnet ef --version

Code First: entities → database

With Code First you define C# classes first and EF Core generates the database:

// Simple entity
public class Employee
{
    public int     Id         { get; set; }
    public string  Name       { get; set; } = string.Empty;
    public string  Email      { get; set; } = string.Empty;
    public decimal Salary     { get; set; }
    public DateTime JoinDate  { get; set; }

    // One-to-many relationship (FK)
    public int         DepartmentId { get; set; }
    public Department  Department   { get; set; } = null!;
}

public class Department
{
    public int    Id        { get; set; }
    public string Name      { get; set; } = string.Empty;

    // Navigation property (collection)
    public ICollection<Employee> Employees { get; set; } = new List<Employee>();
}

Configuring the DbContext

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

    public DbSet<Employee>   Employees   => Set<Employee>();
    public DbSet<Department> Departments => Set<Department>();

    protected override void OnModelCreating(ModelBuilder model)
    {
        // Apply all configurations from an assembly
        model.ApplyConfigurationsFromAssembly(typeof(CompanyDbContext).Assembly);
    }
}

// Separate configuration (best practice)
public class EmployeeConfiguration : IEntityTypeConfiguration<Employee>
{
    public void Configure(EntityTypeBuilder<Employee> builder)
    {
        builder.HasKey(e => e.Id);

        builder.Property(e => e.Name)
               .IsRequired()
               .HasMaxLength(150);

        builder.Property(e => e.Email)
               .IsRequired()
               .HasMaxLength(200);

        builder.HasIndex(e => e.Email).IsUnique();

        builder.Property(e => e.Salary)
               .HasColumnType("decimal(18,2)")
               .HasDefaultValue(0);

        builder.HasOne(e => e.Department)
               .WithMany(d => d.Employees)
               .HasForeignKey(e => e.DepartmentId)
               .OnDelete(DeleteBehavior.Cascade);
    }
}

Register in Program.cs

// PostgreSQL
builder.Services.AddDbContext<CompanyDbContext>(opts =>
    opts.UseNpgsql(builder.Configuration.GetConnectionString("Default")));

// appsettings.json
// "ConnectionStrings": {
//   "Default": "Host=localhost;Database=company;Username=app;Password=secret"
// }

Migrations

# Create initial migration
dotnet ef migrations add InitialCreate

# Preview the SQL that will be generated (without executing)
dotnet ef migrations script

# Apply migrations in development
dotnet ef database update

# Revert to a specific migration
dotnet ef database update MigrationName

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

Apply programmatically at app startup:

// In Program.cs — auto-migrate at startup
using (var scope = app.Services.CreateScope())
{
    var db = scope.ServiceProvider.GetRequiredService<CompanyDbContext>();
    await db.Database.MigrateAsync();
}

LINQ queries with EF Core

// Basic query
var employees = await db.Employees.ToListAsync();

// Filter
var highEarners = await db.Employees
    .Where(e => e.Salary > 3000)
    .ToListAsync();

// Include relationships (eager loading)
var withDept = await db.Employees
    .Include(e => e.Department)
    .ToListAsync();

// Nested relationships (ThenInclude)
var ordersWithDetails = await db.Orders
    .Include(o => o.Customer)
    .Include(o => o.Items)
        .ThenInclude(i => i.Product)
    .ToListAsync();

// Projection to DTO (avoids loading full objects)
var summary = await db.Employees
    .Select(e => new EmployeeDto(e.Id, e.Name, e.Department.Name))
    .ToListAsync();

// Pagination
int page = 1;
int size = 10;
var pageResult = await db.Employees
    .OrderBy(e => e.Name)
    .Skip((page - 1) * size)
    .Take(size)
    .AsNoTracking()
    .ToListAsync();

CRUD operations

// CREATE
var newEmployee = new Employee
{
    Name         = "David Morales",
    Email        = "[email protected]",
    Salary       = 4500m,
    DepartmentId = 1,
    JoinDate     = DateTime.Today
};
db.Employees.Add(newEmployee);
await db.SaveChangesAsync();
// newEmployee.Id now has the value generated by the DB

// READ
var employee  = await db.Employees.FindAsync(id);   // by PK — uses internal cache
var byEmail   = await db.Employees
    .FirstOrDefaultAsync(e => e.Email == "[email protected]");

// UPDATE
var emp = await db.Employees.FindAsync(id);
if (emp is not null)
{
    emp.Salary = 5000m;
    await db.SaveChangesAsync(); // EF detects the change automatically
}

// Bulk UPDATE (EF Core 7+)
await db.Employees
    .Where(e => e.DepartmentId == 2)
    .ExecuteUpdateAsync(s =>
        s.SetProperty(e => e.Salary, e => e.Salary * 1.10m)); // +10%

// DELETE
db.Employees.Remove(emp);
await db.SaveChangesAsync();

// Bulk DELETE (EF Core 7+)
await db.Employees
    .Where(e => !e.Active)
    .ExecuteDeleteAsync();

Transactions

// Explicit transaction
await using var transaction = await db.Database.BeginTransactionAsync();
try
{
    var dept = new Department { Name = "DevOps" };
    db.Departments.Add(dept);
    await db.SaveChangesAsync();

    var emp = new Employee { Name = "Ana Lopez", DepartmentId = dept.Id };
    db.Employees.Add(emp);
    await db.SaveChangesAsync();

    await transaction.CommitAsync();
}
catch
{
    await transaction.RollbackAsync();
    throw;
}

Practice

  1. Basic store: Create Category and Product with a 1-N relationship. Configure DbContext with SQLite (UseInMemoryDatabase("test")), create seed data, and write LINQ queries to list products by category.
  2. Migrations: Create a project with PostgreSQL, model Task and User, and generate the InitialCreate migration with dotnet ef migrations add.
  3. Async CRUD: Implement a ProductRepository with ListAsync, CreateAsync, UpdateAsync, and DeleteAsync using EF Core.

In the next lesson we will learn how to protect the API with JWT authentication and role-based and policy-based authorization.

Use AsNoTracking for read-only queries
EF Core tracks queried objects to detect changes for SaveChangesAsync. If you are only reading data (to display in an API, for example), call .AsNoTracking() on the query. It reduces memory usage and can improve performance by up to 30% on large queries.
ExecuteUpdateAsync and ExecuteDeleteAsync
EF Core 7+ introduced ExecuteUpdateAsync and ExecuteDeleteAsync for bulk operations without loading objects into memory. They can be up to 10x faster than the load → modify → SaveChanges approach for updating or deleting many records.
Never run dotnet ef migrations in production
Migrations (dotnet ef database update) should never be applied manually in production. Use the programmatic method context.Database.MigrateAsync() at app startup, or a dedicated CI/CD pipeline. Automating migrations prevents manual errors in critical environments. 
using Microsoft.EntityFrameworkCore;

// ── Domain entities ───────────────────────────────────
public class Category
{
    public int    Id       { get; set; }
    public string Name     { get; set; } = string.Empty;
    public ICollection<Product> Products { get; set; } = new List<Product>();
}

public class Product
{
    public int      Id          { get; set; }
    public string   Name        { get; set; } = string.Empty;
    public decimal  Price       { get; set; }
    public int      Stock       { get; set; }
    public bool     Active      { get; set; } = true;
    public DateTime CreatedAt   { get; set; } = DateTime.UtcNow;

    // Foreign key + navigation property
    public int        CategoryId  { get; set; }
    public Category   Category    { get; set; } = null!;
}

// ── DbContext ─────────────────────────────────────────
public class StoreDbContext : DbContext
{
    public StoreDbContext(DbContextOptions<StoreDbContext> options)
        : base(options) { }

    public DbSet<Product>  Products   => Set<Product>();
    public DbSet<Category> Categories => Set<Category>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Fluent API — mapping configuration
        modelBuilder.Entity<Product>(e =>
        {
            e.HasKey(p => p.Id);
            e.Property(p => p.Name).IsRequired().HasMaxLength(200);
            e.Property(p => p.Price).HasColumnType("decimal(18,2)");
            e.HasOne(p => p.Category)
             .WithMany(c => c.Products)
             .HasForeignKey(p => p.CategoryId)
             .OnDelete(DeleteBehavior.Restrict);
        });

        // Seed data
        modelBuilder.Entity<Category>().HasData(
            new Category { Id = 1, Name = "Electronics" },
            new Category { Id = 2, Name = "Peripherals" }
        );
    }
}