Database CRUD API Project with EF Core, Pagination & Search Masterclass

⚡ C# 12 & .NET 8 🟢 Chapter 38 of 35 📂 Phase 13: Databases & Entity Framework Core 📅 2026 Edition

📌 Covered in this chapter: EF Core Project Setup · SQLite Configuration · Auto Migrations · Repository Pattern · Pagination · Search · Dynamic Sorting · PagedResult · Database Error Handling

Welcome to Phase 13 (Chapter 38): Database-Connected CRUD API Project with EF Core, Pagination & Search! In this practical project chapter, we build a complete production-ready ASP.NET Core REST API with a real SQLite database backend using EF Core. We implement: Entity models, DbContext configuration, Repository pattern, Service layer, Controller with full CRUD, Pagination, Search, Sorting, and proper error handling.

1Project Setup & Database Configuration

C# — Program.cs with EF Core SQLite ▶ Run in Compiler

var builder = WebApplication.CreateBuilder(args);

// Register EF Core with SQLite (use SqlServer for production)
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlite(builder.Configuration.GetConnectionString("DefaultConnection")
        ?? "Data Source=products.db"));

// Register Repository and Service (Scoped per request)
builder.Services.AddScoped<IProductRepository, ProductRepository>();
builder.Services.AddScoped<IProductService, ProductService>();

builder.Services.AddControllers();
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

var app = builder.Build();

// Auto-create database on first run
using (var scope = app.Services.CreateScope())
{
    var db = scope.ServiceProvider.GetRequiredService<AppDbContext>();
    db.Database.Migrate(); // Apply pending migrations automatically
}

app.UseSwagger(); app.UseSwaggerUI();
app.MapControllers();
app.Run();

2Pagination, Search & Sorting

C# — Paginated Query with Search & Sort ▶ Run in Compiler

// Pagination Query Parameters DTO
public class ProductQueryParams
{
    public string? Search   { get; set; }
    public string? Category { get; set; }
    public string  SortBy   { get; set; } = "Name";
    public bool    Descending { get; set; } = false;
    public int     Page     { get; set; } = 1;
    public int     PageSize { get; set; } = 10;
}

// Paginated Result Wrapper
public class PagedResult<T>
{
    public List<T> Items      { get; set; } = new();
    public int     TotalCount { get; set; }
    public int     Page       { get; set; }
    public int     PageSize   { get; set; }
    public int     TotalPages => (int)Math.Ceiling((double)TotalCount / PageSize);
}

// Service Implementation
public async Task<PagedResult<Product>> GetProductsAsync(ProductQueryParams q)
{
    var query = _context.Products.AsQueryable();

    // Search filter
    if (!string.IsNullOrWhiteSpace(q.Search))
        query = query.Where(p => p.Name.Contains(q.Search) || p.Category.Contains(q.Search));

    // Category filter
    if (!string.IsNullOrWhiteSpace(q.Category))
        query = query.Where(p => p.Category == q.Category);

    // Dynamic sorting
    query = q.SortBy switch
    {
        "Price"   => q.Descending ? query.OrderByDescending(p => p.Price) : query.OrderBy(p => p.Price),
        "Stock"   => q.Descending ? query.OrderByDescending(p => p.Stock) : query.OrderBy(p => p.Stock),
        _         => q.Descending ? query.OrderByDescending(p => p.Name)  : query.OrderBy(p => p.Name)
    };

    int total = await query.CountAsync();

    var items = await query
        .Skip((q.Page - 1) * q.PageSize)
        .Take(q.PageSize)
        .ToListAsync();

    return new PagedResult<Product> { Items = items, TotalCount = total, Page = q.Page, PageSize = q.PageSize };
}

3Technical FAQs

Q1: Why use Skip() and Take() for pagination instead of loading all rows?

Skip() and Take() translate to SQL OFFSET and FETCH NEXT / LIMIT clauses, letting the database return only the requested page of records. Loading all rows into memory and paginating in C# wastes RAM and database bandwidth exponentially as data grows.

Q2: Why use AsQueryable() before applying filters?

AsQueryable() keeps the LINQ query as an IQueryable<T> expression tree that EF Core can translate to optimized SQL. Each chained Where(), OrderBy(), and Skip() adds to the SQL rather than executing separate queries.