What We Build
A Books API that stores data in a database. We use DTOs (Data Transfer Objects) so the API contract is separate from the database model.
Step 1: Create the Project and Packages
bash
dotnet new webapi -n BooksApi
cd BooksApi
dotnet add package Microsoft.EntityFrameworkCore.Sqlite
dotnet add package Microsoft.EntityFrameworkCore.Desig
Step 2: Entity and DbContext
csharp
using Microsoft.EntityFrameworkCore;
public class Book
{
public int Id { get; set; }
public required string Title { get; set; }
public required string Author { get; set; }
public decimal Price { get; set; }
public DateTime CreatedUtc { get; set; } = DateTime.UtcNow;
}
public class AppDbContext(DbContextOptions<AppDbContext> options) : DbContext(options)
{
public DbSet<Book> Books => Set<Book>();
}
Step 3: DTOs
csharp
using System.ComponentModel.DataAnnotations;
public record BookDto(int Id, string Title, string Author, decimal Price);
public class SaveBookRequest
{
[Required, StringLength(200, MinimumLength = 1)]
public string Title { get; set; } = "";
[Required, StringLength(100)]
public string Author { get; set; } = "";
[Range(0, 10000)]
public decimal Price { get; set; }
}
Why DTOs? The database entity may contain fields clients must never see or set (for example internal flags or password hashes). DTOs expose only what the API promises, and you can change the database without breaking clients.
Step 4: Register the DbContext
csharp
// appsettings.json
// "ConnectionStrings": { "Default": "Data Source=books.db" }
builder.Services.AddDbContext<AppDbContext>(options =>
options.UseSqlite(builder.Configuration.GetConnectionString("Default")));
DbContext lifetime: AddDbContext registers it as scoped: one instance per request, disposed automatically.
Step 5: Create the Database
bash
dotnet ef migrations add InitialCreate
dotnet ef database update
Step 6: Endpoints
csharp
using Microsoft.AspNetCore.Http.HttpResults;
var books = app.MapGroup("/api/books").WithTags("Books");
books.MapGet("/", async (AppDbContext db, int page = 1, int pageSize = 10, string? search = null) =>
{
pageSize = Math.Clamp(pageSize, 1, 100);
var query = db.Books.AsNoTracking();
if (!string.IsNullOrWhiteSpace(search))
query = query.Where(b => b.Title.Contains(search) || b.Author.Contains(search));
int total = await query.CountAsync();
var items = await query
.OrderBy(b => b.Id)
.Skip((page - 1) * pageSize)
.Take(pageSize)
.Select(b => new BookDto(b.Id, b.Title, b.Author, b.Price))
.ToListAsync();
return TypedResults.Ok(new { page, pageSize, total, items });
});
books.MapGet("/{id:int}", async Task<Results<Ok<BookDto>, NotFound>> (int id, AppDbContext db) =>
{
var b = await db.Books.AsNoTracking().FirstOrDefaultAsync(x => x.Id == id);
return b is null
? TypedResults.NotFound()
: TypedResults.Ok(new BookDto(b.Id, b.Title, b.Author, b.Price));
});
books.MapPost("/", async (SaveBookRequest request, AppDbContext db) =>
{
var book = new Book { Title = request.Title, Author = request.Author, Price = request.Price };
db.Books.Add(book);
await db.SaveChangesAsync();
var dto = new BookDto(book.Id, book.Title, book.Author, book.Price);
return TypedResults.Created($"/api/books/{book.Id}", dto);
});
books.MapPut("/{id:int}", async Task<Results<NoContent, NotFound>> (int id, SaveBookRequest request, AppDbContext db) =>
{
var book = await db.Books.FindAsync(id);
if (book is null) return TypedResults.NotFound();
book.Title = request.Title;
book.Author = request.Author;
book.Price = request.Price;
await db.SaveChangesAsync();
return TypedResults.NoContent();
});
books.MapDelete("/{id:int}", async Task<Results<NoContent, NotFound>> (int id, AppDbContext db) =>
{
int deleted = await db.Books.Where(b => b.Id == id).ExecuteDeleteAsync();
return deleted == 0 ? TypedResults.NotFound() : TypedResults.NoContent();
});
Step 7: Validation
csharp
builder.Services.AddValidation(); // .NET 10: validates DataAnnotations on SaveBookRequest
Step 8: Test It
text
POST https://localhost:7001/api/books
Content-Type: application/json
{ "title": "Refactoring", "author": "Martin Fowler", "price": 45 }
###
GET https://localhost:7001/api/books?search=fowler&page=1&pageSize=5
Next Step: A Service Layer
As logic grows, move database code from the endpoints into a service such as IBookService registered with AddScoped. The endpoints then only translate HTTP to method calls. This keeps your API easy to test and change.
| Layer | Responsibility |
| Endpoint / controller | HTTP: binding, status codes |
| Service | Business rules |
| DbContext | Data access |
Conclusion
You now have a complete database-backed API with paging, search, DTOs and validation. Use DTOs for the contract, AsNoTracking for reads and ExecuteDelete for simple deletes.
Quick Summary: AddDbContext, migrations, DTOs in and out, paging with Skip/Take.