Project: REST API with EF Core

This project turns the previous lessons into a small but complete HTTP API: PostgreSQL through EF Core, request and response DTOs, validation, pagination, consistent errors, integration tests, and a runbook someone else can follow. The domain is a library catalog with authors and books; the patterns scale to orders, tickets, and tenants.

Layout

LibraryApi/
  Program.cs
  Data/LibraryDb.cs
  Data/Entities.cs
  Contracts/BookContracts.cs
  Endpoints/BookEndpoints.cs
  appsettings.json
tests/LibraryApi.Tests/
  ApiFactory.cs
  BookEndpointsTests.cs

Create the app with dotnet new webapi -n LibraryApi --use-minimal-apis, then add the test project with dotnet new xunit -n LibraryApi.Tests and reference the app.

Entities and context

public sealed class Author
{
    public int Id { get; set; }
    public required string Name { get; set; }
    public List<Book> Books { get; set; } = [];
}
 
public sealed class Book
{
    public int Id { get; set; }
    public required string Title { get; set; }
    public decimal Price { get; set; }
    public int AuthorId { get; set; }
    public Author Author { get; set; } = null!;
}

public sealed class LibraryDb(DbContextOptions<LibraryDb> options) : DbContext(options)
{
    public DbSet<Book> Books => Set<Book>();
    public DbSet<Author> Authors => Set<Author>();
 
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Author>()
            .HasIndex(a => a.Name)
            .IsUnique();
 
        modelBuilder.Entity<Book>(book =>
        {
            book.Property(b => b.Title).HasMaxLength(200).IsRequired();
            book.Property(b => b.Price).HasPrecision(10, 2);
            book.HasOne(b => b.Author)
                .WithMany(a => a.Books)
                .HasForeignKey(b => b.AuthorId)
                .OnDelete(DeleteBehavior.Restrict);
        });
    }
}

The unique author index is a real constraint, not a hopeful AnyAsync check. The database remains correct even when two requests race.

Contracts

Never return EF entities directly. DTOs define the public shape and prevent navigation cycles:

public sealed record BookDto(int Id, string Title, string Author, decimal Price);
public sealed record CreateBookRequest(string Title, int AuthorId, decimal Price);
public sealed record UpdateBookRequest(string Title, decimal Price);
public sealed record CreateAuthorRequest(string Name);
public sealed record PagedResult<T>(IReadOnlyList<T> Items, int Page, int PageSize, int TotalCount);

Endpoints

using LibraryApi.Contracts;
using LibraryApi.Data;
using Microsoft.AspNetCore.Http.HttpResults;
using Microsoft.EntityFrameworkCore;
 
namespace LibraryApi.Endpoints;
 
public static class BookEndpoints
{
    public static RouteGroupBuilder MapBooks(this IEndpointRouteBuilder app)
    {
        var group = app.MapGroup("/api/books").WithTags("Books");
 
        group.MapGet("/", GetBooks);
        group.MapGet("/{id:int}", GetBook);
        group.MapPost("/", CreateBook);
        group.MapPut("/{id:int}", UpdateBook);
        group.MapDelete("/{id:int}", DeleteBook);
 
        return group;
    }
 
    private static async Task<Ok<PagedResult<BookDto>>> GetBooks(
        LibraryDb db, int page = 1, int pageSize = 20, string? q = null,
        CancellationToken ct = default)
    {
        page = Math.Max(1, page);
        pageSize = Math.Clamp(pageSize, 1, 100);
 
        var query = db.Books.AsNoTracking();
        if (!string.IsNullOrWhiteSpace(q))
            query = query.Where(b => b.Title.Contains(q));
 
        var total = await query.CountAsync(ct);
        var items = await query
            .OrderBy(b => b.Title)
            .ThenBy(b => b.Id)
            .Skip((page - 1) * pageSize)
            .Take(pageSize)
            .Select(b => new BookDto(b.Id, b.Title, b.Author.Name, b.Price))
            .ToListAsync(ct);
 
        return TypedResults.Ok(new PagedResult<BookDto>(items, page, pageSize, total));
    }
 
    private static async Task<Results<Ok<BookDto>, NotFound>> GetBook(
        int id, LibraryDb db, CancellationToken ct)
    {
        var book = await db.Books
            .AsNoTracking()
            .Where(b => b.Id == id)
            .Select(b => new BookDto(b.Id, b.Title, b.Author.Name, b.Price))
            .SingleOrDefaultAsync(ct);
 
        if (book is null)
            return TypedResults.NotFound();
 
        return TypedResults.Ok(book);
    }
 
    private static async Task<Results<Created<BookDto>, ValidationProblem, NotFound>> CreateBook(
        CreateBookRequest request, LibraryDb db, CancellationToken ct)
    {
        var errors = Validate(request.Title, request.Price);
        if (errors.Count > 0)
            return TypedResults.ValidationProblem(errors);
 
        var author = await db.Authors.FindAsync([request.AuthorId], ct);
        if (author is null)
            return TypedResults.NotFound();
 
        var book = new Book
        {
            Title = request.Title.Trim(),
            Price = request.Price,
            AuthorId = author.Id
        };
 
        db.Books.Add(book);
        await db.SaveChangesAsync(ct);
 
        var dto = new BookDto(book.Id, book.Title, author.Name, book.Price);
        return TypedResults.Created($"/api/books/{book.Id}", dto);
    }
 
    private static async Task<Results<NoContent, ValidationProblem, NotFound>> UpdateBook(
        int id, UpdateBookRequest request, LibraryDb db, CancellationToken ct)
    {
        var errors = Validate(request.Title, request.Price);
        if (errors.Count > 0)
            return TypedResults.ValidationProblem(errors);
 
        var book = await db.Books.FindAsync([id], ct);
        if (book is null)
            return TypedResults.NotFound();
 
        book.Title = request.Title.Trim();
        book.Price = request.Price;
        await db.SaveChangesAsync(ct);
 
        return TypedResults.NoContent();
    }
 
    private static async Task<Results<NoContent, NotFound>> DeleteBook(
        int id, LibraryDb db, CancellationToken ct)
    {
        var book = await db.Books.FindAsync([id], ct);
        if (book is null)
            return TypedResults.NotFound();
 
        db.Books.Remove(book);
        await db.SaveChangesAsync(ct);
        return TypedResults.NoContent();
    }
 
    private static Dictionary<string, string[]> Validate(string title, decimal price)
    {
        var errors = new Dictionary<string, string[]>();
 
        if (string.IsNullOrWhiteSpace(title))
            errors["title"] = ["Title is required."];
        else if (title.Length > 200)
            errors["title"] = ["Title must be 200 characters or fewer."];
 
        if (price <= 0)
            errors["price"] = ["Price must be greater than zero."];
 
        return errors;
    }
}

OrderBy before Skip and Take is mandatory for stable pages. Without it, the database may return rows in a different order between requests, duplicating or skipping records. ThenBy breaks ties.

Author endpoints and errors

app.MapGet("/api/authors", async (LibraryDb db, CancellationToken ct) =>
    TypedResults.Ok(await db.Authors.AsNoTracking()
        .OrderBy(a => a.Name)
        .Select(a => new { a.Id, a.Name, BookCount = a.Books.Count })
        .ToListAsync(ct)));
 
app.MapPost("/api/authors", CreateAuthor);
 
static async Task<IResult> CreateAuthor(
    CreateAuthorRequest request, LibraryDb db, CancellationToken ct)
{
    if (string.IsNullOrWhiteSpace(request.Name))
        return Results.ValidationProblem(
            new Dictionary<string, string[]> { ["name"] = ["Name is required."] });
 
    var name = request.Name.Trim();
    if (await db.Authors.AnyAsync(a => a.Name == name, ct))
        return Results.Problem(statusCode: 409, title: "Author already exists.");
 
    var author = new Author { Name = name };
    db.Authors.Add(author);
    await db.SaveChangesAsync(ct);
 
    return Results.Created($"/api/authors/{author.Id}", new { author.Id, author.Name });
}

A unique-index race still surfaces as a DbUpdateException. Catch it at the boundary and translate it to 409 rather than letting it become a 500; the pre-check is a friendly fast path, not a guarantee. With PostgreSQL, check for SQLSTATE 23505 on the inner PostgresException rather than catching every database failure as a conflict.

Wiring the application

using LibraryApi.Data;
using LibraryApi.Endpoints;
using Microsoft.EntityFrameworkCore;
 
var builder = WebApplication.CreateBuilder(args);
 
builder.Services.AddDbContext<LibraryDb>(options =>
    options.UseNpgsql(builder.Configuration.GetConnectionString("Library")));
 
builder.Services.AddProblemDetails();
builder.Services.AddOpenApi();
 
var app = builder.Build();
 
app.UseExceptionHandler();
app.MapOpenApi();
app.MapBooks();
 
app.MapGet("/health", () => TypedResults.Ok(new { status = "ok" }));
 
app.Run();
 
public partial class Program { }

AddDbContext is scoped; each request gets its own context. The public partial class Program declaration lets WebApplicationFactory<Program> find the entry point.

Integration tests

using System.Data.Common;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Mvc.Testing;
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection.Extensions;
 
public sealed class ApiFactory : WebApplicationFactory<Program>
{
    private readonly SqliteConnection _connection = new("DataSource=:memory:");
 
    public ApiFactory() => _connection.Open();
 
    protected override void ConfigureWebHost(IWebHostBuilder builder)
    {
        builder.UseEnvironment("Testing");
        builder.ConfigureServices(services =>
        {
            services.RemoveAll<DbContextOptions<LibraryDb>>();
            services.AddSingleton<DbConnection>(_connection);
            services.AddDbContext<LibraryDb>((sp, options) =>
                options.UseSqlite(sp.GetRequiredService<DbConnection>()));
        });
    }
 
    protected override void Dispose(bool disposing)
    {
        base.Dispose(disposing);
        if (disposing)
            _connection.Dispose();
    }
}

The test project needs Microsoft.AspNetCore.Mvc.Testing and Microsoft.EntityFrameworkCore.Sqlite in addition to the app reference. The SQLite connection stays open for the lifetime of the factory; closing it discards the schema and data.

[Fact]
public async Task Create_then_list_returns_the_book()
{
    await using var factory = new ApiFactory();
    int authorId;
 
    using (var scope = factory.Services.CreateScope())
    {
        var db = scope.ServiceProvider.GetRequiredService<LibraryDb>();
        await db.Database.EnsureCreatedAsync();
 
        var author = new Author { Name = "Frank Herbert" };
        db.Authors.Add(author);
        await db.SaveChangesAsync();
        authorId = author.Id;
    }
 
    var client = factory.CreateClient();
    var created = await client.PostAsJsonAsync(
        "/api/books", new CreateBookRequest("Dune", authorId, 19.99m));
 
    Assert.Equal(HttpStatusCode.Created, created.StatusCode);
 
    var page = await client.GetFromJsonAsync<PagedResult<BookDto>>(
        "/api/books?page=1&pageSize=10");
 
    Assert.NotNull(page);
    Assert.Single(page.Items);
    Assert.Equal("Dune", page.Items[0].Title);
}

Also test the failure paths: blank title, zero price, missing author, unknown ID, and a page size above 100. SQLite does not enforce every PostgreSQL behavior, so run migration and concurrency tests against the real provider with Testcontainers.

Runbook

# 1. Start PostgreSQL
docker run --name library-db \
  -e POSTGRES_PASSWORD=devpass \
  -e POSTGRES_DB=library \
  -p 5432:5432 -d postgres:17
 
# 2. Configure locally
dotnet user-secrets init
dotnet user-secrets set "ConnectionStrings:Library" \
  "Host=localhost;Database=library;Username=postgres;Password=devpass"
 
# 3. Create and apply the schema
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet tool install --global dotnet-ef
dotnet ef migrations add InitialCreate
dotnet ef database update
 
# 4. Run and verify
dotnet run
curl http://localhost:5000/health   # use the URL printed by dotnet run
dotnet test

Endpoint                              Success        Failure
GET  /api/books?page=1&pageSize=20    200            400 for bad paging
GET  /api/books/{id}                  200            404
POST /api/books                       201 + Location 400/404
PUT  /api/books/{id}                  204            400/404
DELETE /api/books/{id}                204            404
GET  /api/authors                     200
POST /api/authors                     201            400/409
GET  /health                          200

Configuration comes from the environment. In production set ConnectionStrings__Library from the platform secret store; never bake it into the image. Apply migrations as a release step, not from every application instance.

Common mistakes

Forgetting SaveChangesAsync and returning a 201 for an object that was never stored.
Calling ToListAsync before Skip and Take, which loads the whole table.
Pagination without a deterministic order.
Accepting any pageSize, including 100,000.
Returning entities and leaking navigation properties or internal columns.
Checking uniqueness only in application code and ignoring the database constraint.
Using the in-memory EF provider for integration tests and missing relational failures.

Practice

Add GET /api/authors/{id} with the author's books, projected in one query.
Add sorting to the books list with an allow-list of fields, not raw SQL or dynamic LINQ from user input.
Add optimistic concurrency with a rowversion or version column and return 409 on conflict.
Add a POST /api/books/{id}/reviews endpoint with a one-to-many review entity and validation.
Add integration tests for 400, 404, and 409. Seed each test through the API where possible.
Write a Dockerfile and a Compose file that starts the API and PostgreSQL, applies migrations once, and exposes a health check.

Layout

LibraryApi/
  Program.cs
  Data/LibraryDb.cs
  Data/Entities.cs
  Contracts/BookContracts.cs
  Endpoints/BookEndpoints.cs
  appsettings.json
tests/LibraryApi.Tests/
  ApiFactory.cs
  BookEndpointsTests.cs

Create the app with dotnet new webapi -n LibraryApi --use-minimal-apis, then add the test project with dotnet new xunit -n LibraryApi.Tests and reference the app.

Entities and context

public sealed class Author
{
    public int Id { get; set; }
    public required string Name { get; set; }
    public List<Book> Books { get; set; } = [];
}
 
public sealed class Book
{
    public int Id { get; set; }
    public required string Title { get; set; }
    public decimal Price { get; set; }
    public int AuthorId { get; set; }
    public Author Author { get; set; } = null!;
}

public sealed class LibraryDb(DbContextOptions<LibraryDb> options) : DbContext(options)
{
    public DbSet<Book> Books => Set<Book>();
    public DbSet<Author> Authors => Set<Author>();
 
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Author>()
            .HasIndex(a => a.Name)
            .IsUnique();
 
        modelBuilder.Entity<Book>(book =>
        {
            book.Property(b => b.Title).HasMaxLength(200).IsRequired();
            book.Property(b => b.Price).HasPrecision(10, 2);
            book.HasOne(b => b.Author)
                .WithMany(a => a.Books)
                .HasForeignKey(b => b.AuthorId)
                .OnDelete(DeleteBehavior.Restrict);
        });
    }
}

The unique author index is a real constraint, not a hopeful AnyAsync check. The database remains correct even when two requests race.

Contracts

Never return EF entities directly. DTOs define the public shape and prevent navigation cycles:

public sealed record BookDto(int Id, string Title, string Author, decimal Price);
public sealed record CreateBookRequest(string Title, int AuthorId, decimal Price);
public sealed record UpdateBookRequest(string Title, decimal Price);
public sealed record CreateAuthorRequest(string Name);
public sealed record PagedResult<T>(IReadOnlyList<T> Items, int Page, int PageSize, int TotalCount);

Endpoints

using LibraryApi.Contracts;
using LibraryApi.Data;
using Microsoft.AspNetCore.Http.HttpResults;
using Microsoft.EntityFrameworkCore;
 
namespace LibraryApi.Endpoints;
 
public static class BookEndpoints
{
    public static RouteGroupBuilder MapBooks(this IEndpointRouteBuilder app)
    {
        var group = app.MapGroup("/api/books").WithTags("Books");
 
        group.MapGet("/", GetBooks);
        group.MapGet("/{id:int}", GetBook);
        group.MapPost("/", CreateBook);
        group.MapPut("/{id:int}", UpdateBook);
        group.MapDelete("/{id:int}", DeleteBook);
 
        return group;
    }
 
    private static async Task<Ok<PagedResult<BookDto>>> GetBooks(
        LibraryDb db, int page = 1, int pageSize = 20, string? q = null,
        CancellationToken ct = default)
    {
        page = Math.Max(1, page);
        pageSize = Math.Clamp(pageSize, 1, 100);
 
        var query = db.Books.AsNoTracking();
        if (!string.IsNullOrWhiteSpace(q))
            query = query.Where(b => b.Title.Contains(q));
 
        var total = await query.CountAsync(ct);
        var items = await query
            .OrderBy(b => b.Title)
            .ThenBy(b => b.Id)
            .Skip((page - 1) * pageSize)
            .Take(pageSize)
            .Select(b => new BookDto(b.Id, b.Title, b.Author.Name, b.Price))
            .ToListAsync(ct);
 
        return TypedResults.Ok(new PagedResult<BookDto>(items, page, pageSize, total));
    }
 
    private static async Task<Results<Ok<BookDto>, NotFound>> GetBook(
        int id, LibraryDb db, CancellationToken ct)
    {
        var book = await db.Books
            .AsNoTracking()
            .Where(b => b.Id == id)
            .Select(b => new BookDto(b.Id, b.Title, b.Author.Name, b.Price))
            .SingleOrDefaultAsync(ct);
 
        if (book is null)
            return TypedResults.NotFound();
 
        return TypedResults.Ok(book);
    }
 
    private static async Task<Results<Created<BookDto>, ValidationProblem, NotFound>> CreateBook(
        CreateBookRequest request, LibraryDb db, CancellationToken ct)
    {
        var errors = Validate(request.Title, request.Price);
        if (errors.Count > 0)
            return TypedResults.ValidationProblem(errors);
 
        var author = await db.Authors.FindAsync([request.AuthorId], ct);
        if (author is null)
            return TypedResults.NotFound();
 
        var book = new Book
        {
            Title = request.Title.Trim(),
            Price = request.Price,
            AuthorId = author.Id
        };
 
        db.Books.Add(book);
        await db.SaveChangesAsync(ct);
 
        var dto = new BookDto(book.Id, book.Title, author.Name, book.Price);
        return TypedResults.Created($"/api/books/{book.Id}", dto);
    }
 
    private static async Task<Results<NoContent, ValidationProblem, NotFound>> UpdateBook(
        int id, UpdateBookRequest request, LibraryDb db, CancellationToken ct)
    {
        var errors = Validate(request.Title, request.Price);
        if (errors.Count > 0)
            return TypedResults.ValidationProblem(errors);
 
        var book = await db.Books.FindAsync([id], ct);
        if (book is null)
            return TypedResults.NotFound();
 
        book.Title = request.Title.Trim();
        book.Price = request.Price;
        await db.SaveChangesAsync(ct);
 
        return TypedResults.NoContent();
    }
 
    private static async Task<Results<NoContent, NotFound>> DeleteBook(
        int id, LibraryDb db, CancellationToken ct)
    {
        var book = await db.Books.FindAsync([id], ct);
        if (book is null)
            return TypedResults.NotFound();
 
        db.Books.Remove(book);
        await db.SaveChangesAsync(ct);
        return TypedResults.NoContent();
    }
 
    private static Dictionary<string, string[]> Validate(string title, decimal price)
    {
        var errors = new Dictionary<string, string[]>();
 
        if (string.IsNullOrWhiteSpace(title))
            errors["title"] = ["Title is required."];
        else if (title.Length > 200)
            errors["title"] = ["Title must be 200 characters or fewer."];
 
        if (price <= 0)
            errors["price"] = ["Price must be greater than zero."];
 
        return errors;
    }
}

Author endpoints and errors

app.MapGet("/api/authors", async (LibraryDb db, CancellationToken ct) =>
    TypedResults.Ok(await db.Authors.AsNoTracking()
        .OrderBy(a => a.Name)
        .Select(a => new { a.Id, a.Name, BookCount = a.Books.Count })
        .ToListAsync(ct)));
 
app.MapPost("/api/authors", CreateAuthor);
 
static async Task<IResult> CreateAuthor(
    CreateAuthorRequest request, LibraryDb db, CancellationToken ct)
{
    if (string.IsNullOrWhiteSpace(request.Name))
        return Results.ValidationProblem(
            new Dictionary<string, string[]> { ["name"] = ["Name is required."] });
 
    var name = request.Name.Trim();
    if (await db.Authors.AnyAsync(a => a.Name == name, ct))
        return Results.Problem(statusCode: 409, title: "Author already exists.");
 
    var author = new Author { Name = name };
    db.Authors.Add(author);
    await db.SaveChangesAsync(ct);
 
    return Results.Created($"/api/authors/{author.Id}", new { author.Id, author.Name });
}

Wiring the application

using LibraryApi.Data;
using LibraryApi.Endpoints;
using Microsoft.EntityFrameworkCore;
 
var builder = WebApplication.CreateBuilder(args);
 
builder.Services.AddDbContext<LibraryDb>(options =>
    options.UseNpgsql(builder.Configuration.GetConnectionString("Library")));
 
builder.Services.AddProblemDetails();
builder.Services.AddOpenApi();
 
var app = builder.Build();
 
app.UseExceptionHandler();
app.MapOpenApi();
app.MapBooks();
 
app.MapGet("/health", () => TypedResults.Ok(new { status = "ok" }));
 
app.Run();
 
public partial class Program { }

AddDbContext is scoped; each request gets its own context. The public partial class Program declaration lets WebApplicationFactory<Program> find the entry point.

Integration tests

using System.Data.Common;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Mvc.Testing;
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection.Extensions;
 
public sealed class ApiFactory : WebApplicationFactory<Program>
{
    private readonly SqliteConnection _connection = new("DataSource=:memory:");
 
    public ApiFactory() => _connection.Open();
 
    protected override void ConfigureWebHost(IWebHostBuilder builder)
    {
        builder.UseEnvironment("Testing");
        builder.ConfigureServices(services =>
        {
            services.RemoveAll<DbContextOptions<LibraryDb>>();
            services.AddSingleton<DbConnection>(_connection);
            services.AddDbContext<LibraryDb>((sp, options) =>
                options.UseSqlite(sp.GetRequiredService<DbConnection>()));
        });
    }
 
    protected override void Dispose(bool disposing)
    {
        base.Dispose(disposing);
        if (disposing)
            _connection.Dispose();
    }
}

[Fact]
public async Task Create_then_list_returns_the_book()
{
    await using var factory = new ApiFactory();
    int authorId;
 
    using (var scope = factory.Services.CreateScope())
    {
        var db = scope.ServiceProvider.GetRequiredService<LibraryDb>();
        await db.Database.EnsureCreatedAsync();
 
        var author = new Author { Name = "Frank Herbert" };
        db.Authors.Add(author);
        await db.SaveChangesAsync();
        authorId = author.Id;
    }
 
    var client = factory.CreateClient();
    var created = await client.PostAsJsonAsync(
        "/api/books", new CreateBookRequest("Dune", authorId, 19.99m));
 
    Assert.Equal(HttpStatusCode.Created, created.StatusCode);
 
    var page = await client.GetFromJsonAsync<PagedResult<BookDto>>(
        "/api/books?page=1&pageSize=10");
 
    Assert.NotNull(page);
    Assert.Single(page.Items);
    Assert.Equal("Dune", page.Items[0].Title);
}

Runbook

# 1. Start PostgreSQL
docker run --name library-db \
  -e POSTGRES_PASSWORD=devpass \
  -e POSTGRES_DB=library \
  -p 5432:5432 -d postgres:17
 
# 2. Configure locally
dotnet user-secrets init
dotnet user-secrets set "ConnectionStrings:Library" \
  "Host=localhost;Database=library;Username=postgres;Password=devpass"
 
# 3. Create and apply the schema
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet tool install --global dotnet-ef
dotnet ef migrations add InitialCreate
dotnet ef database update
 
# 4. Run and verify
dotnet run
curl http://localhost:5000/health   # use the URL printed by dotnet run
dotnet test

Endpoint                              Success        Failure
GET  /api/books?page=1&pageSize=20    200            400 for bad paging
GET  /api/books/{id}                  200            404
POST /api/books                       201 + Location 400/404
PUT  /api/books/{id}                  204            400/404
DELETE /api/books/{id}                204            404
GET  /api/authors                     200
POST /api/authors                     201            400/409
GET  /health                          200

Common mistakes

Forgetting SaveChangesAsync and returning a 201 for an object that was never stored.
Calling ToListAsync before Skip and Take, which loads the whole table.
Pagination without a deterministic order.
Accepting any pageSize, including 100,000.
Returning entities and leaking navigation properties or internal columns.
Checking uniqueness only in application code and ignoring the database constraint.
Using the in-memory EF provider for integration tests and missing relational failures.

Practice

Add GET /api/authors/{id} with the author's books, projected in one query.
Add sorting to the books list with an allow-list of fields, not raw SQL or dynamic LINQ from user input.
Add optimistic concurrency with a rowversion or version column and return 409 on conflict.
Add a POST /api/books/{id}/reviews endpoint with a one-to-many review entity and validation.
Add integration tests for 400, 404, and 409. Seed each test through the API where possible.
Write a Dockerfile and a Compose file that starts the API and PostgreSQL, applies migrations once, and exposes a health check.

Project: REST API with EF Core

Layout

Entities and context

Contracts

Endpoints

Author endpoints and errors

Wiring the application

Integration tests

Runbook

Common mistakes

Practice

Go deeper

Minimal APIs

Entity Framework Core

Unit Testing with dotnet test

Lesson discussion

Project: REST API with EF Core

Layout

Entities and context

Contracts

Endpoints

Author endpoints and errors

Wiring the application

Integration tests

Runbook

Common mistakes

Practice

Go deeper

Minimal APIs

Entity Framework Core

Unit Testing with dotnet test

Lesson discussion