6. Worked example — "Products" CRUD with search and paging

CREATE TABLE dbo.Products (
    Id         INT IDENTITY PRIMARY KEY,
    Name       NVARCHAR(200) NOT NULL,
    Category   NVARCHAR(100) NOT NULL,
    Price      DECIMAL(18,2) NOT NULL CHECK (Price >= 0),
    IsActive   BIT NOT NULL DEFAULT 1,
    RowVersion ROWVERSION
);
CREATE INDEX IX_Products_Category_Name ON dbo.Products(Category, Name) INCLUDE (Price) WHERE IsActive = 1;

public class Product { public int Id { get; set; } public string Name { get; set; } = ""; public string Category { get; set; } = "";
    public decimal Price { get; set; } public bool IsActive { get; set; } = true; public byte[] RowVersion { get; set; } = []; }

public record ProductDto(int Id, string Name, string Category, decimal Price);
public record SaveProductRequest(
    [property: Required, MaxLength(200)] string Name,
    [property: Required, MaxLength(100)] string Category,
    [property: Range(0, 1_000_000)] decimal Price);

var products = app.MapGroup("/api/products").RequireAuthorization();

products.MapGet("/", async (AppDb db, string? q, int page = 1, int size = 20) =>
{
    var query = db.Products.AsNoTracking().Where(p => p.IsActive);
    if (!string.IsNullOrWhiteSpace(q)) query = query.Where(p => p.Name.Contains(q));
    var total = await query.CountAsync();
    var items = await query.OrderBy(p => p.Name).Skip((page - 1) * size).Take(size)
        .Select(p => new ProductDto(p.Id, p.Name, p.Category, p.Price)).ToListAsync();
    return Results.Ok(new { items, total });
});

products.MapPost("/", async (AppDb db, SaveProductRequest r) =>
{
    var p = new Product { Name = r.Name, Category = r.Category, Price = r.Price };
    db.Products.Add(p);
    await db.SaveChangesAsync();
    return Results.Created($"/api/products/{p.Id}", new ProductDto(p.Id, p.Name, p.Category, p.Price));
});

products.MapDelete("/{id:int}", async (AppDb db, int id) =>
    await db.Products.Where(p => p.Id == id).ExecuteUpdateAsync(s => s.SetProperty(p => p.IsActive, false)) == 1
        ? Results.NoContent() : Results.NotFound());   // soft delete

function Products() {
  const [q, setQ] = useState("");
  const [page, setPage] = useState(1);
  const [data, setData] = useState<{ items: ProductDto[]; total: number }>({ items: [], total: 0 });

  useEffect(() => {
    const ctrl = new AbortController();
    const t = setTimeout(() =>
      fetch(`/api/products?q=${encodeURIComponent(q)}&page=${page}`, { signal: ctrl.signal })
        .then(r => r.json()).then(setData).catch(() => {}), 300);   // debounce typing
    return () => { clearTimeout(t); ctrl.abort(); };
  }, [q, page]);

  return (<>
    <input value={q} onChange={e => { setQ(e.target.value); setPage(1); }} placeholder="Search" />
    <table><tbody>{data.items.map(p => <tr key={p.id}><td>{p.name}</td><td>{p.category}</td><td>{p.price}</td></tr>)}</tbody></table>
    <button disabled={page === 1} onClick={() => setPage(page - 1)}>Prev</button>
    <button disabled={page * 20 >= data.total} onClick={() => setPage(page + 1)}>Next</button>
  </>);
}