Entity Framework (EF6 & EF Core)

In short: An open-source, cross-platform ORM that maps C# classes to database tables.

• You query with LINQ and EF generates parameterised SQL, tracks changes and saves them in a transaction.
• Advantages: productivity (less data-access boilerplate), strong typing and compile-time checks, migrations for schema evolution, multiple DB providers, built-in protection from SQL injection, relationships and change tracking handled for you.
• Trade-offs: generated SQL must be monitored for performance; complex reports may be better in raw SQL/Dapper.

In short: Code-first: you design entity classes and DbContext; migrations create and evolve the schema — the model is the source of truth (common for new projects).

• Database-first: the database exists (or DBAs own it);
• you scaffold entities and context from it with dotnet ef dbcontext scaffold and re-scaffold when the schema changes.
• EF Core has no EDMX designer (that was EF6).

In short: DbContext represents a session with the database: it holds DbSets, the change tracker, configuration, and implements unit of work (SaveChanges).

• It is not thread-safe — never share it across concurrent operations (no parallel awaits on the same context).
• Register it scoped in ASP.NET Core (one per request) and keep it short-lived; use IDbContextFactory for background services, Blazor Server or parallel work.

In short: Migrations are versioned C# files describing schema changes (Up/Down), generated by comparing the current model with the model snapshot.

• Commands: dotnet ef migrations add <Name>, dotnet ef database update [target], dotnet ef migrations remove, dotnet ef migrations list, dotnet ef migrations script --idempotent, dotnet ef migrations bundle.
• Applied migrations are recorded in __EFMigrationsHistory.

In short: Eager: related data loaded in the same query with Include/ThenInclude.

• Explicit: load later on demand with Entry(x).Collection(...).Load() / .Reference(...).Load().
• Lazy: navigation properties load automatically on first access (needs proxies or ILazyLoader, virtual navigations) — convenient but causes hidden N+1 queries.
• Projections (Select) are often better than any of them for read models.

In short: When EF loads entities it keeps a snapshot of their original values.

• On SaveChanges, DetectChanges compares current vs original values and generates UPDATEs for changed columns; entity states drive INSERT/DELETE.
• AsNoTracking() skips this — faster and less memory for read-only queries, but changes to those objects won't be saved unless you attach them.

In short: Detached (not tracked), Added (INSERT on save), Unchanged (loaded, no changes), Modified (UPDATE on save), Deleted (DELETE on save).

• Inspect/set with db.Entry(entity).State.
• Attach → Unchanged; Update → Modified (all properties); Add → Added; Remove → Deleted (or detached if it was Added).
• After a successful save, Added/Modified become Unchanged and Deleted become Detached.

In short: Loading N parent entities with one query and then triggering one query per parent for its related data (usually lazy loading in a loop or view).

• Avoid by eager loading (Include), projecting into DTOs with the needed related fields in one query, using AsSplitQuery when including collections, and disabling lazy loading.
• Detect via SQL logging or profilers showing many similar queries.

In short: Optimistic concurrency via concurrency tokens: a [Timestamp]/IsRowVersion() rowversion column (SQL Server updates it automatically) or IsConcurrencyToken() on specific properties.

• EF adds the original value to the UPDATE/DELETE WHERE clause;
• if 0 rows are affected it throws DbUpdateConcurrencyException, which you handle by reloading, overwriting (client wins) or merging.
• No database locks are held between read and write.

In short: By convention (navigation properties + XxxId FK), data annotations ([ForeignKey], [InverseProperty], [Required]), or Fluent API (HasOne/WithMany/HasForeignKey/OnDelete).

• Fluent API has the highest precedence, keeps domain classes free of persistence attributes and supports everything (composite keys, indexes, delete behaviour, owned types, filters).
• Organise it in IEntityTypeConfiguration<T> classes.

In short: Project only needed columns;

• AsNoTracking;
• avoid N+1 and lazy loading;
• AsSplitQuery for multiple collection includes;
• paginate;
• add indexes for filters and sorts;
• set-based ExecuteUpdate/Delete;
• compiled queries and DbContext pooling for hot paths;
• check generated SQL and execution plans;
• avoid client evaluation;
• batch writes;
• keep contexts small;
• cache read-mostly data;
• and use Dapper or views/stored procedures for complex reporting queries.

In short: Classic N+1: one query for orders and one per order for its customer (lazy loading).

• Replace with a single projection: db.Orders.AsNoTracking().OrderByDescending(o => o.CreatedAt).Take(50).Select(o => new OrderRow(o.Id, o.Customer.Name, o.Total)).ToListAsync() → one SQL with a JOIN.
• Disable lazy loading globally, add an index on CreatedAt, and confirm with logging that only one query runs.

In short: Add a rowversion concurrency token ([Timestamp] byte[] RowVersion).

• Send the RowVersion to the client with the data and back on update;
• set it as the original value (db.Entry(p).Property(x => x.RowVersion).OriginalValue = dto.RowVersion).
• If someone else saved first, EF throws DbUpdateConcurrencyException;
• return 409 Conflict (or 412 with ETags) with the current values so the user can review and retry.

In short: Don't load entities.

• Use ExecuteDeleteAsync in batches (e.g. loop deleting Take(10_000) by key range or TOP (10000) raw SQL until 0 rows) to keep transactions small and avoid lock escalation and log growth;
• run off-peak via a background job;
• ensure an index on the date column.
• Better long-term: table partitioning by month and TRUNCATE/switch out old partitions, or a retention policy in a time-series store.