ASP.NET Core 10 Web API: Databases, Searching, Filtering and Paging

Demo Project: CityInfo API Technologies: ASP.NET Core 10 · Entity Framework Core · SQLite · AutoMapper · Repository Pattern

Table of Contents

• Module 1 — Getting Started with Entity Framework Core
  • Introduction to EF Core
  • Entities and DbContext
  • EF Core Migrations
  • Seed Data
  • Repository Pattern
• Module 2 — Working with Data via the Repository
  • Creating a Resource (POST)
  • Updating a Resource (PUT)
  • Partial Update (PATCH)
  • Deleting a Resource (DELETE)
  • Bulk Operations: ExecuteUpdateAsync and ExecuteDeleteAsync
  • Request Cancellation (CancellationToken)
• Module 3 — Manipulating Data via the Repository
• Module 4 — Searching and Filtering Resources
  • Filtering vs Searching
  • Implementing Filtering
  • Implementing Searching
  • Deferred Execution
• Module 5 — Paging Resources
  • Pagination Concepts
  • Implementing Pagination
  • Pagination Metadata
• Module 6 — Service Layer
  • Understanding the Service Layer
  • Use Cases in Code
  • Implementing a Service Layer
• Overall Architecture
• Repository Pattern Flow
• Search / Filter / Pagination Pipeline
• Reference Tables

Module 1 — Getting Started with Entity Framework Core

Introduction to EF Core

Entity Framework Core (EF Core) is an open-source ORM (Object-Relational Mapper) for .NET. It allows you to work with a database using .NET objects, without writing most of the data access SQL code.

In this course, the database used is SQLite, which is lightweight and ideal for local development.

Key Concepts

Entities and DbContext

Entities are C# classes that map to database tables. They use Data Annotations to configure constraints.

City Entity

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace CityInfo.API.Entities;

public class City(string name)
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    [Required]
    [MaxLength(50)]
    public string Name { get; set; } = name;

    [MaxLength(200)]
    public string? Description { get; set; }

    public ICollection<PointOfInterest> PointsOfInterest { get; set; } = [];
}

PointOfInterest Entity

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace CityInfo.API.Entities;

public class PointOfInterest(string name)
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    [Required]
    [MaxLength(50)]
    public string Name { get; set; } = name;

    [MaxLength(200)]
    public string? Description { get; set; }

    [ForeignKey("CityId")]
    public City? City { get; set; }

    public int CityId { get; set; }
}

CityInfoContext — DbContext

using CityInfo.API.Entities;
using Microsoft.EntityFrameworkCore;

namespace CityInfo.API.DbContexts;

public class CityInfoContext(DbContextOptions<CityInfoContext> options) : DbContext(options)
{
    public DbSet<City> Cities { get; set; }
    public DbSet<PointOfInterest> PointsOfInterest { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Seed Data - see dedicated section
        base.OnModelCreating(modelBuilder);
    }
}

Note: The use of primary constructors (C# 12) is a new feature — CityInfoContext(DbContextOptions<CityInfoContext> options) injects options directly without needing to store them in a field.

EF Core Migrations

Migrations allow you to evolve the database schema incrementally and in a versioned manner.

Essential CLI Commands

# Add an initial migration
dotnet ef migrations add CityInfoDBInitialMigration

# Apply pending migrations to the database
dotnet ef database update

# Add a migration after a model change
dotnet ef migrations add CityInfoDBAddPOIDescription

# Remove the last migration (if not yet applied)
dotnet ef migrations remove

Migration File Structure

// 20260129091654_CityInfoDBInitialMigration.cs
public partial class CityInfoDBInitialMigration : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "Cities",
            columns: table => new
            {
                Id = table.Column<int>(nullable: false)
                    .Annotation("Sqlite:Autoincrement", true),
                Name = table.Column<string>(maxLength: 50, nullable: false),
                Description = table.Column<string>(maxLength: 200, nullable: true)
            },
            constraints: table => table.PrimaryKey("PK_Cities", x => x.Id));

        migrationBuilder.CreateTable(
            name: "PointsOfInterest",
            columns: table => new
            {
                Id = table.Column<int>(nullable: false)
                    .Annotation("Sqlite:Autoincrement", true),
                Name = table.Column<string>(maxLength: 50, nullable: false),
                Description = table.Column<string>(maxLength: 200, nullable: true),
                CityId = table.Column<int>(nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_PointsOfInterest", x => x.Id);
                table.ForeignKey(
                    name: "FK_PointsOfInterest_Cities_CityId",
                    column: x => x.CityId,
                    principalTable: "Cities",
                    principalColumn: "Id",
                    onDelete: ReferentialAction.Cascade);
            });
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropTable(name: "PointsOfInterest");
        migrationBuilder.DropTable(name: "Cities");
    }
}

Seed Data

Seed Data initializes the database with demo data inside OnModelCreating.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<City>().HasData(
        new City("New York City") { Id = 1, Description = "The one with that big park." },
        new City("Antwerp")      { Id = 2, Description = "The one with the cathedral that was never really finished." },
        new City("Paris")        { Id = 3, Description = "The one with that big tower." }
    );

    modelBuilder.Entity<PointOfInterest>().HasData(
        new PointOfInterest("Central Park")
            { Id = 1, CityId = 1, Description = "The most visited urban park in the United States." },
        new PointOfInterest("Empire State Building")
            { Id = 2, CityId = 1, Description = "A 102-story skyscraper located in Midtown Manhattan." },
        new PointOfInterest("Cathedral")
            { Id = 3, CityId = 2, Description = "A Gothic style cathedral." },
        new PointOfInterest("Antwerp Central Station")
            { Id = 4, CityId = 2, Description = "The finest example of railway architecture in Belgium." },
        new PointOfInterest("Eiffel Tower")
            { Id = 5, CityId = 3, Description = "A wrought iron lattice tower on the Champ de Mars." },
        new PointOfInterest("The Louvre")
            { Id = 6, CityId = 3, Description = "The world's largest museum." }
    );

    base.OnModelCreating(modelBuilder);
}

Repository Pattern

The Repository Pattern is an abstraction of the data access layer. It decouples business logic from the persistence mechanism.

ICityInfoRepository Interface

using CityInfo.API.Entities;
using CityInfo.API.Models;

namespace CityInfo.API.Services;

public interface ICityInfoRepository
{
    Task<IEnumerable<City>> GetCitiesAsync(CancellationToken cancellationToken);

    Task<IEnumerable<City>> GetCitiesReadOnlyAsync(CancellationToken cancellationToken);

    Task<(IEnumerable<City>, PaginationMetadata?)> GetCitiesReadOnlyAsync(
        string? name,
        string? searchQuery,
        int pageNumber,
        int pageSize,
        CancellationToken cancellationToken);

    Task<bool> CityExistsAsync(int cityId, CancellationToken cancellationToken);

    Task<City?> GetCityAsync(int cityId, bool includePointsOfInterest, CancellationToken cancellationToken);

    Task<IEnumerable<PointOfInterest>> GetPointsOfInterestForCityAsync(int cityId, CancellationToken cancellationToken);

    Task<PointOfInterest?> GetPointOfInterestForCityAsync(int cityId, int pointOfInterestId, CancellationToken cancellationToken);

    Task AddPointOfInterestForCityAsync(int cityId, PointOfInterest pointOfInterest, CancellationToken cancellationToken);

    void DeletePointOfInterest(PointOfInterest pointOfInterest);

    Task<int> UpdatePointsOfInterestDescriptionForCityAsync(int cityId, string updatedDescription, CancellationToken cancellationToken);

    Task<int> DeleteAllPointsOfInterestForCityAsync(int cityId, CancellationToken cancellationToken);

    Task<bool> SaveChangesAsync(CancellationToken cancellationToken);
}

Registration in Program.cs

// Register DbContext with SQLite
builder.Services.AddDbContext<CityInfoContext>(dbContextOptions =>
    dbContextOptions.UseSqlite(
        builder.Configuration["ConnectionStrings:CityInfoDBConnectionString"]
            ?? throw new InvalidOperationException()));

// Register repository (Scoped = one instance per HTTP request)
builder.Services.AddScoped<ICityInfoRepository, CityInfoRepository>();

// AutoMapper — automatic profile detection
builder.Services.AddAutoMapper(config => { },
    AppDomain.CurrentDomain.GetAssemblies());

Module 2 — Working with Data via the Repository

Creating a Resource (POST)

To create a resource via EF Core:

1. Map the incoming DTO to an entity
2. Add the entity to the DbSet via the repository
3. Call SaveChangesAsync to persist

[HttpPost]
public async Task<ActionResult<PointOfInterestDto>> CreatePointOfInterest(
    int cityId,
    PointOfInterestForCreationDto pointOfInterest,
    CancellationToken cancellationToken = default)
{
    var result = await pointOfInterestService.CreatePointOfInterestAsync(
        cityId, pointOfInterest, cancellationToken);

    if (!result.Success)
    {
        return BadRequest(new { error = result.ErrorMessage });
    }

    return CreatedAtRoute("GetPointOfInterest",
        new { cityId, pointOfInterestId = result.PointOfInterest!.Id },
        result.PointOfInterest);
}

AutoMapper — Mapping DTOs to entities:

// In PointOfInterestProfile
CreateMap<PointOfInterestForCreationDto, PointOfInterest>();
CreateMap<PointOfInterest, PointOfInterestDto>();

Updating a Resource (PUT)

PUT completely replaces the resource. AutoMapper can directly map the source to the destination, updating the values of the entity tracked by EF Core:

[HttpPut("{pointOfInterestId}")]
public async Task<ActionResult> UpdatePointOfInterest(int cityId, int pointOfInterestId,
    PointOfInterestForUpdateDto pointOfInterest,
    CancellationToken cancellationToken = default)
{
    if (!await cityInfoRepository.CityExistsAsync(cityId, cancellationToken))
        return NotFound();

    var pointOfInterestEntity = await cityInfoRepository
        .GetPointOfInterestForCityAsync(cityId, pointOfInterestId, cancellationToken);

    if (pointOfInterestEntity == null)
        return NotFound();

    // AutoMapper overwrites the tracked entity's values
    mapper.Map(pointOfInterest, pointOfInterestEntity);

    await cityInfoRepository.SaveChangesAsync(cancellationToken);

    return NoContent();
}

Tip: With mapper.Map(source, destination), AutoMapper overwrites the destination entity’s properties with those from the DTO source. Since the entity is already tracked by EF Core, calling SaveChangesAsync is sufficient to persist the changes.

Partial Update (PATCH)

PATCH uses a JsonPatchDocument to apply partial modifications.

[HttpPatch("{pointOfInterestId}")]
public async Task<ActionResult> PartiallyUpdatePointOfInterest(int cityId, int pointOfInterestId,
    JsonPatchDocument<PointOfInterestForUpdateDto> patchDocument,
    CancellationToken cancellationToken = default)
{
    var pointOfInterestEntity = await cityInfoRepository
        .GetPointOfInterestForCityAsync(cityId, pointOfInterestId, cancellationToken);

    if (pointOfInterestEntity == null) return NotFound();

    // 1. Map the entity to a DTO (the patch is applied on the DTO, not the entity)
    var pointOfInterestToPatch = mapper.Map<PointOfInterestForUpdateDto>(pointOfInterestEntity);

    // 2. Apply the patch document to the DTO
    patchDocument.ApplyTo(pointOfInterestToPatch, jsonPatchError =>
    {
        var key = jsonPatchError.AffectedObject.GetType().Name;
        ModelState.AddModelError(key, jsonPatchError.ErrorMessage);
    });

    if (!ModelState.IsValid || !TryValidateModel(pointOfInterestToPatch))
        return BadRequest(ModelState);

    // 3. Map the modified DTO back to the entity
    mapper.Map(pointOfInterestToPatch, pointOfInterestEntity);

    await cityInfoRepository.SaveChangesAsync(cancellationToken);

    return NoContent();
}

Example PATCH request:

[
  { "op": "replace", "path": "/description", "value": "Updated description" }
]

Deleting a Resource (DELETE)

[HttpDelete("{pointOfInterestId}")]
public async Task<ActionResult> DeletePointOfInterest(int cityId, int pointOfInterestId,
    CancellationToken cancellationToken = default)
{
    if (!await cityInfoRepository.CityExistsAsync(cityId, cancellationToken))
        return NotFound();

    var pointOfInterestEntity = await cityInfoRepository
        .GetPointOfInterestForCityAsync(cityId, pointOfInterestId, cancellationToken);

    if (pointOfInterestEntity == null) return NotFound();

    cityInfoRepository.DeletePointOfInterest(pointOfInterestEntity);
    await cityInfoRepository.SaveChangesAsync(cancellationToken);

    mailService.Send("Point of interest deleted.",
        $"Point of interest {pointOfInterestEntity.Name} with id {pointOfInterestEntity.Id} was deleted.");

    return NoContent();
}

Note: DeletePointOfInterest is a synchronous method because removing from the DbContext is an in-memory operation. It is SaveChangesAsync that generates the SQL DELETE query.

Bulk Operations: ExecuteUpdateAsync and ExecuteDeleteAsync

These methods allow executing updates/deletes directly at the database level, without loading entities into memory and without change tracking.

Traditional vs Bulk Approach

Repository Implementation

// Bulk update — a single SQL UPDATE
public async Task<int> UpdatePointsOfInterestDescriptionForCityAsync(
    int cityId,
    string updatedDescription,
    CancellationToken cancellationToken)
{
    return await context.PointsOfInterest
        .Where(p => p.CityId == cityId)
        .ExecuteUpdateAsync(setters => setters
            .SetProperty(p => p.Description, updatedDescription),
            cancellationToken);

    // Generated SQL:
    // UPDATE PointsOfInterest
    // SET Description = 'updatedDescription'
    // WHERE CityId = cityId
}

// Bulk delete — a single SQL DELETE
public async Task<int> DeleteAllPointsOfInterestForCityAsync(
    int cityId,
    CancellationToken cancellationToken)
{
    return await context.PointsOfInterest
        .Where(p => p.CityId == cityId)
        .ExecuteDeleteAsync(cancellationToken);

    // Generated SQL:
    // DELETE FROM PointsOfInterest
    // WHERE CityId = cityId
}

Key points:

• These methods bypass change tracking entirely
• They execute immediately (no need for SaveChangesAsync)
• Entities already loaded in context are not automatically updated
• Ideal for bulk operations or when you know exactly what changes to apply

Request Cancellation (CancellationToken)

A CancellationToken allows interrupting a long operation when the API consumer navigates away or abandons the request.

How It Works

HTTP Client ──── request ────► Controller action
                                    │
                    CancellationToken propagated
                                    │
                              Repository method
                                    │
                           EF Core query with token
                                    │
                              Database

Implementation

// Controller — ASP.NET Core framework injects CancellationToken automatically
[HttpGet]
public async Task<ActionResult<IEnumerable<CityWithoutPointsOfInterestDto>>> GetCities(
    string? name,
    string? searchQuery,
    int pageNumber = 1,
    int pageSize = 10,
    CancellationToken cancellationToken = default)  // default = CancellationToken.None if not injected
{
    var (cityEntities, paginationMetadata) = await cityInfoRepository
        .GetCitiesReadOnlyAsync(name, searchQuery, pageNumber, pageSize, cancellationToken);
    // ...
}

// Repository — passing the token to EF Core methods
public async Task<IEnumerable<City>> GetCitiesReadOnlyAsync(CancellationToken cancellationToken)
{
    return await context.Cities
        .AsNoTracking()
        .OrderBy(c => c.Name)
        .ToListAsync(cancellationToken); // EF Core monitors the token
}

Design decision: Inside the code (repository), the CancellationToken is made mandatory to enforce its systematic use and avoid unnecessary long-running tasks. On the public surface (controller action), it remains optional with a default value.

Module 3 — Manipulating Data via the Repository

This module consolidates complete CRUD operations. The CityInfo.API project structure follows this organization:

CityInfo.API/
├── Controllers/
│   ├── CitiesController.cs
│   ├── PointsOfInterestController.cs
│   └── FilesController.cs
├── DbContexts/
│   └── CityInfoContext.cs
├── Entities/
│   ├── City.cs
│   └── PointOfInterest.cs
├── Migrations/
│   ├── 20260129091654_CityInfoDBInitialMigration.cs
│   ├── 20260129092939_CityInfoDBAddPOIDescription.cs
│   └── 20260129093636_SeedData.cs
├── Models/
│   ├── CityDto.cs
│   ├── CityWithoutPointsOfInterestDto.cs
│   ├── PaginationMetadata.cs
│   ├── PointOfInterestDto.cs
│   ├── PointOfInterestForCreationDto.cs
│   ├── PointOfInterestForUpdateDto.cs
│   ├── PointOfInteresCreationResult.cs
│   └── PointsOfInterestBulkUpdateDto.cs
├── Services/
│   ├── ICityInfoRepository.cs
│   ├── CityInfoRepository.cs
│   ├── IPointOfInterestService.cs
│   ├── PointOfInterestService.cs
│   ├── IMailService.cs
│   ├── LocalMailService.cs
│   └── CloudMailService.cs
├── appsettings.json
└── Program.cs

Module 4 — Searching and Filtering Resources

Filtering vs Searching

These two concepts are often confused but work differently:

Implementing Filtering

// CitiesController.cs
[HttpGet]
public async Task<ActionResult<IEnumerable<CityWithoutPointsOfInterestDto>>> GetCities(
    string? name,          // Filter parameter — null = no filter
    string? searchQuery,   // Search parameter — null = no search
    int pageNumber = 1,
    int pageSize = 10,
    CancellationToken cancellationToken = default)
{
    // ...
}

// CityInfoRepository.cs — Implementation with IQueryable (deferred execution)
public async Task<(IEnumerable<City>, PaginationMetadata?)> GetCitiesReadOnlyAsync(
    string? name,
    string? searchQuery,
    int pageNumber,
    int pageSize,
    CancellationToken cancellationToken)
{
    // Starting point: IQueryable<City> (not yet executed)
    var collection = context.Cities as IQueryable<City>;

    // Apply filter on name (exact match)
    if (!string.IsNullOrWhiteSpace(name))
    {
        name = name.Trim();
        collection = collection.Where(c => c.Name == name);
    }

    // Apply full-text search
    if (!string.IsNullOrWhiteSpace(searchQuery))
    {
        searchQuery = searchQuery.Trim();
        collection = collection.Where(c =>
            c.Name.Contains(searchQuery) ||
            (c.Description != null && c.Description.Contains(searchQuery)));
    }

    // At this point, no SQL query has been sent to the database yet
    // The query is built as a LINQ expression tree

    var totalItemCount = await collection.CountAsync(cancellationToken);
    // ↑ HERE the first SQL query is sent: SELECT COUNT(*)...

    var paginationMetadata = new PaginationMetadata(totalItemCount, pageSize, pageNumber);

    var collectionToReturn = await collection
        .AsNoTracking()
        .OrderBy(c => c.Name)
        .Skip(pageSize * (pageNumber - 1))
        .Take(pageSize)
        .ToListAsync(cancellationToken);
    // ↑ HERE the second SQL query is sent with OFFSET/FETCH

    return (collectionToReturn, paginationMetadata);
}

Implementing Searching

Searching uses the same method as filtering but with Contains on multiple fields:

// Search in Name AND Description
collection = collection.Where(c =>
    c.Name.Contains(searchQuery) ||
    (c.Description != null && c.Description.Contains(searchQuery)));

Generated SQL (approximate):

SELECT * FROM Cities
WHERE (Name LIKE '%Tower%' OR Description LIKE '%Tower%')
ORDER BY Name
LIMIT 10 OFFSET 0

Note: For more complex scenarios, you can use full-text search components such as Lucene or the full-text capabilities of the target database.

Deferred Execution

Deferred execution is a fundamental concept for building efficient queries with LINQ and EF Core.

IQueryable<City> query = context.Cities;       // ← No SQL sent
query = query.Where(c => c.Name == "London");  // ← No SQL sent (expression tree)
query = query.OrderBy(c => c.Name);            // ← No SQL sent
var list = await query.ToListAsync();          // ← SQL sent HERE

When Is Execution Triggered?

Benefit: By combining filtering, searching, AND pagination in the same IQueryable chain, a single optimized SQL query is sent to the database rather than several separate queries.

Module 5 — Paging Resources

Pagination Concepts

Pagination is an essential best practice for resource collections:

• Without pagination, returning thousands of records in a single response negatively impacts performance
• Pagination must go all the way to the database (not just at the controller level)
• If you first return all data and then page at the controller level, you have still loaded too much data into memory

Typical Parameters

Implementing Pagination

Controller

[ApiController]
[Route("api/cities")]
public class CitiesController(ICityInfoRepository cityInfoRepository, IMapper mapper) : ControllerBase
{
    const int _maxCitiesPageSize = 20; // Maximum allowed per page

    [HttpGet]
    public async Task<ActionResult<IEnumerable<CityWithoutPointsOfInterestDto>>> GetCities(
        string? name,
        string? searchQuery,
        int pageNumber = 1,
        int pageSize = 10,
        CancellationToken cancellationToken = default)
    {
        // Cap page size to the allowed maximum
        if (pageSize > _maxCitiesPageSize)
        {
            pageSize = _maxCitiesPageSize;
        }

        var (cityEntities, paginationMetadata) = await cityInfoRepository.GetCitiesReadOnlyAsync(
            name, searchQuery, pageNumber, pageSize, cancellationToken);

        if (paginationMetadata != null)
        {
            Response.Headers.Append("X-Pagination",
                JsonSerializer.Serialize(paginationMetadata));
        }

        return Ok(mapper.Map<IEnumerable<CityWithoutPointsOfInterestDto>>(cityEntities));
    }
}

Repository — LINQ Implementation with Skip/Take

var collectionToReturn = await collection
    .AsNoTracking()
    .OrderBy(c => c.Name)
    .Skip(pageSize * (pageNumber - 1))  // Skip previous pages
    .Take(pageSize)                      // Take only the current page
    .ToListAsync(cancellationToken);

Generated SQL:

SELECT * FROM Cities
ORDER BY Name
LIMIT 10 OFFSET 0       -- Page 1
-- LIMIT 10 OFFSET 10   -- Page 2
-- LIMIT 10 OFFSET 20   -- Page 3

Pagination Metadata

PaginationMetadata Model

namespace CityInfo.API.Models;

public class PaginationMetadata(int totalItemCount, int pageSize, int currentPage)
{
    public int TotalItemCount  { get; set; } = totalItemCount;

    // Automatic calculation of total page count
    public int TotalPageCount  { get; set; } = (int)Math.Ceiling(totalItemCount / (double)pageSize);

    public int PageSize        { get; set; } = pageSize;
    public int CurrentPage     { get; set; } = currentPage;
}

Where to Place the Metadata?

Pagination metadata is not part of the resource representation itself. It should be returned in a custom HTTP header rather than in the body.

GET /api/cities?pageNumber=2&pageSize=10

HTTP/1.1 200 OK
Content-Type: application/json
X-Pagination: {"TotalItemCount":50,"TotalPageCount":5,"PageSize":10,"CurrentPage":2}

[
  { "id": 1, "name": "Antwerp", ... },
  ...
]

Returning the header in the controller:

Response.Headers.Append("X-Pagination",
    JsonSerializer.Serialize(paginationMetadata));

Comparison of Approaches

Module 6 — Service Layer

Understanding the Service Layer

The service layer (or business logic layer) sits between controllers and repositories. It is useful when business logic goes beyond simple CRUD operations.

Architecture With and Without Service Layer

Without Service Layer:
Controller ──────────────────► Repository ──► Database

With Service Layer:
Controller ──► Service Layer ──► Repository ──► Database

When to Add a Service Layer?

Use Cases in Code

Example without Service Layer (acceptable for simple CRUD):

// GetCities — no complex business logic, no need for service layer
public async Task<ActionResult<IEnumerable<CityWithoutPointsOfInterestDto>>> GetCities(...)
{
    var (cityEntities, paginationMetadata) = await cityInfoRepository
        .GetCitiesReadOnlyAsync(name, searchQuery, pageNumber, pageSize, cancellationToken);
    // Simple delegation to repository
    return Ok(mapper.Map<IEnumerable<CityWithoutPointsOfInterestDto>>(cityEntities));
}

Example with Service Layer (justified by business rules):

Business rules for CreatePointOfInterest:

1. A city cannot have more than 10 points of interest
2. Creation must send a notification to city subscribers

Implementing a Service Layer

IPointOfInterestService Interface

namespace CityInfo.API.Services;

public interface IPointOfInterestService
{
    Task<PointOfInterestCreationResult> CreatePointOfInterestAsync(
        int cityId,
        PointOfInterestForCreationDto pointOfInterest,
        CancellationToken cancellationToken);
}

Result Class PointOfInterestCreationResult

namespace CityInfo.API.Models;

public class PointOfInterestCreationResult
{
    public bool Success { get; set; }
    public string? ErrorMessage { get; set; }
    public PointOfInterestDto? PointOfInterest { get; set; }

    // Factory pattern — static methods to create instances
    public static PointOfInterestCreationResult Successful(PointOfInterestDto poi) =>
        new() { Success = true, PointOfInterest = poi };

    public static PointOfInterestCreationResult Failed(string error) =>
        new() { Success = false, ErrorMessage = error };
}

PointOfInterestService Implementation

using AutoMapper;
using CityInfo.API.Models;

namespace CityInfo.API.Services;

public class PointOfInterestService(
    ICityInfoRepository repository,
    IMapper mapper,
    IMailService mailService) : IPointOfInterestService
{
    public async Task<PointOfInterestCreationResult> CreatePointOfInterestAsync(
        int cityId,
        PointOfInterestForCreationDto pointOfInterest,
        CancellationToken cancellationToken)
    {
        // Rule 1: City must exist
        if (!await repository.CityExistsAsync(cityId, cancellationToken))
        {
            return PointOfInterestCreationResult.Failed("City not found");
        }

        // Rule 2: Maximum 10 points of interest per city
        var existingPOIs = await repository
            .GetPointsOfInterestForCityAsync(cityId, cancellationToken);

        if (existingPOIs.Count() >= 10)
        {
            return PointOfInterestCreationResult.Failed(
                "City has reached maximum capacity of 10 points of interest");
        }

        // Map DTO → Entity
        var pointOfInterestEntity = mapper.Map<Entities.PointOfInterest>(pointOfInterest);

        // Persist
        await repository.AddPointOfInterestForCityAsync(cityId, pointOfInterestEntity, cancellationToken);
        await repository.SaveChangesAsync(cancellationToken);

        // Rule 3: Send a notification
        await SendCreationNotificationsAsync(cityId, pointOfInterestEntity);

        var resultDto = mapper.Map<PointOfInterestDto>(pointOfInterestEntity);
        return PointOfInterestCreationResult.Successful(resultDto);
    }

    private async Task SendCreationNotificationsAsync(
        int cityId,
        Entities.PointOfInterest pointOfInterest)
    {
        var subject = "New Point of Interest Added";
        var message = $"A new point of interest '{pointOfInterest.Name}' " +
                      $"has been added to city {cityId}.";
        mailService.Send(subject, message);
    }
}

Registration in Program.cs

// Service Layer — Scoped (one instance per HTTP request)
builder.Services.AddScoped<IPointOfInterestService, PointOfInterestService>();

Complete Program.cs

using CityInfo.API;
using CityInfo.API.DbContexts;
using CityInfo.API.Services;
using Microsoft.AspNetCore.StaticFiles;
using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);
builder.Logging.ClearProviders();
builder.Logging.AddConsole();

builder.Services.AddControllers()
    .AddXmlDataContractSerializerFormatters();

builder.Services.AddOpenApi();

builder.Services.AddProblemDetails(options =>
    options.CustomizeProblemDetails = ctx =>
    {
        ctx.ProblemDetails.Extensions.Add("additionalInfo", "Additional info example");
        ctx.ProblemDetails.Extensions.Add("server", Environment.MachineName);
    });

builder.Services.AddSingleton<FileExtensionContentTypeProvider>();

#if DEBUG
builder.Services.AddTransient<IMailService, LocalMailService>();
#else
builder.Services.AddTransient<IMailService, CloudMailService>();
#endif

builder.Services.AddDbContext<CityInfoContext>(dbContextOptions =>
    dbContextOptions.UseSqlite(
        builder.Configuration["ConnectionStrings:CityInfoDBConnectionString"]
            ?? throw new InvalidOperationException()));

builder.Services.AddScoped<ICityInfoRepository, CityInfoRepository>();
builder.Services.AddScoped<IPointOfInterestService, PointOfInterestService>();

builder.Services.AddAutoMapper(config => { },
    AppDomain.CurrentDomain.GetAssemblies());

var app = builder.Build();

if (app.Environment.IsDevelopment())
{
    app.MapOpenApi();
}

app.UseHttpsRedirection();
app.UseAuthorization();
app.MapControllers();

app.Run();

Overall Architecture

graph TB
    Client["HTTP Client\n(Postman / Browser / App)"]

    subgraph "ASP.NET Core 10 Web API"
        direction TB
        Controller["Controllers\nCitiesController\nPointsOfInterestController"]
        ServiceLayer["Service Layer\nIPointOfInterestService\nPointOfInterestService"]
        Repository["Repository\nICityInfoRepository\nCityInfoRepository"]
        AutoMap["AutoMapper\nEntity ↔ DTO"]
        DbCtx["DbContext\nCityInfoContext"]
    end

    subgraph "Persistence"
        DB["SQLite Database\nCityInfo.db"]
    end

    Client -->|"HTTP Request"| Controller
    Controller -->|"Business logic"| ServiceLayer
    Controller -->|"Data access"| Repository
    ServiceLayer -->|"Data access"| Repository
    Repository -->|"LINQ queries"| DbCtx
    DbCtx -->|"SQL"| DB
    AutoMap -.->|"Mapping"| Controller
    AutoMap -.->|"Mapping"| ServiceLayer
    DB -->|"Results"| DbCtx
    DbCtx -->|"Entities"| Repository
    Repository -->|"Entities"| Controller
    Controller -->|"HTTP Response (DTO)"| Client

Repository Pattern Flow

sequenceDiagram
    participant C as Controller
    participant S as Service Layer
    participant R as Repository
    participant D as DbContext
    participant DB as SQLite DB

    C->>S: CreatePointOfInterestAsync(cityId, dto, token)
    S->>R: CityExistsAsync(cityId, token)
    R->>D: context.Cities.AnyAsync(...)
    D->>DB: SELECT COUNT(*) FROM Cities WHERE Id = ?
    DB-->>D: 1
    D-->>R: true
    R-->>S: true

    S->>R: GetPointsOfInterestForCityAsync(cityId, token)
    R->>D: context.PointsOfInterest.Where(...).ToListAsync()
    D->>DB: SELECT * FROM PointsOfInterest WHERE CityId = ?
    DB-->>D: [poi1, poi2, ...]
    D-->>R: List<PointOfInterest>
    R-->>S: IEnumerable<PointOfInterest>

    S->>R: AddPointOfInterestForCityAsync(cityId, entity, token)
    Note over R,D: In-memory operation (change tracking)

    S->>R: SaveChangesAsync(token)
    R->>D: context.SaveChangesAsync()
    D->>DB: INSERT INTO PointsOfInterest (...)
    DB-->>D: 1 row affected
    D-->>R: 1
    R-->>S: true

    S-->>C: PointOfInterestCreationResult.Successful(dto)
    C-->>C: return CreatedAtRoute(...)

Search / Filter / Pagination Pipeline

flowchart TD
    A["HTTP Request\nGET /api/cities?name=London&searchQuery=Tower&pageNumber=2&pageSize=10"]

    B["Controller\nGetCities()"]

    C{"pageSize > maxPageSize?"}
    D["pageSize = maxPageSize"]
    E["Repository Call\nGetCitiesReadOnlyAsync()"]

    F["IQueryable<City>\ncontext.Cities"]

    G{"name provided?"}
    H["collection.Where(c => c.Name == name)"]

    I{"searchQuery provided?"}
    J["collection.Where(c => c.Name.Contains(q)\n|| c.Description.Contains(q))"]

    K["COUNT(*) SQL\ntotalItemCount"]
    L["PaginationMetadata\ncompute TotalPageCount"]

    M["collection\n.OrderBy(c => c.Name)\n.Skip(pageSize * (pageNumber-1))\n.Take(pageSize)\n.ToListAsync()"]

    N["Map Entities → DTOs\n(AutoMapper)"]

    O["X-Pagination Header\n+ Response Body"]

    A --> B --> C
    C -->|Yes| D --> E
    C -->|No| E
    E --> F --> G
    G -->|Yes| H --> I
    G -->|No| I
    I -->|Yes| J --> K
    I -->|No| K
    K --> L --> M --> N --> O

Reference Tables

EF Core Methods and Their Impact

Dependency Injection Lifetimes

AutoMapper — Mapping Patterns

HTTP Return Codes for CRUD Actions

Query String Parameters

Summary of best practices from this course:

1. Always paginate resource collections, even small ones, to anticipate growth
2. Propagate the CancellationToken to EF Core calls to release resources quickly
3. Use AsNoTracking() for read-only operations to improve performance
4. Leverage deferred execution (IQueryable) to compose complex queries into a single SQL query
5. Return pagination metadata in a header (X-Pagination) rather than in the body
6. Use ExecuteUpdateAsync/ExecuteDeleteAsync for bulk operations
7. Introduce a Service Layer only when business logic justifies the added complexity
8. Use the Factory Pattern on result classes for a clear and expressive internal API

Search Terms

asp.net · core · web · api · databases · searching · filtering · paging · apis · c# · .net · development · repository · layer · service · pagination · data · entity · program.cs · resource · architecture · bulk · concepts · dbcontext