Guided: C# 14 Integration Testing with EF Core

Table of Contents

1. Challenge

   Introduction

   Welcome to the Integration Testing with EF Core Code Lab. In this hands-on lab, you learn when to test without a database versus when to use integration tests with a real database. You will write unit tests using in-memory repositories, then progress to integration tests against a real PostgreSQL instance. By the end of this lab, you will understand the trade-offs between test isolation and database integration testing.

   Entity Framework Core (EF Core) is an object-relational mapper (ORM) for .NET. It allows you to work with database records as C# objects instead of writing raw SQL. You define entity classes (like User), configure how they map to database tables, and EF Core handles translating your C# operations into SQL statements. When you call AddAsync on a repository, EF Core generates an INSERT statement. When you call FindAsync, it generates a SELECT. This translation layer is powerful but introduces a risk: if your entity mappings do not match the actual database schema, the generated SQL fails at runtime, not at compile time.

   The Database and Testing Bridge

   Not every test needs a database. In fact, many tests work better without one. The key insight is understanding which tests benefit from database access and which ones are faster and more reliable without it:

   • Tests without database access verify business logic in milliseconds. They use fake implementations that store data in memory, giving you speed, isolation, and determinism. The trade-off is that they cannot catch database-specific bugs.
   • Tests with real database access validate that your code works against a genuine database engine. They catch schema mismatches, constraint violations, and SQL translation errors that fakes miss entirely.

   A common trap in testing is writing tests that only prove the test itself works rather than proving the application works. For example, testing a fake repository only proves the fake behaves correctly—it says nothing about whether your actual SQL queries run or your database constraints hold. Both approaches have value, and a well-tested application uses both. This lab walks you through building each type so you can see the differences firsthand.

   What You Will Learn

   This lab covers three core areas:

   1. The database and testing bridge - You will see why excluding full database access from certain tests is valuable (speed, isolation, determinism) while also understanding why integration tests that use a real database instance are essential for catching real-world bugs.

   2. Writing integration tests against a real database - You will write an integration test for a repository that saves a User to a real PostgreSQL database and verifies that the data was actually persisted to the database.

   3. Test database configuration - You will configure test database startup (creating the schema), teardown (dropping the database), and test isolation (clearing data between tests). You will also practice DbContext injection and asserting database state.

   IMPORTANT: Before starting any tasks, run the setup script in the terminal:

   chmod +x setup.sh
   ./setup.sh
   

   Wait for the setup to complete. You will see Setup complete! when ready. The setup script ensures the built-in PostgreSQL server is running and configures a test database named carvedrock with a dedicated user.

   The Scenario

   You are a backend engineer at CarvedRock maintaining a user management service. The team debates how to test repositories: some prefer fast, isolated tests without database dependencies, while others want integration tests against real databases to catch schema issues. Recent production bugs from database mismatches show both approaches have value. Your job is to implement both testing strategies so the team can see the trade-offs and make informed decisions about which tests to write for each situation.

   The Application (click to expand)

   This lab uses a .NET 10 application with Entity Framework Core that manages user records. The application includes:

   • User entity representing customer accounts with Id, Username, Email, CreatedAt, and IsActive fields
   • IUserRepository interface defining the contract for data access operations (AddAsync, GetByIdAsync, UpdateAsync, DeleteAsync)
   • UserRepository that persists data through EF Core to a real PostgreSQL database
   • FakeUserRepository that uses an in-memory list for fast unit testing without any database dependency

   Your Mission

   Your job is to:

   1. Configure the EF Core DbContext with entity-to-table mappings
   2. Implement repository methods that persist data through EF Core
   3. Build a fake repository that simulates database behavior in memory
   4. Write unit tests and observe how fast they run without a database
   5. Configure the PostgreSQL test database lifecycle with proper startup, teardown, and test isolation
   6. Write an integration test that saves a user to the real database and verifies persistence
   7. Test data retrieval from the real database
   8. Test deletion from the real database and run the complete test suite

   Familiarizing with the Program Structure (click to expand)

   The lab environment includes the following key files:

   1. src/Models/User.cs - The User entity mapped to the database

   2. src/Data/AppDbContext.cs - EF Core database context (you will configure this)

   3. src/Repositories/IUserRepository.cs - Repository interface defining CRUD operations

   4. src/Repositories/UserRepository.cs - EF Core repository implementation (you will complete this)

   5. src/Repositories/FakeUserRepository.cs - In-memory repository for unit tests (you will complete this)

   6. tests/UnitTests/UserRepositoryUnitTests.cs - Unit tests using the fake repository (you will complete this)

   7. tests/IntegrationTests/DatabaseFixture.cs - PostgreSQL test fixture with startup, teardown, and isolation (you will complete this)

   8. tests/IntegrationTests/UserRepositoryIntegrationTests.cs - Integration tests against real database (you will complete this)

   9. tests/IntegrationTests/DatabaseCollection.cs - xUnit collection definition for sharing the fixture

      The environment uses C# 14 with .NET 10, Entity Framework Core with the Npgsql PostgreSQL provider, and xUnit for testing. All required dependencies are pre-installed.

   Build the solution using:

   dotnet build
   

   Run the tests using:

   dotnet test tests/CarvedRock.Tests.csproj
   

   Important Note: Complete tasks in order. Each task builds on the previous one. Build frequently using dotnet build to catch errors early.

2. Challenge

   Building the Data Access Layer

   Understanding DbContext

   The DbContext is the central class in Entity Framework Core. It represents a session with the database and provides an API for configuring entity mappings, querying data, and saving changes. Every EF Core application needs at least one DbContext that defines which entities map to which tables and how columns are configured.

   Two key concepts make the DbContext work:

   • DbSet<T> is a property on the context that represents a collection of entities mapped to a database table. When you declare DbSet<User> Users, you are telling EF Core two things: include the User entity in the database model, and provide a queryable collection that maps to the Users table. Without this declaration, EF Core does not know about your entity.
   • OnModelCreating is a method you override to configure entity mappings using the Fluent API. While EF Core can infer many mappings by convention (for example, a property named Id automatically becomes the primary key), explicit configuration gives you precise control. You can specify table names, mark columns as required, set maximum lengths, and define relationships. In production applications, this explicit configuration prevents subtle bugs where EF Core's conventions do not match your actual database schema.

   Why does this matter for testing? The entity mappings you configure determine the SQL statements that EF Core generates. If your mappings are wrong—for example, if you map to a table that does not exist or misconfigure a column constraint—your integration tests will fail, catching the bug before it reaches production. This is exactly the kind of bug that unit tests using fake repositories cannot detect. ### Understanding the Repository Pattern

   The repository pattern abstracts data access behind an interface. Instead of scattering database calls throughout your application, you define operations like AddAsync, UpdateAsync, and DeleteAsync on an interface (IUserRepository), then provide implementations that handle the actual data access. This separation is what makes testing possible—you can swap the real implementation for a fake one in tests.

   Change tracking is a core EF Core concept that directly affects how you write repository methods. When you add an entity to a DbSet, EF Core doesn't immediately write to the database. Instead, it begins tracking the entity in the Added state. Similarly, when you call Update, the entity enters the Modified state. Nothing touches the database until you call SaveChangesAsync, at which point EF Core inspects all tracked entities, generates the appropriate SQL statements (INSERT, UPDATE, DELETE), and executes them within a single transaction.

   This two-step pattern (track the change, then save) is important to understand because your repository methods must include both calls. If you add an entity but forget to save, the data never reaches the database. Integration tests will catch this mistake because they verify data in the actual database. A fake repository, which only adds items to a list, would not catch this bug.

3. Challenge

   Testing Without a Database

   Why Exclude the Database from Some Tests?

   This step addresses a fundamental testing question: when should you test without a database, and why is it valuable?

   Tests that exclude database access run in milliseconds. A fake repository stores data in a plain list, so there is no server startup, no network round-trip, and no shared state between tests. This speed makes them ideal for verifying business logic during development and running in CI pipelines where fast feedback matters.

   The critical point is that these tests verify your application logic, not the database. They answer questions like "Does AddAsync assign an ID?" and "Does GetByIdAsync return null for missing users?" These are logic questions that do not require a real database.

   Consider the CarvedRock scenario: the team's production bugs came from database schema mismatches, not logic errors. A fake repository could not have caught those bugs because it never talks to a database. However, fake repositories excel at catching a different class of bugs—logic errors in how your code handles data, null checks, ID assignment, and return values.

   The trade-off is clear. Fake repositories give you speed and isolation at the cost of database coverage. They skip SQL translation, ignore schema constraints, and never execute real queries. You get fast feedback on logic correctness, but you miss database-specific bugs entirely. That is why you also need integration tests, which you build in later steps.

   Task.FromResult is a pattern you will use in the fake repository. Because the IUserRepository interface defines async methods (returning Task<T>), the fake implementation must also return tasks. Since there is no actual async work happening (everything is in-memory), you wrap synchronous results in Task.FromResult to satisfy the interface contract without unnecessary overhead.

4. Challenge

   Writing Unit Tests and Comparing Speed

   Understanding the Arrange-Act-Assert Pattern

   xUnit is the testing framework used in this lab. It uses the [Fact] attribute to mark test methods that accept no parameters. Each test follows the Arrange-Act-Assert pattern:

   • Arrange - Set up the test data and dependencies. In unit tests, this means creating a fake repository and preparing User objects.
   • Act - Call the method being tested. This is the single action you want to verify.
   • Assert - Verify the results. xUnit's Assert class provides methods like Assert.True (verify a boolean condition), Assert.Equal (verify two values match), Assert.NotNull (verify a value exists), and Assert.Null (verify a value does not exist).

   Why Speed Matters in Testing

   Because you are using the FakeUserRepository, these tests execute without any database connection. The file includes a test called UnitTests_ShouldRunFast_WithoutDatabase that demonstrates this advantage explicitly. It runs 100 add-and-retrieve operations and measures the elapsed time, asserting that all 100 complete well under one second. In practice, unit tests like these often complete in single-digit milliseconds.

   This speed has practical consequences. When you run your test suite as part of a CI/CD pipeline, unit tests finish in seconds regardless of how many you have. Integration tests against a real database take longer because each operation involves network I/O, SQL parsing, and disk writes. When you run the full test suite in the final step, you will see this speed difference firsthand—the unit tests finish almost instantly while the integration tests take noticeably longer.

   The takeaway is not that integration tests are bad (they catch bugs that unit tests miss), but that you should write unit tests for logic and reserve integration tests for database-specific concerns. This gives you the best of both worlds: fast feedback on logic and thorough coverage of database behavior.

5. Challenge

   Configuring the Test Database

   Understanding Test Database Configuration

   Now you shift from unit tests to integration tests. The previous steps showed why excluding database access is valuable for testing logic quickly. This step shows the other side of the coin: configuring a real database for tests that need to verify actual data persistence.

   Integration tests need a real database, but they also need control over that database's lifecycle. A poorly configured test database causes flaky tests, leftover data from previous runs, and tests that pass in isolation but fail together. A well-configured test database handles three concerns:

   • Startup - Connect to the database and create the schema before any tests run. This ensures the tables exist and match your entity mappings. In this lab, startup calls EnsureDeletedAsync (to remove any leftover database from a previous run) followed by EnsureCreatedAsync (to build fresh tables from your entity mappings). This guarantees a clean starting state every time.

   • Teardown - Drop the database after all tests complete. This leaves no artifacts behind and prevents leftover data from affecting future test runs. Without proper teardown, old test data accumulates and can cause false failures or false passes in subsequent runs.

   • Test isolation - Reset the data between individual tests. Each test must start with a clean table so results are deterministic and tests do not interfere with each other. If Test A inserts a user and Test B expects an empty table, Test B will fail unless you clear the data first. The ResetAsync method handles this by removing all rows from the Users table between tests.

   How xUnit Manages the Fixture Lifecycle

   The DatabaseFixture class implements IAsyncLifetime, an xUnit interface with two methods: InitializeAsync (called once before any test in the collection runs) and DisposeAsync (called once after all tests in the collection complete). The [CollectionDefinition] attribute in DatabaseCollection.cs groups tests that share this fixture, so the database is created once and reused across all integration tests rather than recreated for each individual test.

   The ResetAsync method is different from InitializeAsync—it is called before each individual test (from the test class's own IAsyncLifetime.InitializeAsync), not once for the collection. This gives you collection-level startup/teardown plus test-level data isolation.

   DbContext injection is the pattern you use to connect tests to the database. The CreateContext method builds a new AppDbContext configured with the PostgreSQL connection string. Each test creates its own context, and when verifying data persistence, you create a separate context to force a real database read instead of getting cached data from EF Core's change tracker.

6. Challenge

   Writing Integration Tests Against a Real Database

   Verifying Real Database Persistence

   This is where you put everything together. You now have a configured DbContext, a working repository, and a database fixture that manages PostgreSQL. The integration tests you write here save, retrieve, and delete User entities in the real database and prove the operations actually worked.

   The critical difference between unit tests and integration tests is how you verify results. In a unit test, you check the return value of the method. In an integration test, you verify that data was actually written to the database by reading it back using a separate context.

   Why a separate context? EF Core maintains an in-memory cache called the change tracker. When you add a User and call SaveChangesAsync, the context remembers that entity. If you immediately query for that User using the same context, EF Core might return the cached entity without hitting the database. This means your test could pass even if the data was never written to PostgreSQL. By creating a fresh context from the fixture, you force EF Core to issue a real SQL SELECT query against the database. If the User is not there, the assertion fails—exactly what you want.

   This pattern—write with one context, read with another—is the gold standard for integration test verification. It proves that data survived the full round-trip: C# object to EF Core change tracker to SQL INSERT to PostgreSQL storage to SQL SELECT back to a new C# object.

   Verifying Read Operations Against a Real Database

   Integration tests for read operations follow the same insert-then-query pattern: first persist known data to the database, then call the repository method you want to test, and finally assert that the returned data matches what you inserted.

   This may seem redundant with the unit test you wrote earlier, but it verifies something entirely different. The unit test proved that the FakeUserRepository GetByIdAsync method correctly searches a list. The integration test proves that EF Core’s FindAsync generates the correct SQL query, PostgreSQL executes it correctly, and EF Core maps the result back to a C# User object with all properties intact.

   In real-world applications, read operations can fail in ways that fakes never reveal. For example, if you add a new column to the User entity but forget to update the database migration, the integration test fails because the column does not exist in PostgreSQL. The unit test would still pass because the fake repository has no concept of database columns. This is why testing both with and without a database gives you comprehensive coverage.

   Asserting Database State After Deletion

   Deletion is one of the most important operations to test against a real database. When you delete a user through the repository, you need to prove that the row was actually removed from PostgreSQL—not just that the method returned true. A fake repository removes an item from a list, which always works. But a real database might have foreign key constraints, triggers, or permissions that prevent deletion. Only an integration test catches those issues.

   The verification pattern is the same as for AddAsync: after calling DeleteAsync, create a fresh context and attempt to find the deleted user. If FindAsync returns null, the DELETE SQL statement executed successfully and the row is gone from PostgreSQL. If it returns the user, something went wrong.