Core API CRUD Operations with Database

With EF Core connected to SQL Server, it is time to replace the in-memory list in the BookStore API with real database operations. CRUD stands for Create, Read, Update, and Delete — the four fundamental operations for any data-driven API.

CRUD Operations and HTTP Methods

Updating BookService to Use the Database

The BookService now uses BookStoreDbContext to talk to the database. The in-memory list is replaced with actual database calls.

// Services/BookService.cs
using BookStoreAPI.Data;
using BookStoreAPI.Models;
using Microsoft.EntityFrameworkCore;

namespace BookStoreAPI.Services
{
    public class BookService : IBookService
    {
        private readonly BookStoreDbContext _context;

        public BookService(BookStoreDbContext context)
        {
            _context = context;
        }

        // READ ALL
        public async Task<List<Book>> GetAllAsync()
        {
            return await _context.Books.ToListAsync();
        }

        // READ ONE
        public async Task<Book?> GetByIdAsync(int id)
        {
            return await _context.Books.FindAsync(id);
        }

        // CREATE
        public async Task<Book> CreateAsync(Book book)
        {
            book.CreatedDate = DateTime.UtcNow;
            _context.Books.Add(book);
            await _context.SaveChangesAsync();
            return book;
        }

        // UPDATE
        public async Task<bool> UpdateAsync(int id, Book updatedBook)
        {
            var book = await _context.Books.FindAsync(id);
            if (book == null) return false;

            book.Title = updatedBook.Title;
            book.Author = updatedBook.Author;
            book.Price = updatedBook.Price;
            book.Category = updatedBook.Category;
            book.IsAvailable = updatedBook.IsAvailable;

            await _context.SaveChangesAsync();
            return true;
        }

        // DELETE
        public async Task<bool> DeleteAsync(int id)
        {
            var book = await _context.Books.FindAsync(id);
            if (book == null) return false;

            _context.Books.Remove(book);
            await _context.SaveChangesAsync();
            return true;
        }
    }
}

Updating the Interface

// Services/IBookService.cs
namespace BookStoreAPI.Services
{
    public interface IBookService
    {
        Task<List<Book>> GetAllAsync();
        Task<Book?> GetByIdAsync(int id);
        Task<Book> CreateAsync(Book book);
        Task<bool> UpdateAsync(int id, Book updatedBook);
        Task<bool> DeleteAsync(int id);
    }
}

Updating BooksController to Use Async Methods

// Controllers/BooksController.cs
using Microsoft.AspNetCore.Mvc;
using BookStoreAPI.Models;
using BookStoreAPI.Services;

namespace BookStoreAPI.Controllers
{
    [ApiController]
    [Route("api/[controller]")]
    public class BooksController : ControllerBase
    {
        private readonly IBookService _bookService;

        public BooksController(IBookService bookService)
        {
            _bookService = bookService;
        }

        // GET /api/books
        [HttpGet]
        public async Task<IActionResult> GetAll()
        {
            var books = await _bookService.GetAllAsync();
            return Ok(books);
        }

        // GET /api/books/1
        [HttpGet("{id:int}")]
        public async Task<IActionResult> GetById(int id)
        {
            var book = await _bookService.GetByIdAsync(id);
            if (book == null)
                return NotFound($"Book with Id {id} was not found.");
            return Ok(book);
        }

        // POST /api/books
        [HttpPost]
        public async Task<IActionResult> Create([FromBody] Book book)
        {
            var created = await _bookService.CreateAsync(book);
            return CreatedAtAction(nameof(GetById), new { id = created.Id }, created);
        }

        // PUT /api/books/1
        [HttpPut("{id:int}")]
        public async Task<IActionResult> Update(int id, [FromBody] Book book)
        {
            var success = await _bookService.UpdateAsync(id, book);
            if (!success)
                return NotFound($"Book with Id {id} was not found.");
            return NoContent();
        }

        // DELETE /api/books/1
        [HttpDelete("{id:int}")]
        public async Task<IActionResult> Delete(int id)
        {
            var success = await _bookService.DeleteAsync(id);
            if (!success)
                return NotFound($"Book with Id {id} was not found.");
            return NoContent();
        }
    }
}

Understanding Async / Await

Database operations take time — the app waits for SQL Server to respond. Using async/await lets the server handle other requests while waiting for the database, instead of blocking the thread.

Without async:   Thread waits (blocked) ──────[ DB responds ]──── Thread continues
With async:      Thread is free ─── [ DB responds ] ─── Thread picks up where it left off

EF Core Operations Explained

Add and SaveChanges

_context.Books.Add(book);          // Marks the book as "to be inserted"
await _context.SaveChangesAsync(); // Sends INSERT statement to SQL Server

Find and Update

var book = await _context.Books.FindAsync(id);  // SELECT WHERE Id = id
book.Price = 24.99m;                            // Change the property
await _context.SaveChangesAsync();              // EF Core detects the change and sends UPDATE

EF Core tracks the state of every object it fetches. When SaveChangesAsync() runs, it compares the current state to the original state and generates the right SQL automatically. This is called change tracking.

Remove and SaveChanges

var book = await _context.Books.FindAsync(id);  // Fetch the book
_context.Books.Remove(book);                    // Mark as deleted
await _context.SaveChangesAsync();              // Sends DELETE statement

Testing CRUD with Postman

Create a Book

POST http://localhost:5000/api/books
Content-Type: application/json

{
  "title": "Clean Architecture",
  "author": "Robert C. Martin",
  "price": 39.99,
  "category": "Technology",
  "isAvailable": true
}

Response: 201 Created
{ "id": 3, "title": "Clean Architecture", ... }

Get All Books

GET http://localhost:5000/api/books
Response: 200 OK
[
  { "id": 1, "title": "Clean Code", ... },
  { "id": 2, "title": "The Pragmatic Programmer", ... },
  { "id": 3, "title": "Clean Architecture", ... }
]

Update a Book

PUT http://localhost:5000/api/books/3
Content-Type: application/json

{
  "title": "Clean Architecture (2nd Edition)",
  "author": "Robert C. Martin",
  "price": 44.99,
  "category": "Technology",
  "isAvailable": true
}

Response: 204 No Content

Delete a Book

DELETE http://localhost:5000/api/books/3
Response: 204 No Content

Data Now Persists

Unlike the in-memory list, data written to SQL Server persists when the application restarts. Stop and restart the API, then call GET /api/books — the books added before the restart are still there.

Key Points

• EF Core replaces manual SQL with C# methods: Add(), FindAsync(), Remove(), and SaveChangesAsync().
• SaveChangesAsync() must be called after any create, update, or delete to persist changes to the database.
• EF Core's change tracking detects which properties changed and generates only the necessary SQL update statements.
• Async methods (ToListAsync, SaveChangesAsync) keep the server responsive during database operations.
• Data written to SQL Server survives application restarts — unlike the static in-memory list used earlier.