EF Core Data Access and Migrations

A valid migration can still erase the names

An orders table contains two unfulfilled orders: Neda and Reza. The new model renames CustomerName to BuyerName, adds required Status, and introduces a concurrency token. An AI assistant generates a migration that drops the old column and adds a new one. The command succeeds. Have you preserved the customers' names?

Your deliverable is the EF Core Data Access, Migration and Concurrency Evidence Ledger v1. It connects the model, provider, query, migration operations, database observations and conflict policy to a bounded review decision. You will distinguish a generated plan from an observed result and from permission to deploy.

Allow 60 active minutes: 10 for the model, 15 for the worked example, 15 for guided diagnosis, 15 for independent transfer and 5 for defending the ledger. The core uses Python 3.10+ and supplied synthetic JSON; no external Python packages, database server or SDK are needed. A second script actually executes SQL against temporary, in-memory SQLite data. That execution does not run EF Core. The C# excerpts explain the corresponding EF APIs; they are not a compiled project or publisher execution evidence.

For readers who know basic C#, asynchronous calls and dependency lifetimes and need to justify data-access changes beyond “the tool generated it.”

This lesson does not build an entire web API, select a production database, run a live migration or complete the hosting and diagnostics outcome of NET-004.

Check the foundation, then choose a route

NET-001 is required; NET-002 is helpful. Educational navigation proceeds from NET-002 to this lesson and then NET-004, independently of the order in which these materials were produced.

Explain what await waits for, who disposes a scoped dependency, and why two concurrent operations should not share a mutable unit of work. Then distinguish a C# property from a stored database column.

A model describes the objects your code expects; a database stores values under an existing schema. Changing one does not automatically change the other. A unit of work groups related changes; its owner must finish and dispose it. Await a database operation before reusing its context. To check this bridge, describe how a renamed property can leave an old database column untouched and identify the component that owns the pending changes.

Foundation route: read the field map, follow both worked cases, then run the exercise. Repair route: isolate the first invalid dimension, but retain all other blockers until each has evidence. Experienced route: inspect the tradeoffs and start at practice. All routes submit the same ledger, negative case and changed-provider transfer.

Five records answer five different questions

The model is the application's intended shape. The model snapshot records the model associated with the last scaffolded migration. Migration operations propose how to move the schema. The database migration history records which migrations were applied. Finally, observed rows and tests show what actually happened in a particular fixture. Agreement between only two of these records is insufficient.

EF migrations use model changes to generate incremental schema updates and maintain applied-migration history. A rename may be interpreted as drop/add, so review generated operations. Migrations overview, Managing migrations

Imagine the database is current with all migration files, but a developer adds Archived without scaffolding another migration. There can be no unapplied migration file while the model still differs from its snapshot. Conversely, an unchanged model can coexist with a database missing an already generated migration. These are different defects with different repairs.

The flow's text equivalent is: bind model and provider; inspect query and tracking; compare snapshot, operations and history; run disposable tests; resolve the observed conflict; review deployment evidence. Missing or stale evidence means HOLD; negative evidence means repair and rerun while retaining HOLD; complete evidence permits READY_FOR_REVIEW only within this synthetic exercise. These states are MAKNOW design choices, not Microsoft deployment approval.

Read the ledger before reading the verdict

The downloadable NET-003-T1.csv includes the complete evidence fields; the compact table here is a reading aid. Fixture fields are nested: for example, migration_operation in the ledger comes from /migration/operations, not a second invented capture. field_map.json states every mapping.

UNKNOWN means a decisive value is unknown; UNVERIFIED means evidence has not established it; UNSUPPORTED means this provider or contract cannot support the claim; STALE means a capture no longer applies. FAILED records a violated expectation and REVIEW_REQUIRED keeps a decision open. None can be averaged away by successful checks. CONCURRENCY_CONFLICT remains visible until a tested business resolution handles it. A resolved conflict can be a passing negative test: the expected behavior was to reject a stale write.

For this exercise, all starting orders are unfulfilled. That explicit business assumption justifies backfilling Status with Pending; it would mislabel a mixed historical table. Likewise, token value 1 is an initial exercise value, not a timestamp.

From EF code to evidence

EF Core 10 targets .NET 10 and is an LTS release supported through November 10, 2028. That lifecycle fact does not establish provider compatibility for your application. EF Core 10

Context and query boundaries

Use a short-lived DbContext for a unit of work. It is not thread-safe; await operations or use separate contexts for independent concurrent work. DbContext lifetime

For the supplied entity-read contract, the intent is explicit:

var orders = await db.Orders.AsNoTracking()
    .OrderBy(o => o.Id).ToListAsync(cancellationToken);

This returns entities without retaining them in this context's change tracker. A tracking query can reuse an already tracked instance. A scalar-only DTO projection has no entity to track; an anonymous result containing an entity can still track that entity. Thus “projection means no tracking” and “no tracking is always faster” are poor general rules. Tracking queries

Our test starts with a fresh context and expects two entity results and zero tracked entries. If you remove AsNoTracking, returned values may remain identical while the tracking contract fails. A test that checks only the count misses that change. Do not infer EF tracking behavior from the SQLite SQL demo; SQL alone has no EF change tracker.

Review a transition, not just a generated filename

These commands belong in a separately prepared, disposable EF Core 10 project with its matching provider and tools. They do not run from the Python learner folder:

dotnet ef migrations add BuyerStatusVersion
dotnet ef migrations has-pending-model-changes
dotnet ef migrations script Initial BuyerStatusVersion

Use that project's actual migration names or IDs for the script range. Our JSON identifiers 001_Initial and 002_BuyerStatusVersion are synthetic labels, not scaffolded timestamp IDs. Inspect the Up operations before applying the script. For the rename, the intended operation is:

migrationBuilder.RenameColumn(
    name: "CustomerName", table: "Orders", newName: "BuyerName");

Then add and backfill required fields according to the business assumption, inspect provider-specific generated SQL and rehearse on disposable rows. Compare before/after names and row count; test failure partway through the transition. A reversible schema operation does not recover data already discarded by a destructive change.

GetPendingMigrationsAsync concerns unapplied migrations, not unscaffolded model changes. Use the pending-model-change check for the latter. EF 9+ migration application detects pending model changes; ignoring that warning is not a repair. Managing migrations, EF 9 behavior changes

Treat optimistic concurrency as a business decision

The example uses an application-managed integer token:

modelBuilder.Entity<Order>()
    .Property(o => o.Version).IsConcurrencyToken();
// After loading the tracked row and choosing a valid change:
order.Status = "Paid";
order.Version += 1; // Application responsibility, not IsConcurrencyToken magic.
await db.SaveChangesAsync(cancellationToken);

The stored token and the original token participate in conflict detection. EF can raise DbUpdateConcurrencyException when an expected update affects no row. SQL Server's database-generated rowversion is provider-specific; SQLite needs another strategy, such as an application-managed token. Concurrency

Suppose both writers read token 1. The first writes Paid and token 2. The stale second writer must not silently replace it with Cancelled. Our business rule is reject, leaving Paid intact and asking the caller to reload before making a new decision. Retry, merge and rejection are alternatives requiring their own business policy. Merely changing the expected token and replaying the stale intent is not conflict resolution.

Transactions and migration locks have boundaries

A supported provider normally makes one SaveChanges call transactional; several calls are not automatically one business transaction. Our SQL rehearsal deliberately uses an explicit transaction and tests rollback. Provider limitations still apply. Transactions

Review generated migrations and test before production application. EF 9+ migration application locking reduces concurrent migration execution; it does not stop ordinary application traffic or establish compatible online DDL. An externally executed SQL script also depends on its execution tool and transaction boundaries. Applying migrations

SQLite may require table rebuilds for schema changes and does not support EF's general idempotent-script mechanism. Its EF migration lock table can need recovery after a failed process. First establish whether an applier is active and what state remains; this lesson supplies no blind lock-table deletion command. A two-row SQLite rehearsal cannot certify a SQL Server migration. SQLite limitations, Testing strategy

Worked evidence: preserve names and reject the stale writer

Open fixtures/baseline.json. Its observations are invented, clearly labelled evidence for orders-r2-sqlite-small. Read expectations before the verdict:

The complete synthetic record supports READY_FOR_REVIEW, scope SYNTHETIC_ONLY. It does not report a real EF run. Note how the first writer changes the database after the migration/query observations: the migration expects Pending, while the later conflict capture retains Paid. They are different points in the test sequence, not contradictory snapshots.

Now inspect drop_add.json. /migration/operations/0 drops CustomerName. Even if /migration/data_loss_warning is false and test labels remain PASS, the operation contradicts the preservation requirement. Record both the destructive operation and the incomplete reviewed plan; aggregate HOLD. The minimal repair is to review a data-preserving rename, rebuild the migration evidence and rerun preservation and rollback checks. Editing the warning to false repairs nothing.

For a separate actual experiment, run python sqlite_demo.py. It creates only in-memory data, migrates it, executes both competing updates and triggers a rollback test. Its JSON output reports the SQL observations and ef_core_executed: false. The deliberately named LessonMigrationHistory is a teaching table, not EF's migration history. Zero affected rows models the database predicate underlying optimistic concurrency; the script does not throw or test an EF exception.

Diagnose what a green label conceals

missing_evidence, history_gap and stale_review provide additional diagnosis. State what is absent or invalid instead of inventing a replacement capture. A source link explains an API; it cannot replace your run result. A reviewer name in a synthetic fixture is also synthetic and is not a production owner's authorization.

Do not repair one blocker and declare success while another remains. Preserve the full issue set, make the smallest coherent repair to the affected dimension, and recompute. If the requested change alters the fixed exercise target, write a new reviewed contract rather than quietly moving the expectation to match the result.

For experienced readers: inspect the assumptions first

Ask whether an existing tracked entity can affect the read, whether a required-column backfill tells the truth about old rows, and whether a concurrency retry replays a business action that is no longer valid. Those questions expose more than memorizing migration commands.

A generated SQL file deserves review for target database state, data preservation, transaction boundaries, compatibility during deployment, expected locking and recovery. The exercise's REVIEWED_MANUAL_SCRIPT is one controlled plan; it does not mean every application must use manual execution. With a different deployment method, retain those evidence obligations.

Keep the scope honest when transferring prior experience. A successful relational query in SQLite does not establish SQL Server translation, locking or rowversion behavior. An EF in-memory test provider does not establish relational migration correctness. Your fast path saves explanatory reading; it does not waive the negative case or transfer.

Choose a strategy by its remaining obligations

For a large table, consider staged compatibility and measured batches rather than assuming the small-fixture operation is operationally acceptable. Our rule triggers a batched rehearsal at 100,000 rows. This number is an exercise threshold, not a universal database safety limit. A real threshold depends on schema, workload, hardware and allowed interruption. Recording five million rows with a small-fixture capture does not meet it.

Build your own response without changing the inputs

Download the learner files. Open a terminal in the extracted learner folder. Start with the inventory and optional SQL observation:

python run_checks.py --inventory
python sqlite_demo.py
python run_checks.py --template fixtures/pending_model.json submission.json --as-of 2026-09-12

The template supplies input and policy hashes, context and evaluation date. It leaves the decision and explanation for you. Existing output files are not overwritten. The historical date above makes this lesson's source-review comparison reproducible; record today's date when making a current evaluation and do not hide expired evidence by backdating it.

Guided task: compare the target shape with the snapshot and visible fixed contract. Follow MODEL_PENDING in policy.json; identify the exact pointer that invalidates the NONE claim. Fill the aggregate state, every issue's code and state, and the fields evidence_refs, rationale, minimal_repair and review_trigger. Explain how the intended model change would get a reviewed migration; do not edit the shipped input to create a PASS.

python run_checks.py --check fixtures/pending_model.json submission.json

STRUCTURE_PASS_SEMANTICS_UNREVIEWED means the response is well formed and bound to these inputs. It does not mean the decision is correct. Evidence references are JSON Pointers, such as /model/target; they identify fields in this fixture, not invented files. Use numeric array indices and valid ~0/~1 escapes when needed.

Next, independently analyze tracking_changed, unsupported_token and concurrency_conflict. Finally create a fresh submission for transfer_provider.json: a SQL Server target with five million rows still carries evidence from the earlier context. Determine every affected dimension, the bounded decision and the minimum new provider-specific rehearsal. The lesson does not prefill this transfer answer. Treat all captures as synthetic and explain what real observations would be needed before a deployment review.

AI may help format supplied evidence or critique your reasoning. It may not invent test results, recover missing authority or manufacture an approval. Keep your final explanation inspectable by another reader.

What the submitted artifact must prove

Submit the unchanged fixture identity and SHA-256, policy SHA-256, context, evaluation date, aggregate state, complete typed issue set, exact field references, explanation, minimal repair and review trigger. Include your baseline ledger, destructive-change diagnosis and independent changed-provider response. The CSV and JSON table are synchronized views of the worked baseline, not a filled answer for independent practice.

The public checker validates shape and binding. The instructor semantic check compares the complete state and issue set against visible rules, then still requires review of the explanation and references. Neither treats a hash as proof that a synthetic capture happened in a real database. A human reviewer should be able to reconstruct your conclusion from the visible files.

For real execution evidence, additionally record project revision, SDK and EF/provider versions, database identity, commands, migration source and SQL, observations, failures and cleanup. Preserve original negative observations after repair. Our publisher receipt covers actual SQLite SQL on invented rows; EF tracking, EF scaffolding, exception handling and SQL Server behavior remain unexecuted here.

Four tasks, five critical dimensions

NET-003-S1 — Retrieval. Explain the distinction between model snapshot, migration history and observed rows. State this lesson's data-access boundary and one outcome owned by an adjacent lesson.

NET-003-S2 — Application. Produce the baseline ledger and justify its bounded state. Explain why a rejected stale update can satisfy the concurrency test and why it cannot authorize production deployment.

NET-003-S3 — Diagnosis. For the generated drop/add change, identify the invalid operation, likely data consequence and minimum data-preserving repair. Explain why PASS labels and a false warning flag cannot override the operation.

NET-003-S4 — Transfer. Recompute transfer_provider from its own evidence. Name the changed provider and scale, all captures that require replacement, a suitable rehearsal plan and the conditions for reopening review. Do not copy the baseline verdict.

The five rubric dimensions are scope/contract, exact evidence binding, negative evidence, bounded decision and transfer. Each must be sufficient; averaging cannot erase a critical failure. A correct aggregate state with missing blockers is incomplete. A complete list without an explanation of the data consequence is also incomplete. Repair scope errors through the mental model, tracking or token errors through the explanation, and copied-transfer errors by rebuilding the changed-context ledger.

Sources, dates and claim boundaries

The linked Microsoft pages were checked on 2026-09-12; the next planned review is 2026-10-18, or earlier when a relevant EF/provider behavior changes. sources.json and source_links.md in the learner download contain the complete official-source list. See the migrations overview and concurrency documentation for the central platform boundaries.

Separate FACT (documented API or provider behavior), INFERENCE (what the supplied evidence permits) and DESIGN (our two-row contract, reject policy, typed states and large-table threshold). Microsoft documentation supports the first category; it neither certifies MAKNOW's exercise nor supplies missing execution evidence. Refresh by creating a new reviewed version; preserve this version's sources, fixture hashes and dated observations.

python run_checks.py --inventory
python sqlite_demo.py
python run_checks.py --template fixtures/pending_model.json submission.json --as-of 2026-09-12
python run_checks.py --check fixtures/pending_model.json submission.json