03 — EF Core / SQL / Database P0 Review

Tracking vs AsNoTracking

Tracking = EF tracks entity state, useful for updates.
AsNoTracking = read-only, lower tracking overhead.

IQueryable vs IEnumerable

IQueryable = expression tree + provider translation.
IEnumerable = .NET enumeration / LINQ to Objects.

Deferred execution

Queries often execute when materialized/enumerated:
ToList, First, Single, Any, Count, Sum, Max, Min.

AsEnumerable

Does not immediately load everything.
Switches following operators to LINQ-to-Objects.

ACID

Atomicity = all or nothing
Consistency = preserve valid state
Isolation = concurrent transactions don’t corrupt each other
Durability = committed data survives failure

Isolation levels

Read Uncommitted, Read Committed, Repeatable Read, Snapshot, Serializable

Anomalies:
dirty read, non-repeatable read, phantom read

Trade-off:
stronger isolation can reduce concurrency and increase blocking.

Optimistic vs pessimistic concurrency

Optimistic = version/concurrency token, detect conflict at write time.
Pessimistic = lock earlier, more blocking/deadlock risk.

Inventory scenario

Stock = 1, two buyers.
Possible approaches:

  • atomic UPDATE
  • transaction
  • optimistic concurrency
  • pessimistic locking
  • suitable isolation

Example:
UPDATE Product
SET Quantity = Quantity – 1
WHERE Id = @id AND Quantity > 0;

Check affected rows.

Indexes

Know clustered, nonclustered, composite, covering.

Slow query:
identify query → execution plan → scan/seek → predicates/joins → indexes → IO/CPU → optimize → measure again

Deadlock

Tx A locks 1 needs 2; Tx B locks 2 needs 1.

Mitigate:

  • consistent lock order
  • short transactions
  • good indexes
  • no external calls inside transaction
  • retry victim when appropriate

Pagination

Offset = simple, worse at large offsets.
Cursor/keyset = efficient for large/changing datasets, needs stable sort key.

Questions

  1. Tracking vs AsNoTracking?
  2. IQueryable vs IEnumerable?
  3. Deferred execution?
  4. ACID?
  5. Isolation levels?
  6. Optimistic vs pessimistic?
  7. Prevent overselling?
  8. Investigate slow SQL?
  9. Deadlock?
  10. Offset vs cursor?