Day 18: CRUD Operations Part 1 (Create & Read)

Welcome to Day 18. Our database is set up. Now we need to interact with it. CRUD stands for Create, Read, Update, and Delete. Today we focus on the first two.

Because we registered AppDbContext in the DI container yesterday, we can instantly request it in our endpoints!

Create (Inserting Data)

To add data, we Add() it to the DbSet and call SaveChangesAsync(). EF Core generates the INSERT INTO SQL statements under the hood!

app.MapPost("/books", async (Book newBook, AppDbContext db) => 
{
    // The framework magically turns the incoming JSON body into a 'Book' object!
    
    db.Books.Add(newBook);
    
    // Nothing is saved to the database until you call this!
    await db.SaveChangesAsync(); 
    
    return Results.Created($"/books/{newBook.Id}", newBook);
});

Note: You never need to pass the Id in the JSON request. EF Core knows it is the Primary Key and SQLite will auto-increment it for you.

Read (Querying Data)

Querying data with EF Core is beautiful. You just use standard LINQ! When you write LINQ against db.Books, EF Core translates that LINQ into incredibly efficient SELECT SQL statements.

Getting All Items

using Microsoft.EntityFrameworkCore; // Required for async EF operations!

app.MapGet("/books", async (AppDbContext db) => 
{
    // SELECT * FROM Books;
    var allBooks = await db.Books.ToListAsync();
    
    return Results.Ok(allBooks);
});

Getting a Single Item by ID

The most efficient way to get a single item by its Primary Key is FindAsync.

app.MapGet("/books/{id}", async (int id, AppDbContext db) => 
{
    // SELECT * FROM Books WHERE Id = {id} LIMIT 1;
    var book = await db.Books.FindAsync(id);
    
    if (book is null) return Results.NotFound();
    
    return Results.Ok(book);
});

Filtering Data with LINQ Where

Remember LINQ from Day 7? It translates perfectly to SQL.

app.MapGet("/books/cheap", async (AppDbContext db) => 
{
    // SELECT * FROM Books WHERE Price < 20.0;
    var cheapBooks = await db.Books.Where(b => b.Price < 20.0m).ToListAsync();
    
    return Results.Ok(cheapBooks);
});

Challenge for Day 18

Create a GET /books/search endpoint that accepts an author query parameter. Use EF Core and LINQ to return a list of books where the author exactly matches the query parameter!

Tomorrow: CRUD Operations Part 2 (Update & Delete)!