Optimizing and Configuring City Data with ASP.NET Core

Table of Contents

1. Challenge

   Step 1: Orientation to the CityDataApi optimization project

   Welcome to CityDataApi

   CityDataApi is an ASP.NET Core Web API that a tourism board uses to manage cities and the points of interest (museums, parks, restaurants) inside them. The starter solution compiles and can technically return a city or two, but it was clearly built to prove a concept, not to survive production traffic. Over the next four steps you will harden it one concern at a time: persistence and seeding, efficient reads, correct writes, and searchable/paged listings backed by a proper service layer.

   Open src/ now and look at Models/City.cs, Models/PointOfInterest.cs, and Dtos/CityDto.cs / Dtos/PointOfInterestDto.cs. The entities carry EF Core navigation properties; the DTOs are the flat shapes the API should actually expose. Keeping those separate is what lets you change the database schema later without breaking every client of the API. ## The five weaknesses you're fixing

   1. Configuration — CityContext has no connection string wiring worth the name, and the model has no seed data, so every fresh database starts empty.
   2. Reads — the repository methods that list cities and points of interest return fully tracked EF Core entities, which wastes memory and CPU on data nobody is going to modify.
   3. Writes — create, update, and delete for points of interest are either missing or naive: no cancellation support, no partial updates, and no efficient way to remove many rows at once.
   4. Listing — there's no way to filter by category, search by text, or page a large city's points of interest; every request returns the entire table.
   5. Business rules — validation like "no duplicate names" and "no more than N points of interest per city" is written inline inside a controller action, which means it can't be unit tested or reused.

   Each of the next four steps closes one of these gaps, in the order a real team would tackle them: you can't optimize reads on a database that has no data, and you can't build a service layer around writes that don't exist yet. ## Where the code lives

   • src/Data/CityContext.cs — the primary DbContext, its entity configuration, and seed data.
   • src/Data/CityPointsContext.cs — a secondary context and its fixed-id seed data for design-time and migration scenarios.
   • src/Data/ConnectionStringProvider.cs — resolves the connection string from configuration.
   • src/Repositories/CityRepository.cs and src/Repositories/PointOfInterestRepository.cs — all database access: reads, writes, bulk operations, filtering, and paging.
   • src/Services/PointOfInterestCreationService.cs and src/Services/PointOfInterestRequestHandler.cs — the business rules and the workflow that composes them with the repository.

   Each task you complete lives in one of these files, and the related tasks build on one another — by Step 5 you'll be calling methods you wrote in Step 3 from code you write in Step 5. ## How you'll work

   Each task gives you a // TODO: Task X.Y comment inside a method stub, with a // Hint: line naming the technique to use without spelling out the exact code. Fill in the method body so the described behavior holds, then move to the next task — later steps assume earlier ones are done. There's no task in this first step; use it to skim the files listed above so the vocabulary (City, PointOfInterest, CityDto, PointOfInterestDto) is familiar before you start editing in Step 2.

   If you get stuck, you can refer to the provided solution code for each task, available in the solution folder.

2. Challenge

   Step 2: Configure EF Core entities, seed data, and connection strings

   Configuring the DbContext

   CityContext is where EF Core learns about your entities. Two things are missing: the model has no seed rows, and nothing tells EF Core how a City relates to its PointOfInterest records beyond the raw foreign key. OnModelCreating is the right place for both — it's the one method EF Core always calls while building the model, before any query runs. HasData lets you seed fixed rows with explicit primary keys, which is exactly what a fresh database needs on first run. ## Getting the connection string out of source code

   A hardcoded connection string is a credential sitting in version control. ASP.NET Core's configuration system — appsettings.json, environment variables, user secrets — is designed so the same code can point at a local database in development and a managed one in production without a single code change. IConfiguration.GetConnectionString reads the conventional ConnectionStrings section by name; the only work left is deciding what happens when the setting isn't there. ## Seeding a migration-ready context

   CityPointsContext is a separate context used for design-time and migration scenarios. Its seed data also needs explicit primary keys, and every point of interest needs a CityId that refers to one of the seeded cities. Seed at least two points of interest per city so each city has starter data.

3. Challenge

   Step 3: Optimize asynchronous repository reads with no-tracking DTO projections

   Reading without tracking

   By default, every entity EF Core loads is added to the context's change tracker so it can detect edits later. For a list endpoint that only ever reads data, that tracking is pure overhead — memory spent snapshotting rows nobody will modify. AsNoTracking() turns it off, and projecting with Select into a DTO goes a step further: EF Core generates SQL that only selects the columns the DTO actually needs, instead of every column on the entity. ## Scoping reads to one city

   The same no-tracking, DTO-projection pattern applies to points of interest, but with one more ingredient: the query must be scoped to a single city before anything else happens, so the database — not your application code — does the filtering. Filtering early also means the AsNoTracking/Select pattern you just used composes cleanly with a Where clause placed in front of it.

4. Challenge

   Step 4: Handle create, update, patch, and delete workflows with cancellation and bulk operations

   Writing with cancellation in mind

   Reads aren't the only place a CancellationToken matters — any asynchronous EF Core call, including AddAsync and SaveChangesAsync, accepts one, and passing it through means a client that disconnects mid-request doesn't leave the server finishing work nobody will see. Creating an entity is the simplest write to get right: stage it with AddAsync, commit with SaveChangesAsync, then read the key EF Core generated back off the tracked entity. ## Updating only what changed

   A PATCH request should change only the fields the client sent, leaving everything else exactly as it was. That means loading a tracked entity — the opposite of the no-tracking reads you just built — so EF Core can compute a diff when you call SaveChangesAsync. The trick is copying over only the properties that are actually present on the incoming patch, and reporting whether the target even existed to update. ## Deleting many rows without loading any

   Deleting a whole category of points of interest the naive way means loading every matching entity, calling Remove on each, then saving — three round trips of work for what the database can do in one. EF Core's ExecuteDeleteAsync translates a filtered IQueryable directly into a single SQL DELETE, never materializing the rows it removes, and returns the number of rows affected so the caller can report exactly how many were deleted.

5. Challenge

   Step 5: Compose filtering, searching, paging, and a testable creation service

   Filtering and searching in the database

   A listing endpoint that loads every row and filters in memory throws away everything EF Core is good at. Conditional Where clauses — added only when a category or searchTerm parameter is actually supplied — let the database narrow the result set before a single row crosses the wire, and they compose cleanly with the AsNoTracking/Select projection you've already built for reads. ## Paging with honest metadata

   Once a query can return an unbounded number of rows, a client needs both a bounded page and enough metadata to know there's more. Skip/Take bound the page, but the total count has to come from the filtered query before paging is applied — otherwise "total results" would only ever equal the page size, which tells the caller nothing useful about how many pages exist. ## Moving business rules out of the controller

   Rules like "no duplicate names in a city" and "no more than N points of interest per city" are business logic, not plumbing — they deserve a class you can unit test directly, without spinning up HTTP or a controller. PointOfInterestCreationService takes the new POI plus the city's existing ones and returns a result describing success or exactly why creation was refused, so nothing about why a request failed gets lost. ## Composing validation with persistence

   A service that only validates is half a workflow. PointOfInterestRequestHandler is where validation and persistence meet: load the current state a rule needs, run the rule, and only touch the database if the rule passed. Short-circuiting on failure — never calling the repository's create method unless validation succeeded — is what keeps an invalid point of interest from ever reaching storage.

6. Challenge

   Step 6: Run and exercise the finished API

   Start the API

   Every task is graded, but you haven't yet seen the pieces work together. CityDataApi is a web API with no pages to browse, so you'll call it from the Terminal with curl.

   Supply the connection string your Task 2.2 code reads, then start the API in the background with its log written to server.log:

   export ConnectionStrings__CityDataApi="Data Source=citydata.db"
   dotnet run --project src/Lab.csproj > server.log 2>&1 &
   curl -s --retry 60 --retry-connrefused --retry-delay 2 localhost:5000 -w '\n'
   

   When curl prints CityDataApi is running, the API is listening on port 5000 against a fresh database seeded from your HasData rows. If nothing prints within a minute, run cat server.log to see why. ## Call the API

   List the seeded points of interest for a city, returned as PointOfInterestDto objects by your no-tracking projection:

   curl -s localhost:5000/api/cities/1/pointsofinterest -w '\n'
   

   Create a new one. The request goes through your validation service before the repository saves it, so you get back HTTP 201 and the new id:

   curl -s -X POST localhost:5000/api/cities/1/pointsofinterest 
     -H "Content-Type: application/json" 
     -d '{"name":"Riverside Cafe","description":"Coffee by the river","category":"Food"}' 
     -w '\nHTTP %{http_code}\n'
   

   Run the same command again and the duplicate-name rule refuses it with HTTP 400 and a failureReason. To see the SQL EF Core generated for these requests, run tail -n 15 server.log.

   When you're finished, stop the API:

   kill %1
   ``` Two things worth trying before you finish: break something on purpose and watch the
   matching check fail, then put it back. Knowing what a failure looks like is worth as
   much as knowing what a pass looks like, and it costs you nothing here.
   
   When you are done, everything you wrote is still in the editor tabs -- the lab is
   yours to keep reading.