What to Unit Test Around EF and What Requires a Real Database

Learning outcomes

1. The first testing question is not “which mock library?”

ServiceHub now contains tenant filters, composite tenant-aware foreign keys, an application-managed Revision concurrency token, migrations, raw SQL boundaries, provider-specific mappings, and transaction behavior. A test double cannot reproduce all of those mechanisms. The useful question is: what observable behavior must this test prove?

A unit test runs a small unit of application/domain logic without the database. An integration test exercises EF plus a relational provider and database. A provider-fidelity test specifically runs against the same engine/provider family used in production when translation, DDL, locking, type semantics, or plans matter.

2. Pure domain logic belongs outside EF and stays cheap to test

Suppose ServiceHub escalates a work order when its priority is high and its age exceeds a policy threshold. This decision does not need SQL, identity resolution, or a change tracker. Keep the decision in a pure type and unit-test its edge cases directly.

public sealed class EscalationPolicy{    public bool ShouldEscalate(int priority, DateTime openedUtc, DateTime nowUtc)        => priority >= 3 && nowUtc - openedUtc >= TimeSpan.FromHours(4);}

[Theory][InlineData(3, 5, true)][InlineData(2, 8, false)][InlineData(4, 2, false)]public void Escalation_rule_is_pure(int priority, int ageHours, bool expected){    var now = new DateTime(2026, 8, 27, 12, 0, 0, DateTimeKind.Utc);    var result = new EscalationPolicy().ShouldEscalate(        priority, now.AddHours(-ageHours), now);    Assert.Equal(expected, result);}

This test is fast because the contract is pure. Adding EF to it would increase cost without adding evidence.

3. Some contracts only exist after EF translates and a database executes

Chapter 23 does not replace unit tests with integration tests. It puts each behavior at the cheapest layer that can actually prove it.

4. Deliberate failure: a mocked IQueryable is not an EF provider

A common shortcut is to expose IQueryable<WorkOrder>, return List<WorkOrder>.AsQueryable() in tests, and conclude that the production LINQ works. The test executes LINQ-to-Objects delegates; no EF translation, SQL parameterization, collation, global filter, provider function, or database constraint is involved.

IQueryable<WorkOrder> fake = seededWorkOrders.AsQueryable();var rows = fake    .Where(w => w.Summary.Contains(term))    .OrderBy(w => w.Id)    .Take(20)    .ToList();Assert.NotEmpty(rows); // says nothing about EF/provider translation

A safer seam is to unit-test business decisions above data access and separately integration-test the EF query against the relevant provider. If an application deliberately uses a repository abstraction, mock the repository’s result contract, not EF’s query provider.

5. Make the EF integration test assert external behavior

await using var db = fixture.CreateContext("tenant-a");var sql = db.WorkOrders    .Where(w => w.Priority >= 3)    .OrderBy(w => w.Id)    .Select(w => new { w.Id, w.WorkOrderNumber })    .ToQueryString();Assert.Contains("tenant_id", sql, StringComparison.OrdinalIgnoreCase);var rows = await db.WorkOrders    .Where(w => w.Priority >= 3)    .OrderBy(w => w.Id)    .ToListAsync();Assert.All(rows, row => Assert.Equal("tenant-a", row.TenantId));

The first assertion checks translation structure relevant to tenant defense. The result assertion checks runtime behavior. For a production provider, add provider-specific checks only where that SQL shape is part of the contract; avoid snapshotting every whitespace/alias detail.

6. Keep tests isolated from production credentials and data

Test databases must be disposable and unmistakably non-production. Use a temporary SQLite file, an in-memory SQLite connection, a dedicated local developer database, or a throwaway container. Never point tests at a production hostname, shared customer schema, or a secret copied from production configuration.

7. ServiceHub testing portfolio

A practical portfolio for this course is layered rather than ideological:

8. Mandatory lab: classify first, then implement two layers

1. List ten existing ServiceHub behaviors from Chapters 13–22.
2. Mark each as pure-domain, EF-relational, or production-provider-specific and write one sentence explaining why.
3. Add the pure EscalationPolicy test above.
4. Add a SQLite integration test for the tenant filter and composite tenant-aware relationship.
5. Capture the translated SQL with sensitive-data logging disabled.
6. Record one behavior the SQLite test still cannot prove for a SQL Server/PostgreSQL deployment.
7. Delete the disposable test database.

9. Production judgment and bridge

The correct testing strategy minimizes false confidence, not merely test duration. Unit-test pure logic aggressively, but do not replace the database when the database is the contract. Microsoft’s EF testing guidance specifically recommends good coverage against the production database system and discourages mocking DbSet for query behavior. Lesson 2 shows why the built-in InMemory provider is an especially weak relational substitute.

Authoritative references

• Choosing a testing strategy — Microsoft guidance on production-database tests, SQLite, InMemory, repositories, and mocked DbSet limitations
• Testing EF Core applications — official EF Core testing entry point
• Testing without the production database — test-double guidance and SQLite/InMemory examples
• EF Core concurrency — database-backed optimistic-concurrency behavior that unit fakes cannot prove
• EF Core transactions — transaction/savepoint behavior requiring relational execution
• EF Core performance — evidence-based performance testing and database-plan considerations