LocalDb

LocalDbTestBase<T> enforces a strict lifecycle for database tests to prevent accidental state leakage or incorrect test logic. The lifecycle follows these transitions:

ArrangeData $\rightarrow$ ActData $\rightarrow$ AssertData

Rules and Exceptions

• Transitioning: Simply accessing the property (e.g., var x = ActData;) triggers the transition to that phase.
• Illegal Access:
  • Accessing ArrangeData after ActData has been accessed throws an exception: `"Phase has already moved to Act. Check for a ActData usage in the preceding code."$
  • Accessing ActData after AssertData has been accessed throws an exception: `"Phase has already moved to Assert. Check for a AssertData usage in the preceding code."$
• Queryable Constraints: Using a IQueryable created in a previous phase (e.g., a queryable from ArrangeData used inside an AssertData verification) after the phase has transitioned will throw an exception.

LocalDb uses a state machine to enforce the Arrange-Act-Assert pattern. This prevents common testing mistakes like modifying data in the 'Arrange' phase after the 'Act' phase has already begun.

• Transitions:
  • Accessing ActData $\rightarrow$ moves state from Arrange to Act.
  • Accessing AssertData $\rightarrow$ moves state from Act to Assert.
• Errors:
  • Accessing ArrangeData after ActData has been accessed $\rightarrow$ Throws exception: "Phase has already moved to Act. Check for a ActData usage in the preceding code."
  • Accessing ActData after AssertData has been accessed $\rightarrow$ Throws exception: "Phase has already moved to Assert. Check for a AssertData usage in the preceding code."
  • Accessing ArrangeData after AssertData has been accessed $\rightarrow$ Throws exception: "Phase has already moved to Assert. Check for a AssertData usage in the preceding code."

When a SqlInstance is defined, LocalDb creates a template database. All subsequent SqlDatabase objects created from that SqlInstance are cloned from this template.

Benefits

• Performance: Instead of re-creating schemas or running migrations every time a database is needed, LocalDb uses the pre-configured template.
• Consistency: Allows you to define schema and seed data once.

Template Database Settings

The following database-level settings are applied to the template and inherited by all databases attached from it:

• auto_update_statistics off: Prevents background statistics updates from causing nondeterministic test timing.
• read_committed_snapshot on: Uses row versioning for READ COMMITTED isolation. This is required to prevent S/X-lock deadlocks when running parallel transactional tests.

Updating Template Settings

If the template files already exist on disk, changes to settings will not be applied automatically. To regenerate the template with new settings, you must either:

1. Delete the existing template files from the file system.
2. Change the timestamp passed to the SqlInstance constructor.

When using SetCurrentPeriodStart or SetHistoryColumn, keep the following in mind:

• Strictly increasing periods: The periodStart provided must be strictly greater than the row's previous PeriodStart. Otherwise, re-enabling system versioning will fail with a period-overlap error.
• Temporal Configuration: The entity must be configured as temporal in OnModelCreating using .IsTemporal().
• Temporal Joins: When using TemporalAsOf, ensure your anchor time is after any related entities were created. Since TemporalAsOf uses inner joins for navigation properties, a missing related row at the anchor time will cause the query to return no results.
• Inheritance: For TPH (Table-Per-Hierarchy) inheritance, the helper automatically walks to the root entity type.
• Limitations: Composite primary keys are not supported.
• Concurrency: The UPDATE operations bump the RowVersion. Use the entity overload of SetCurrentPeriodStart or call Context.Entry(entity).ReloadAsync() after using the ID-based overload to avoid optimistic concurrency failures.

Each call to SetCurrentPeriodStart performs three round trips to SQL Server and involves several ALTER TABLE statements.

Key Performance Factors

• Locking: Each call takes a Sch-M (Schema Modification) lock, which invalidates the plan cache for that table.
• Validation Cost: When re-enabling system versioning, SQL Server validates that periods do not overlap. This cost scales with the total number of history rows, not just the rows being touched.
• Complexity: Typical cost is 5–15 ms on small databases.

Scaling Pitfalls

• Avoid loops over many rows: Calling the helper in a loop for $N$ inserts results in $O(N^2)$ complexity because each call re-validates all existing history rows. For bulk seeding, use a dedicated path that disables versioning, performs a bulk insert into the history table, and then re-enables versioning.
• Plan Cache: Frequent calls will cause subsequent EF queries to pay a one-time recompile cost.