C# Relational Data Access

Table of Contents

1. Challenge

   Application Details

   You're going to be working with an application that lends tools instead of books. Members borrow Tools, and every borrow becomes a Loan with a due date and, eventually, a return date. Volunteer staff live inside three questions all day: which members currently have an overdue tool, what has this member borrowed before, and which tools are free to lend right now. Your job across this lab is to build the C# data access layer that answers all three — reliably, and fast enough that staff never notice the database underneath.

   You'll work against a real local SQLite database file, not an in-memory stand-in. That choice matters: every schema change, every query, and every CRUD operation you write has to survive an actual file on disk, the same way it would in production. ## The shape of the project

   Three entities carry the whole domain: Member, Tool, and Loan. A Loan links a Member to a Tool with a borrow date, a due date, and a return date that starts out empty. All the source you'll edit lives under src/; a companion tests/ directory, hidden from the file tree, is what grades your work, and you won't touch it.

   You'll reach for two different tools depending on the job. EF Core gives you a DbContext, LINQ, and change tracking for the everyday reads and writes — issuing a loan, recording a return, browsing a member's history. Dapper gives you a thin, parameterized-SQL layer for the one query staff hit constantly: the overdue-loans report. Knowing which one to reach for, and why, is as much the point of this lab as the syntax of either. ## Where the next four steps take you

   This step is orientation only; Steps 2 through 5 are where you write code. Step 2 opens a real connection to a SQLite file and maps Member, Tool, and Loan with EF Core's Fluent API. Step 3 puts that mapping to work: LINQ queries across the relationships, asynchronous create/update/delete operations, and a column default so inserts that leave a value out still succeed. Step 4 pulls back the curtain on the SQL EF Core generates and replaces the hottest read path with hand-written, parameterized Dapper. Step 5 wraps the whole thing behind a repository interface, proves a consumer can be tested without a database at all, and tightens up a query that was fetching more than anyone asked for. Step 6 then runs the finished program end to end.

   Each step leaves you with something that runs and returns a concrete answer. dotnet run --project src/Lab.csproj runs every part of the data layer against a real toollending.db file and prints, task by task, the table names SQLite created, a count of the seeded rows, the overdue-loans report, a member's loan history, the tools free to lend, and the SQL EF Core generates — so you always know the data layer is doing what you think it's doing. You can run it from the start: a section whose task you haven't finished yet prints not implemented yet — complete Task N.M instead of a result.

2. Challenge

   Local SQLite Database

   Why SQLite for this data layer

   The data is relational by nature: every Loan points at one Member and one Tool, and every question staff ask is a join or a filter across those links. A document store or a JSON file would push those joins into C# and give up the foreign keys that stop a loan from pointing at a member who no longer exists, so a relational database is the right kind of storage.

   Among relational databases, SQLite fits a local data layer best. The whole database is one file (toollending.db), with no server to install, start, or sign in to, and the tests run the same engine against an in-memory database. Choosing it costs nothing in portability: EF Core talks to SQLite through the same provider model it uses for SQL Server and PostgreSQL, so moving to a server database later means swapping UseSqlite(...) for UseSqlServer(...) or UseNpgsql(...) and changing the connection string, not rewriting the mapping or the LINQ. The tradeoff is concurrency — SQLite allows one writer at a time, which suits one library's volunteer staff and is the point at which you'd move to a server database. ## From nothing to a schema on disk

   ToolLendingContext in src/ToolLendingContext.cs is where the connection to SQLite lives. Before you can map anything, you need proof the context is actually opening the database file and creating tables inside it — not silently no-op-ing against a connection string that goes nowhere. EF Core's Database.EnsureCreated() does the creation; SQLite's own sqlite_master catalog table is how you check its work from the outside, the same way a DBA would. ## Telling EF Core how the tables connect

   A Loan row has two foreign keys pointing at two different tables — one at Member, one at Tool. EF Core's naming conventions can resolve simple cases, but two FKs on the same entity is exactly the kind of ambiguity that needs the Fluent API spelled out in OnModelCreating: which navigation property maps to which foreign key, and what happens to a Loan if the Member or Tool it points at is deleted. Getting the delete behavior right now saves you from a confusing runtime failure later, when Step 3 asks you to actually delete a member. ## See it run

   Open the Terminal tab and run:

   dotnet run --project src/Lab.csproj
   

   After Task 2.1, the [Task 2.1] section lists the tables SQLite created — Loans, Members, Tools — and the next line reports the seeded rows: 5 members, 6 tools, 5 loans. After Task 2.2, the [Task 2.2] section shows both Loan foreign keys with on delete Restrict instead of EF Core's default Cascade. Sections for later tasks print not implemented yet until you reach them.

3. Challenge

   Query Related Data

   Asking the model real questions

   With the mapping in place, context.Loans is a queryable set that already knows how to join back to Member and Tool. The overdue-loans report — the one staff check constantly — is just a filter over Loan (unreturned, past its due date) followed by a projection that pulls in the names from the related rows. This is the query in src/LoanQueries.cs you'll finish first, and it's also the query Step 4 will inspect and eventually replace. ## Writing, not just reading

   A loan's lifecycle is three async operations: issuing it, recording its return, and — much less often — removing a member entirely. src/LoanOperations.cs holds all three.

   Removing a member has a rule you set up in Step 2. DeleteBehavior.Restrict protects every Loan row, returned or not: a Loan is the library's record of who borrowed what, so the database refuses to delete any Member or Tool that a loan still points at. A member with no loans can be deleted; a member with an open loan, or with only returned loans, can't. Left to the database, that refusal arrives as a raw SQLite FOREIGN KEY constraint failed error wrapped in a DbUpdateException, which is a poor way for an application to fail. RemoveMemberAsync should check for any loan by the member first and throw a clear InvalidOperationException instead of letting the database do the talking. ## Giving a new column a safe default

   Staff now want to track each tool's condition — "Good", "Worn", "Needs repair". Tool already has a Condition property, but not every write goes through EF Core: an import script, a hand-written Dapper insert, or code written before the column existed can add a Tools row without naming Condition at all. Right now the column is NOT NULL with no default, so SQLite rejects those inserts outright. HasDefaultValue on the Fluent API property adds DEFAULT 'Good' to the column definition, so SQLite fills the value in for any insert that leaves it out.

   This lab creates its schema with EnsureCreated(), so the default takes effect the next time the schema is created; it doesn't alter the columns of a database file that already exists. ## Check your work

   Run dotnet run --project src/Lab.csproj again. After Task 3.1, [Task 3.1] lists three overdue loans: two for Ada Lovelace and one for Grace Hopper. After Task 3.2, [Task 3.2] issues Rosalind Franklin a loan of the Extension Ladder and records its return, then shows RemoveMemberAsync refusing to remove Grace Hopper (she has an open loan) and Rosalind Franklin (her only loan is returned, but its record is kept) and removing Katherine Johnson (she has never borrowed anything). The tools free to lend right now follow. After Task 3.3, [Task 3.3] inserts a Wheelbarrow with raw SQL that leaves out Condition, and it loads with Condition = "Good"; before Task 3.3, that insert fails with NOT NULL constraint failed: Tools.Condition.

4. Challenge

   Inspect Generated SQL

   Seeing the SQL before you replace it

   Before rewriting anything for speed, look at what EF Core is actually sending to SQLite. IQueryable<T>.ToQueryString() translates a LINQ query into the exact SQL text EF Core would run, without running it — call it too early and you get nothing to translate, call it after a ToList() and there's nothing left to ask. src/OverdueLoanSql.cs is where you'll surface that SQL for the overdue-loans query, so its cost is visible instead of assumed. ## Hand-writing the hot path

   Once you can see the SQL EF Core generates, you can decide whether it's worth keeping. For a report staff hit constantly, a hand-written, parameterized query through Dapper skips the translation layer entirely and gives you full control over the exact SQL that runs. src/DapperLoanQueries.cs is where that query lives — and parameterization isn't optional here: @asOf and @name have to be bound through Dapper's parameter object, never built into the SQL string, or the query becomes exactly the kind of hole SQL injection exploits. ## A second query, the same discipline

   Staff also want to look up a specific member's loan history by name — and a name is exactly the kind of caller-supplied value that has to be treated as data, never as part of the query text. The same connection.QueryAsync<T>(sql, parameters) pattern from the overdue report applies here, just with a different bound value. ## Check your work

   Run dotnet run --project src/Lab.csproj again. After Task 4.1, [Task 4.1] prints the SQL EF Core generates: one SELECT with two INNER JOINs, a WHERE on ReturnedOn and DueOn, and @asOf as a parameter. Notice that it selects every column of all three tables, although the report needs only three values — that's the cost the hand-written Dapper query avoids. After Task 4.2, [Task 4.2] prints the same three overdue loans from your Dapper query and confirms Same rows as EF Core? yes. After Task 4.3, [Task 4.3] lists Ada Lovelace's three loans, including the one she returned, and shows that the name Ada Lovelace' OR '1'='1 matches 0 rows.

5. Challenge

   Shape Repositories

   One seam instead of two data-access styles

   So far, callers would need to know whether a given read goes through EF Core or through Dapper. IToolLendingRepository, already declared in src/ToolLendingRepository.cs, exists to hide that choice: one interface, backed by both the shared DbContext and the shared SqliteConnection, so the rest of the application never has to pick a data-access technology itself. You'll finish the concrete ToolLendingRepository behind it, starting with the fast overdue-loans path. ## Fetching related data only when it's wanted

   A tool's full loan history can be long, and most callers asking "what is this tool" don't need it. EF Core's explicit loading — context.Entry(tool).Collection(t => t.Loans).LoadAsync() — fetches a navigation property on demand instead of automatically, which is the difference between a report that scans every loan a tool has ever had and one that only does that when a caller actually asks. ## Testing against the seam, not the database

   src/LoanService.cs holds business logic that should never need a live SQLite file to test: it depends only on IToolLendingRepository. That's what makes it possible to hand it a fake repository in a test and check its behavior in isolation. GetOverdueMemberNamesAsync is the method that proves it — a plain LINQ projection over whatever the repository returns, with no data-access code of its own. ## Check your work

   Run dotnet run --project src/Lab.csproj again. After Task 5.1, [Task 5.1] reports 3 loans from the repository's overdue report. After Task 5.2, the Cordless Drill loads with 0 loans when includeHistory is false and 2 loans when it's true. After Task 5.3, [Task 5.3] prints Ada Lovelace, Grace Hopper — each name once, even though Ada has two overdue loans.

6. Challenge

   Run the application

   Every task is done, and each one was graded the moment you completed it. What you have not done yet is watch the whole application run at once — which is the only place the pieces you wrote separately become a single working program.

   Start the application

   Open the Terminal tab and run:

   dotnet run --project src/Lab.csproj
   

   The program runs in the terminal and prints its output there. Every behavior it shows is code you wrote in the previous steps, so read the output against what each task asked for — that is the whole lab, working as one program. With every task complete, no section prints not implemented yet. ### Run the checks yourself

   The lab ran each task's test for you as you went, but they are ordinary tests and you can run them whenever you like. The whole suite:

   dotnet test tests/Lab.Tests.csproj --nologo --verbosity quiet
   

   Or one task at a time:

   bash runTest.sh Task_2_1
   

   Each failure names the task it belongs to, so the output tells you which step to go back to rather than which line to stare at. 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.