Real persistence, a real relationship, and async all the way down — this is the project where EF Core, LINQ, and async/await finally meet in one system.

The Task API from the last project kept its data in a List<T> that vanished the moment the process stopped. That's fine for learning DI and minimal APIs, but no real backend works that way. This project fixes that: you'll build an Employee Management API backed by a real database through EF Core, with an actual relationship between two entities — an Employee belongs to a Department, and a Department has many Employees — queried with LINQ and touched exclusively through async/await.

This is deliberately more substantial than the last project. You already know EF Core's building blocks — DbContext, DbSet<T>, migrations, LINQ queries — and you already know async/await and cancellation tokens from the Async Programming part. What you haven't done yet is put all of it behind a real REST API, with a genuine one-to-many relationship, navigation properties, and async queries running end to end from an HTTP request down to SQLite and back. That's the payoff of this project.

Project Brief

Build a REST API for managing employees and the departments they belong to, backed by a real (if lightweight) SQL database.

Requirements

• Departments have a name; employees have a name, a salary, a hire date, and belong to exactly one department.
• GET /employees — list all employees, including their department's name.
• GET /employees/{id} — a single employee, 404 if missing.
• POST /employees — hire a new employee into an existing department.
• PUT /employees/{id} — update salary and/or department.
• DELETE /employees/{id} — remove an employee.
• GET /departments/{id}/employees — every employee in one department.
• GET /departments/summary — each department's name, headcount, and average salary, computed with LINQ grouping.
• Data survives an app restart — a real, file-based database, not an in-memory list.
• Every data-access method is async and accepts a CancellationToken.

Designing the Data Model

Two entities, one relationship. A Department has many Employees; an Employee belongs to exactly one Department — a classic one-to-many, modeled with a foreign key and navigation properties on both sides:

public class Department
{
    public int Id { get; set; }
    public required string Name { get; set; }

    // Navigation property — the "many" side
    public List<Employee> Employees { get; set; } = [];
}

public class Employee
{
    public int Id { get; set; }
    public required string Name { get; set; }
    public decimal Salary { get; set; }
    public DateOnly HireDate { get; set; }

    // Foreign key + navigation property — the "one" side
    public int DepartmentId { get; set; }
    public Department? Department { get; set; }
}

DepartmentId is the actual foreign key column that lands in the database; Department is a navigation property that lets you write employee.Department.Name in C# instead of manually joining tables. EF Core's convention-based mapping infers this whole relationship automatically just from the shape of these two classes — Employee.DepartmentId matching Department.Id's type and name pattern is enough for it to wire up a real foreign key constraint.

Building It Step by Step

Step 1 — Project setup and packages

dotnet new web -n EmployeeApi
cd EmployeeApi
dotnet add package Microsoft.EntityFrameworkCore.Sqlite
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet tool install --global dotnet-ef   # once per machine, if not already installed

SQLite is the pragmatic choice here: a real, file-based relational database with proper SQL, foreign keys, and transactions — but zero installation or server process, so the project runs the same way on any machine, exactly like the previous project's in-memory storage did, just with actual persistence this time.

Step 2 — The DbContext

using Microsoft.EntityFrameworkCore;

public class AppDbContext(DbContextOptions<AppDbContext> options) : DbContext(options)
{
    public DbSet<Department> Departments => Set<Department>();
    public DbSet<Employee> Employees => Set<Employee>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Employee>()
            .HasOne(e => e.Department)
            .WithMany(d => d.Employees)
            .HasForeignKey(e => e.DepartmentId)
            .OnDelete(DeleteBehavior.Restrict);

        modelBuilder.Entity<Employee>()
            .Property(e => e.Salary)
            .HasPrecision(10, 2);
    }
}

The DbContextOptions<AppDbContext> primary-constructor parameter is how the DI container hands the context its connection string and provider — you configure that once in Program.cs, not here. OnModelCreating is where you make the relationship and its behavior explicit rather than relying purely on convention: .HasOne().WithMany() spells out the one-to-many relationship in fluent-API form, and DeleteBehavior.Restrict means EF Core will refuse to delete a department that still has employees, rather than silently cascading the deletion — a deliberate business rule, not an accident.

Step 3 — Register the context and connect it to SQLite

// Program.cs
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlite(builder.Configuration.GetConnectionString("Default")
        ?? "Data Source=employees.db"));

// appsettings.json
{
  "ConnectionStrings": {
    "Default": "Data Source=employees.db"
  }
}

AddDbContext registers AppDbContext as a scoped service — a fresh instance (and, underneath, a connection) per HTTP request, which is exactly right: EF Core's DbContext tracks changes for the duration of a single unit of work, and sharing one across concurrent requests would cause exactly the kind of state confusion that led the previous project's in-memory repository to need a manual lock. With EF Core, the connection pooling and per-request scoping do that safety work for you.

Step 4 — Create and apply a migration

dotnet ef migrations add InitialCreate
dotnet ef database update

migrations add inspects your DbContext and entity classes and generates C# code describing the schema — the tables, columns, foreign key, precision setting — as a versioned, reviewable file under a new Migrations/ folder. database update actually runs that migration against employees.db, creating the real SQLite file with real tables. From here on, any change to Employee or Department gets its own new migration — the database's schema evolves in lockstep with your code, with a full history you can read, diff, and even roll back.

Step 5 — Seed some starting data

// after builder.Build(), before app.Run() — a small startup block
using (var scope = app.Services.CreateScope())
{
    var db = scope.ServiceProvider.GetRequiredService<AppDbContext>();
    await db.Database.MigrateAsync();

    if (!await db.Departments.AnyAsync())
    {
        var engineering = new Department { Name = "Engineering" };
        var sales = new Department { Name = "Sales" };
        db.Departments.AddRange(engineering, sales);

        db.Employees.AddRange(
            new Employee { Name = "Ada Lovelace", Salary = 95000m, HireDate = new DateOnly(2022, 3, 1), Department = engineering },
            new Employee { Name = "Grace Hopper", Salary = 98000m, HireDate = new DateOnly(2021, 11, 15), Department = engineering },
            new Employee { Name = "Sam Reyes", Salary = 72000m, HireDate = new DateOnly(2023, 6, 20), Department = sales });

        await db.SaveChangesAsync();
    }
}

app.Services.CreateScope() creates a manual scope so this startup code — which runs once, outside any HTTP request — can resolve the scoped AppDbContext just like a request would. Assigning Department = engineering directly on the navigation property, rather than setting a raw DepartmentId, lets EF Core's change tracker figure out the foreign key value automatically once engineering gets its real, database-generated Id during SaveChangesAsync.

Step 6 — Async LINQ queries against the database

app.MapGet("/employees", async (AppDbContext db, CancellationToken ct) =>
    await db.Employees
        .Include(e => e.Department)
        .Select(e => new EmployeeResponse(e.Id, e.Name, e.Salary, e.HireDate, e.Department!.Name))
        .ToListAsync(ct));

app.MapGet("/employees/{id:int}", async (int id, AppDbContext db, CancellationToken ct) =>
{
    var employee = await db.Employees
        .Include(e => e.Department)
        .FirstOrDefaultAsync(e => e.Id == id, ct);

    return employee is null
        ? Results.NotFound()
        : Results.Ok(new EmployeeResponse(employee.Id, employee.Name, employee.Salary,
            employee.HireDate, employee.Department!.Name));
});

app.MapGet("/departments/{id:int}/employees", async (int id, AppDbContext db, CancellationToken ct) =>
    await db.Employees
        .Where(e => e.DepartmentId == id)
        .Select(e => new EmployeeResponse(e.Id, e.Name, e.Salary, e.HireDate, e.Department!.Name))
        .ToListAsync(ct));

app.MapGet("/departments/summary", async (AppDbContext db, CancellationToken ct) =>
    await db.Employees
        .GroupBy(e => e.Department!.Name)
        .Select(g => new DepartmentSummary(g.Key, g.Count(), g.Average(e => e.Salary)))
        .ToListAsync(ct));

record EmployeeResponse(int Id, string Name, decimal Salary, DateOnly HireDate, string Department);
record DepartmentSummary(string Department, int Headcount, decimal AverageSalary);

Three ideas from earlier in the tier converge here. First, .Include(e => e.Department) — the async-data-access lesson's reminder that navigation properties are not loaded automatically; without Include, employee.Department would be null, because EF Core only fetches what you explicitly ask for. Second, every query ends in ToListAsync or FirstOrDefaultAsync, threading the endpoint's own CancellationToken straight through — if the client disconnects mid-request, the database query itself gets cancelled rather than running to completion for no one. Third, GroupBy and .Average(...) in the summary query are the same LINQ operators from the LINQ part of this tier, except here EF Core translates the entire query into a single SQL GROUP BY statement — the grouping and averaging happen in the database, not by pulling every row into memory first.

Step 7 — Writing data: create, update, delete

app.MapPost("/employees", async (CreateEmployeeRequest request, AppDbContext db, CancellationToken ct) =>
{
    bool departmentExists = await db.Departments.AnyAsync(d => d.Id == request.DepartmentId, ct);
    if (!departmentExists)
        return Results.BadRequest($"Department {request.DepartmentId} does not exist.");

    var employee = new Employee
    {
        Name = request.Name,
        Salary = request.Salary,
        HireDate = DateOnly.FromDateTime(DateTime.UtcNow),
        DepartmentId = request.DepartmentId
    };

    db.Employees.Add(employee);
    await db.SaveChangesAsync(ct);
    return Results.Created($"/employees/{employee.Id}", employee);
});

app.MapPut("/employees/{id:int}", async (int id, UpdateEmployeeRequest request, AppDbContext db, CancellationToken ct) =>
{
    var employee = await db.Employees.FindAsync([id], ct);
    if (employee is null) return Results.NotFound();

    employee.Salary = request.Salary;
    employee.DepartmentId = request.DepartmentId;
    await db.SaveChangesAsync(ct);
    return Results.NoContent();
});

app.MapDelete("/employees/{id:int}", async (int id, AppDbContext db, CancellationToken ct) =>
{
    var employee = await db.Employees.FindAsync([id], ct);
    if (employee is null) return Results.NotFound();

    db.Employees.Remove(employee);
    await db.SaveChangesAsync(ct);
    return Results.NoContent();
});

record CreateEmployeeRequest(string Name, decimal Salary, int DepartmentId);
record UpdateEmployeeRequest(decimal Salary, int DepartmentId);

Notice there's no manual SQL anywhere, and no manual locking either. db.Employees.Add(employee) only stages the change — nothing touches the database until await db.SaveChangesAsync(ct) actually commits it, wrapped in EF Core's own transaction. FindAsync is a small but useful specialization of a lookup-by-primary-key: it checks the context's in-memory change tracker before ever hitting the database, so updating and then immediately deleting the same employee within one request doesn't issue two redundant round trips.

Complete Solution

The full Program.cs, with the entity and DbContext classes shown alongside it (in a real project each would be its own file):

using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlite(builder.Configuration.GetConnectionString("Default")
        ?? "Data Source=employees.db"));

var app = builder.Build();

using (var scope = app.Services.CreateScope())
{
    var db = scope.ServiceProvider.GetRequiredService<AppDbContext>();
    await db.Database.MigrateAsync();

    if (!await db.Departments.AnyAsync())
    {
        var engineering = new Department { Name = "Engineering" };
        var sales = new Department { Name = "Sales" };
        db.Departments.AddRange(engineering, sales);
        db.Employees.AddRange(
            new Employee { Name = "Ada Lovelace", Salary = 95000m, HireDate = new DateOnly(2022, 3, 1), Department = engineering },
            new Employee { Name = "Grace Hopper", Salary = 98000m, HireDate = new DateOnly(2021, 11, 15), Department = engineering },
            new Employee { Name = "Sam Reyes", Salary = 72000m, HireDate = new DateOnly(2023, 6, 20), Department = sales });
        await db.SaveChangesAsync();
    }
}

app.MapGet("/employees", async (AppDbContext db, CancellationToken ct) =>
    await db.Employees.Include(e => e.Department)
        .Select(e => new EmployeeResponse(e.Id, e.Name, e.Salary, e.HireDate, e.Department!.Name))
        .ToListAsync(ct));

app.MapGet("/employees/{id:int}", async (int id, AppDbContext db, CancellationToken ct) =>
{
    var employee = await db.Employees.Include(e => e.Department).FirstOrDefaultAsync(e => e.Id == id, ct);
    return employee is null
        ? Results.NotFound()
        : Results.Ok(new EmployeeResponse(employee.Id, employee.Name, employee.Salary, employee.HireDate, employee.Department!.Name));
});

app.MapGet("/departments/{id:int}/employees", async (int id, AppDbContext db, CancellationToken ct) =>
    await db.Employees.Where(e => e.DepartmentId == id)
        .Select(e => new EmployeeResponse(e.Id, e.Name, e.Salary, e.HireDate, e.Department!.Name))
        .ToListAsync(ct));

app.MapGet("/departments/summary", async (AppDbContext db, CancellationToken ct) =>
    await db.Employees.GroupBy(e => e.Department!.Name)
        .Select(g => new DepartmentSummary(g.Key, g.Count(), g.Average(e => e.Salary)))
        .ToListAsync(ct));

app.MapPost("/employees", async (CreateEmployeeRequest request, AppDbContext db, CancellationToken ct) =>
{
    if (!await db.Departments.AnyAsync(d => d.Id == request.DepartmentId, ct))
        return Results.BadRequest($"Department {request.DepartmentId} does not exist.");

    var employee = new Employee
    {
        Name = request.Name,
        Salary = request.Salary,
        HireDate = DateOnly.FromDateTime(DateTime.UtcNow),
        DepartmentId = request.DepartmentId
    };
    db.Employees.Add(employee);
    await db.SaveChangesAsync(ct);
    return Results.Created($"/employees/{employee.Id}", employee);
});

app.MapPut("/employees/{id:int}", async (int id, UpdateEmployeeRequest request, AppDbContext db, CancellationToken ct) =>
{
    var employee = await db.Employees.FindAsync([id], ct);
    if (employee is null) return Results.NotFound();
    employee.Salary = request.Salary;
    employee.DepartmentId = request.DepartmentId;
    await db.SaveChangesAsync(ct);
    return Results.NoContent();
});

app.MapDelete("/employees/{id:int}", async (int id, AppDbContext db, CancellationToken ct) =>
{
    var employee = await db.Employees.FindAsync([id], ct);
    if (employee is null) return Results.NotFound();
    db.Employees.Remove(employee);
    await db.SaveChangesAsync(ct);
    return Results.NoContent();
});

app.Run();

record EmployeeResponse(int Id, string Name, decimal Salary, DateOnly HireDate, string Department);
record DepartmentSummary(string Department, int Headcount, decimal AverageSalary);
record CreateEmployeeRequest(string Name, decimal Salary, int DepartmentId);
record UpdateEmployeeRequest(decimal Salary, int DepartmentId);

public class Department
{
    public int Id { get; set; }
    public required string Name { get; set; }
    public List<Employee> Employees { get; set; } = [];
}

public class Employee
{
    public int Id { get; set; }
    public required string Name { get; set; }
    public decimal Salary { get; set; }
    public DateOnly HireDate { get; set; }
    public int DepartmentId { get; set; }
    public Department? Department { get; set; }
}

public class AppDbContext(DbContextOptions<AppDbContext> options) : DbContext(options)
{
    public DbSet<Department> Departments => Set<Department>();
    public DbSet<Employee> Employees => Set<Employee>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Employee>()
            .HasOne(e => e.Department)
            .WithMany(d => d.Employees)
            .HasForeignKey(e => e.DepartmentId)
            .OnDelete(DeleteBehavior.Restrict);

        modelBuilder.Entity<Employee>()
            .Property(e => e.Salary)
            .HasPrecision(10, 2);
    }
}

Run dotnet ef database update, then dotnet run. Hit GET /departments/summary and you'll see the headcount and average salary computed straight from SQL. Stop the app, restart it, and query again — the data is still there, because it now lives in employees.db on disk, not in process memory.

Try It Yourself — Extension Challenges

Key Takeaway

You've connected a real ASP.NET Core API to a real, persistent, relational database — with async data access and LINQ queries running all the way down to SQL. Next: going even deeper on the data layer itself.

dotnetmadeeasy.com — Learn C# and .NET, the right way.