Back to interview questions
By
Team Sherlock.sh
An Entity Framework interview should test whether a developer can connect application code to database behavior. Writing a LINQ expression is only part of the task. Candidates also need to explain which data is loaded, what the context tracks, when changes are saved, and how constraints or competing updates can affect the result.
This guide to 100+ Entity Framework Interview Questions and Answers contains 120 questions across beginner, intermediate, and experienced levels. The default baseline is Entity Framework Core 10 on .NET 10. Entity Framework 6 appears only in explicitly labeled comparisons or migration discussions, while database-provider differences remain part of the reasoning rather than being hidden behind a common API.
How can recruiters detect AI-assisted cheating, deepfakes, and impersonation during Entity Framework interviews?
Entity Framework interviews often combine a data-model exercise with a LINQ query, disconnected update, or concurrency scenario. Candidates need to trace the database interaction and explain the trade-offs in their own solution. Unauthorized real-time assistance can make that independent understanding harder to assess.
Sherlock AI helps recruiters detect potential AI-assisted cheating, deepfakes, candidate impersonation, and unauthorized real-time assistance during Entity Framework developer interviews.
Pair interview-integrity checks with a small, clearly specified data-access problem. Add a competing update, change the relationship cardinality, or ask how the query would be tested against the chosen database. Review suspicious activity in context rather than treating a signal as proof of misconduct.
Entity Framework Interview Questions
Choose questions that match the role’s modeling, querying, persistence, and database-maintenance responsibilities. Establish the EF version and provider before discussing behavior. Ask candidates to connect their C# choices to generated commands, database guarantees, and observable failure cases.
Entity Framework Interview Questions for Beginners
These beginner questions cover contexts, entity sets, models, keys, relationships, LINQ queries, tracking, saving changes, and migrations. They test whether a candidate can explain a simple data-access operation from model definition to database result.
What is Entity Framework, and how do EF Core 10 and EF6 differ?
What does an object-relational mapper do, and what database knowledge does EF Core still require?
What responsibilities belong to a DbContext?
Why should a DbContext be short-lived and never used concurrently?
How does DbSet<TEntity> differ from a database table and a materialized list?
What does an EF Core database provider control?
How do you configure a SQL Server DbContext without hard-coding credentials?
How does EF Core discover entity types and build its model?
How are primary keys and generated key values configured?
When do you need a composite key instead of a single-column key?
How do conventions, data annotations, and Fluent API interact?
How do nullability, maximum length, and decimal precision affect mappings?
What do Detached, Unchanged, Added, Modified, and Deleted mean?
How do you insert an entity and retrieve its generated key?
How do you change one property of an entity already tracked by the context?
How does removing an entity differ from removing an item from a navigation collection?
What happens when SaveChangesAsync runs, and what does its return value represent?
When should you use FindAsync, FirstOrDefaultAsync, or SingleOrDefaultAsync?
When does a LINQ query execute, and what happens if you enumerate it twice?
What changes when an EF query crosses from IQueryable to IEnumerable?
How do you filter and order a query before materializing it?
Why project a read-only query into a DTO rather than return full entities?
When should you use AnyAsync rather than CountAsync?
What do async database methods and cancellation tokens actually provide?
What is change tracking, and when is it useful?
When should you use AsNoTracking, and what does it not guarantee?
How are one-to-many relationships represented in the model?
What is the difference between a foreign-key property and a navigation property?
How do Include and ThenInclude load related data?
How do you explicitly load a related collection?
What configuration is needed for lazy loading, and why can it surprise callers?
What is the N+1 query problem, and how can a projection avoid it?
How do code-first development with migrations and reverse engineering differ in EF Core?
What files does a migration add, and why is the model snapshot important?
Why should EnsureCreated not be mixed with a migrations-based lifecycle?
How can you inspect the SQL behind a LINQ query without confusing it with an execution plan?
How do you pass values safely into a raw SQL query?
What is optimistic concurrency, and when does EF Core report a conflict?
What transaction guarantee does one SaveChanges call normally provide?
How would you write a bounded, ordered, read-only product search query?
Entity Framework Interview Questions for Intermediate
These intermediate questions examine query composition, loading strategies, relationship configuration, disconnected updates, concurrency, transactions, migrations, and testing. They ask candidates to preserve correctness when data volume or application boundaries make simple examples insufficient.
How do you organize a large model with IEntityTypeConfiguration classes?
When are backing fields and shadow properties useful?
How do you configure a unique or composite index, and what must the database enforce?
What are value converters, and when is a value comparer also needed?
How do owned entity types differ from complex types in EF Core 10?
When should a query result use a keyless entity type?
How do you model a many-to-many relationship with payload on the join row?
How do you configure a one-to-one relationship and identify its dependent?
How do cascade delete, client-side deletion, and restrictive foreign keys differ?
How do you safely change an optional relationship to a required one?
What can make filtered Include return unexpected entities in a tracking context?
When should you choose AsSplitQuery over a single query?
How does relationship fixup keep foreign keys and navigations aligned?
How does AsNoTrackingWithIdentityResolution differ from tracking and plain no-tracking?
How do you apply an authorized disconnected update without overwriting unrelated fields?
Why can attaching or updating an entire disconnected graph be dangerous?
How would you resolve a DbUpdateConcurrencyException without silently losing an edit?
How does ExecuteUpdateAsync differ from modifying tracked entities and saving?
How do you make an ExecuteDeleteAsync operation bounded and safe?
How does SaveChanges batching differ from set-based updates?
How do explicit transactions and savepoints interact with SaveChanges?
How do you combine an execution strategy with a user-managed transaction?
Which LINQ expressions run on the server, and where is client evaluation allowed?
How do you express an outer join, and why does query shape matter for translation?
When can GroupBy translate to SQL, and when is grouping performed after retrieval?
How can SQL null semantics and database collation change LINQ query results?
How do you implement keyset pagination with a fully unique ordering?
When does streaming reduce memory, and what can still cause EF to buffer results?
What restrictions apply when composing LINQ over FromSql or a stored procedure?
How do you produce and review an idempotent migration script for deployment?
How do you avoid data loss when a generated migration treats a rename as drop-and-add?
How do you preserve customizations when re-scaffolding an existing database?
How do UseSeeding, UseAsyncSeeding, and HasData serve different purposes?
How do global query filters implement soft deletion and tenant-scoped reads in EF Core 10?
How do you convert a tracked delete into a soft delete without assuming bulk deletes follow it?
How do you enforce tenant ownership on writes as well as reads?
How do you log useful database diagnostics without exposing sensitive values?
Why can InMemory or SQLite tests pass when the production provider fails?
When does a repository abstraction help testing, and when does it hide important EF behavior?
How would you implement an authorized price update with a SQL Server rowversion token?
Entity Framework Interview Questions for Experienced
These experienced-level questions focus on data-access architecture, difficult query behavior, concurrency, reliability, performance evidence, and controlled schema evolution. They assess how a developer protects correctness while changing a larger system or investigating a production-shaped workload.
How would you investigate a query that became slow after a data or deployment change?
How do you decide whether to change a LINQ predicate, an index, or both?
How would you redesign an endpoint that loads a large object graph and times out?
How do you maintain a consistent view of data across the multiple commands of a split query?
When do explicit compiled queries help, and what are their limitations?
How can dynamically built expression trees defeat EF and database query caching?
When can a compiled model reduce startup cost, and what trade-offs must you check?
What tenant-state and connection-state hazards arise with DbContext pooling?
Why can row-level optimistic concurrency fail to protect a cross-row business invariant?
How do you make a retried write operation idempotent?
How would you coordinate a database update and message publication with an outbox?
How can two DbContext instances share a relational transaction, and what remains separate?
How would you diagnose deadlocks without treating longer timeouts as the fix?
What should an application do when the connection fails during transaction commit?
How do you plan an expand-and-contract schema change for a rolling deployment?
How would you detect and repair schema drift between migrations and a production database?
When is a reverse migration unsafe, and how should a rollback plan handle data changes?
How do you manage migrations when the application supports more than one database provider?
What should a production-provider integration-test matrix cover beyond CRUD?
How do you choose between tenant-discriminator and database-per-tenant designs?
How can SQL Server row-level security complement EF tenant filters?
How do you control intentional query-filter bypasses without creating a cross-tenant data leak?
How do you design a SaveChanges interceptor for auditing without recursive saves or unsafe shared state?
How do TPH, TPT, and TPC inheritance mappings change query and schema costs?
When should structured JSON data use EF Core 10 complex-type mapping rather than a serialized string converter?
When should a database function be mapped into LINQ rather than called through client code?
How do SQL Server temporal tables help investigate historical changes, and what are their limits?
How should command timeouts and cancellation be handled in a long-running EF operation?
How would you diagnose connection-pool exhaustion in an EF application?
How would you investigate excessive memory retention in a long-lived change tracker?
How do you distinguish a translation regression from a change in database query-plan behavior?
How do you roll out nullable-reference-type changes without accidentally making existing columns required?
How can orphan deletion and relationship fixup cause an unexpected delete during graph editing?
How do alternate keys differ from unique indexes when other entities reference a business identifier?
What must be revalidated when migrating an EF6 application to EF Core?
How would you validate an EF Core 10 upgrade before deploying it?
How do you choose between tracked inserts, set-based updates, and provider-specific bulk-import tooling?
When is disabling automatic change detection justified, and how do you restore correctness?
How would you benchmark an EF optimization without measuring a misleading microbenchmark?
How would you investigate and contain a production incident involving lost updates, duplicate writes, and slow queries?
Entity Framework Interview Questions and Answers
The answers follow the question bank in order and explain both the EF API and its database implications. Examples may be focused configuration or query excerpts rather than complete applications. Use the stated entities, imports, provider packages, schema, and setup; treat EF6 comparisons separately from EF Core examples. Unless stated otherwise, C# snippets assume file-level imports for System, System.Collections.Generic, System.Linq, System.Threading, System.Threading.Tasks, and Microsoft.EntityFrameworkCore; db is a configured, short-lived context and ct is a CancellationToken. Awaited fragments belong inside an async method.
Entity Framework Interview Questions and Answers for Beginners
The beginner answers establish the responsibilities of the context, model, provider, and database. They distinguish changing an object from persisting it and emphasize that an object-relational mapper does not remove the need to understand relational data.
1. What is Entity Framework, and how do EF Core 10 and EF6 differ?
Entity Framework is Microsoft's family of data-access frameworks for mapping .NET objects to database data. EF Core supports LINQ queries, change tracking, persistence, and schema migrations through database providers. This article uses EF Core 10 on .NET 10, with APIs in Microsoft.EntityFrameworkCore.
EF6 is a separate, mature product whose APIs commonly use System.Data.Entity. It remains supported but is not receiving the active feature development of EF Core. EF Core is not a drop-in upgrade: mapping conventions, supported features, generated queries, and provider behavior need review. EF6's EDMX designer workflow should not be presented as an EF Core capability. The choice for an existing application depends on its dependencies and migration cost, not simply its age.
2. What does an object-relational mapper do, and what database knowledge does EF Core still require?
An object-relational mapper translates between an application's object model and relational storage. EF Core materializes query results into objects, translates supported LINQ expressions into provider queries, and turns tracked changes into database commands. It removes much repetitive mapping and command-building code.
Database design still determines correctness and performance. Developers must understand keys, foreign keys, nullability, joins, indexes, transactions, and concurrency. A valid LINQ expression can still request too many rows or generate expensive SQL. For example, loading complete entities to count them wastes transfer and allocation when a database aggregate is sufficient. Inspect the generated commands and database execution plan when performance matters; the ORM does not choose the database's physical execution plan.
3. What responsibilities belong to a DbContext?
A DbContext coordinates a unit of work. It exposes entity sets, uses the configured provider and mapping model to execute queries, tracks entity identities and changes, and writes those changes through SaveChanges or SaveChangesAsync. Its Database API also provides database-level operations, such as relational transaction access.
The context is not a shared application-wide object store. Tracked objects describe this unit of work, not every current row in the database. Ordinary queries still reach the database, and existing tracked values may remain unchanged during materialization. Application services remain responsible for authorization and business decisions. They use the context to persist an allowed operation rather than expecting tracking or mapping configuration to supply access control.
4. Why should a DbContext be short-lived and never used concurrently?
A short-lived context limits retained entities, stale tracked values, and the amount of change-detection work. A common lifetime is one request or business operation, followed by disposal. AddDbContext registers a scoped service by default; applications with multiple independent units of work inside one scope can use a context factory.
A DbContext is not thread-safe. Two asynchronous operations can overlap even without explicitly creating threads, so await one operation before starting another on the same instance. Genuine parallel database work requires separate contexts, which also means separate trackers and ordinarily separate transactions. Do not register a context as a singleton. Dispose contexts you create yourself; dependency injection disposes contexts whose scope it owns.
5. How does DbSet<TEntity> differ from a database table and a materialized list?
A DbSet<TEntity> is a context-bound entry point for querying and changing entities of a mapped type. It participates in LINQ expression construction and provides methods such as Add and Remove. Reading the property does not download the database table.
A database table is a storage object; a materialized List<TEntity> is an in-memory collection containing the results already retrieved. Enumerating a list does not rerun its original query. A set is not necessarily a one-to-one wrapper around a table: inheritance and other mappings can distribute entity data differently. Nor is it a list of tracked objects. DbSet.Local offers a local view of relevant tracked entities, while an ordinary set query evaluates against database data.
6. What does an EF Core database provider control?
A database provider connects EF Core to a particular database engine. For a relational provider, it contributes SQL translation, store-type mappings, command execution, schema-generation behavior, and database-specific features. The application selects it through configuration such as UseSqlServer.
Providers are not interchangeable compatibility guarantees. A LINQ expression supported by SQL Server may translate differently or fail with another provider; decimal handling, generated values, migrations, and transactions can differ. EF Core also has non-relational providers, so relational assumptions must be stated explicitly. Use a provider version compatible with EF Core 10 and review its support matrix. Changing only the connection string cannot switch from SQL Server to SQLite or Cosmos DB; provider configuration and application behavior also require review.
7. How do you configure a SQL Server DbContext without hard-coding credentials?
Read the connection string from application configuration, then pass it into the SQL Server provider. Keep passwords out of source control: development secrets can come from user-secrets, while deployed applications should use an appropriate secret store or identity-based authentication. Reading configuration does not itself make a credential secure; the source and access permissions matter.
This .NET 10 ASP.NET Core example requires compatible Microsoft.EntityFrameworkCore.SqlServer 10.0.x packages and a configured ConnectionStrings:Catalog value. The service scope owns the context's disposal. The Product and CatalogContext definitions below are the shared model for later product-query fragments; those fragments assume a migrated SQL Server database, an injected db context, and an async method with a CancellationToken ct parameter.
C# example
8. How does EF Core discover entity types and build its model?
EF Core includes types exposed by DbSet properties, types explicitly registered through ModelBuilder, and entity types discovered through navigations from known entities. Merely declaring a class somewhere in the application does not automatically make it part of the model. Types can also be explicitly ignored.
Conventions establish initial mappings, and attributes or Fluent API refine them. The resulting metadata describes properties, keys, relationships, generated values, and provider-specific storage details. It is normally cached for reuse rather than rebuilt for every query. Building this model does not create or inspect all database tables. Migrations or a separately managed schema must make the actual database agree with the mappings that queries and updates use.
9. How are primary keys and generated key values configured?
EF Core normally recognizes Id or the entity-type name followed by Id as a primary key. Configure a different property with HasKey or a key attribute. A key identifies an entity for tracking and, in relational databases, normally maps to the table's primary-key constraint.
Generation is a separate decision. Non-composite numeric and GUID keys receive generation conventions; SQL Server commonly maps an integer key to an identity column. Other providers and key types use different strategies. Do not assume ValueGeneratedOnAdd alone creates every desired database default. For externally assigned identifiers, disable generation and supply a valid unique value. The fragment below belongs in CatalogContext.OnModelCreating, imports Microsoft.EntityFrameworkCore, and deliberately replaces the shared model's identity-key behavior.
C# example
10. When do you need a composite key instead of a single-column key?
A composite key identifies an entity by a combination of properties. It fits a record whose identity is naturally scoped, such as an order line identified by its order and line number. Neither value must be globally unique; the pair must be unique. Configure the complete key explicitly rather than marking each property with a separate [Key] attribute.
Composite keys also affect foreign keys and lookup calls: referencing relationships need the matching components in the configured order. A surrogate key plus a unique composite index is another design when simpler references are valuable. The SQL Server example uses an application-assigned line number. Include OrderLine in the context model and put the configuration statement in OnModelCreating.
The entity type needs integer properties named OrderId and LineNumber; the shown fragment imports Microsoft.EntityFrameworkCore.
C# example
11. How do conventions, data annotations, and Fluent API interact?
Conventions provide defaults, such as conventional key names. Data annotations override applicable conventions, and Fluent API has the highest precedence. When conflicting Fluent API calls configure the same setting, the later call generally wins. This makes configuration order important when applying several configuration classes.
Attributes keep simple mapping information next to the entity, but they cannot express every model feature. Fluent API supports richer relationships and provider-specific settings without putting persistence attributes on domain types. Avoid scattering conflicting settings across both mechanisms. In the shared SQL Server CatalogContext, this OnModelCreating fragment sets the effective maximum length to 120 even if Product.Name carries [MaxLength(80)]. The Fluent API import is Microsoft.EntityFrameworkCore; the hypothetical attribute uses System.ComponentModel.DataAnnotations.
C# example
12. How do nullability, maximum length, and decimal precision affect mappings?
Nullability determines whether a property can have no value. With nullable reference types enabled, string is required by convention and string? is optional; value types follow the same distinction through types such as int and int?. Enabling nullable reference types in an existing project can therefore generate unintended schema changes.
Maximum length influences a provider's column choice, while decimal precision is total digits and scale is fractional digits. SQL Server decimal(12,2) permits ten integer digits and two fractional digits. These settings are storage metadata, not a substitute for application validation. EF does not prevalidate every length or precision constraint. The fragment uses the shared product model and belongs in OnModelCreating, with Microsoft.EntityFrameworkCore imported.
C# example
13. What do Detached, Unchanged, Added, Modified, and Deleted mean?
An entity's state tells the change tracker what persistence work is intended. It is not independent proof that a row currently exists: another transaction may have changed or deleted it since the entity was loaded.
State | Meaning for this context | Normal SaveChanges action |
|---|---|---|
| Not tracked | None |
| Tracked, with no detected changes | None |
| Scheduled as new | Insert |
| At least one property is marked changed | Update changed properties |
| Scheduled for removal | Delete |
With snapshot tracking, changing a CLR property may not immediately update the displayed state until change detection runs. Successful saving normally accepts changes: inserted and updated entities become Unchanged, and deleted entities become detached. Setting a state manually can bypass the safer query-edit-save workflow, so use it deliberately.
14. How do you insert an entity and retrieve its generated key?
Create an entity, add it to the context, and save. Add normally changes tracking state without issuing the insert immediately. With the shared SQL Server model, Product.Id is an identity key, so leave it at its default value for a new product. EF manages temporary key information while tracking and assigns the store-generated key after a successful save.
Read the key from the entity, not from the integer returned by SaveChangesAsync. That return value counts written state entries. Saving may also insert newly reachable related entities, so adding an unexpected graph can do more work than adding one row. This async method-body fragment uses the configured CatalogContext db and CancellationToken ct from the shared setup.
C# example
15. How do you change one property of an entity already tracked by the context?
Load the entity with tracking enabled, change the allowed property, and save through the same context. Snapshot change detection compares the current value with the original value, so EF ordinarily updates only the changed mapped properties. Calling Update on an already tracked entity is unnecessary and can mark more properties as modified than intended.
The SQL Server fragment assumes the shared CatalogContext db, an integer id, a validated decimal newPrice, and a cancellation token ct. Authorization must already permit changing this product. A missing row is handled explicitly. A concurrency token, if configured, can additionally detect a conflicting change between reading and saving; tracking by itself does not supply lost-update protection for every property.
C# example
16. How does removing an entity differ from removing an item from a navigation collection?
Calling Remove for an existing entity marks that entity for deletion; SaveChanges issues the delete. Removing a child from a navigation collection instead severs a relationship. What happens next depends on the relationship's requiredness and configured delete behavior.
For an optional one-to-many relationship, EF commonly sets the child's foreign key to null and retains the child row. For a required relationship with normal orphan-delete behavior, severing the relationship can mark the child for deletion. A restrictive configuration can instead make the operation invalid. Removing a many-to-many association normally removes the join relationship rather than the other principal entity. Inspect the mapping and tracked states before assuming that collection removal always deletes a row, or that it never does.
17. What happens when SaveChangesAsync runs, and what does its return value represent?
With default settings, SaveChangesAsync detects tracked changes, creates the required database operations, executes them through the provider, and accepts successfully saved state changes. Generated values such as identity keys are propagated back into tracked entities. Relational providers normally give one save operation transactional all-or-nothing behavior.
The returned integer is the number of state entries written to the database. It is not a generated key, a count of SQL commands, or a universal count of physical rows affected by triggers and mapping arrangements. A return value of zero can simply mean nothing needed saving. Validation or database failures are reported through exceptions. Cancellation is passed to the provider; callers must await completion before reusing the context and should not treat cancellation alone as proof that no write occurred.
18. When should you use FindAsync, FirstOrDefaultAsync, or SingleOrDefaultAsync?
FindAsync looks up an entity by its primary-key values. It first checks tracked entities and only queries the database when necessary, so it can return an existing instance with local changes. It is not a freshness check or a general predicate query.
FirstOrDefaultAsync returns the first matching entity or null; use explicit ordering when several matches are allowed and the choice matters. SingleOrDefaultAsync returns one match or null and throws when multiple rows match. That expresses an expectation but does not replace a database uniqueness constraint. All three examples assume the shared SQL Server context and cancellation token. The key lookup uses integer id; the third example requires a string name, which is not unique in the shared schema and may therefore trigger the exception.
C# example
19. When does a LINQ query execute, and what happens if you enumerate it twice?
Composing an EF LINQ query normally builds an expression rather than fetching results. Execution begins when results are consumed, for example through enumeration, ToListAsync, SingleAsync, or an aggregate such as CountAsync. Keeping the query in a variable does not cache its results.
Materializing the same query twice normally sends two database queries. The database may change between them, and tracking can reuse object instances without eliminating the second query. In contrast, iterating an already materialized list twice uses its existing contents. The fragment assumes the shared SQL Server db and token ct. The count and list operations intentionally demonstrate two round trips; if both are derived from the same small materialized result, use its Count property instead.
C# example
20. What changes when an EF query crosses from IQueryable to IEnumerable?
IQueryable operators build an expression tree that EF's provider can translate. Switching to IEnumerable, commonly with AsEnumerable, makes subsequent standard LINQ operators run as .NET code. AsEnumerable itself does not materialize the results: enumeration still executes the database portion, then applies the later local operations.
This boundary can silently move filtering, ordering, or a row limit out of SQL. For example, placing Take(20) after a local filter does not limit the database query to twenty rows. Apply translatable restrictions and projection before crossing the boundary, and understand the remaining data volume. In EF Core 10, an untranslatable predicate normally throws; EF does not automatically evaluate arbitrary query filters locally. Limited client evaluation is allowed in the final projection.
21. How do you filter and order a query before materializing it?
Compose Where, OrderBy, ThenBy, and any result limit while the query remains an EF-backed IQueryable. The provider can then push supported predicates, ordering, and row limits into SQL. Calling ToListAsync first would make later LINQ operations work over data already loaded into memory.
Ordering should include a unique tie-breaker when taking a slice; sorting only by price leaves equally priced products without a deterministic relative order. The fragment uses the shared SQL Server CatalogContext db, decimal parameters minPrice and maxPrice, and token ct. Validate that the price range is sensible before querying. The fifty-row cap bounds the response, but a suitable index and an execution-plan check are still needed for a large catalog.
C# example
22. Why project a read-only query into a DTO rather than return full entities?
A DTO projection asks for the fields the consumer needs, reducing transferred columns and separating the response contract from the persistence model. It also avoids accidentally exposing writable properties or navigation graphs. Projection must occur before materialization to affect the database selection.
When a projection contains only scalar values and no entity instances, there is no entity for EF to track. Wrapping an entity inside an anonymous object or DTO does not prevent that entity from being tracked. The shared SQL Server example assumes a separately declared public sealed record ProductRow(int Id, string Name, decimal Price);, plus CatalogContext db and CancellationToken ct. DTO construction here does not require an Include, because the query directly selects the requested scalar fields.
C# example
23. When should you use AnyAsync rather than CountAsync?
Use AnyAsync when the required answer is whether at least one matching row exists. CountAsync expresses a different requirement: determining the number of matches. SQL Server commonly translates an existence check using EXISTS, allowing the database to stop once existence is established rather than calculate a complete count.
Use CountAsync or LongCountAsync when the number itself is needed, and do not materialize a list just to test whether it is empty. An existence check is only an observation at that moment. Checking whether a name exists and then inserting it does not enforce uniqueness under concurrency; a database constraint is needed for that. This fragment uses the shared SQL Server context, string name, and token ct.
C# example
24. What do async database methods and cancellation tokens actually provide?
Async database methods let the calling thread do other work while waiting for I/O. They can improve application responsiveness and throughput, but do not inherently make the database's query plan faster. Query-building methods such as Where need no asynchronous equivalent because they do not execute database I/O.
Pass the request or operation cancellation token to terminal async methods. EF forwards it to the provider, which determines how cancellation is honored. Cancellation is cooperative rather than an immediate guaranteed stop, and it is distinct from a command timeout. Await each operation before using that same context again; Task.WhenAll over two queries on one context is unsupported. The fragment uses the shared SQL Server context and an existing CancellationToken ct.
C# example
25. What is change tracking, and when is it useful?
Change tracking records entity identities, states, original values, and modified properties within a context. For a typical tracked query, EF materializes or reuses entity instances, detects later edits, and uses that information when saving. Relationship fixup also keeps tracked foreign keys and navigation references aligned.
Tracking is valuable for a read-edit-save operation, especially when only selected properties change or related entities must be coordinated. It also provides identity resolution, so one key maps to one tracked instance. It is unnecessary overhead for many scalar-only reports or read-only entity queries. Tracking is not a distributed cache, an audit history, or automatic concurrency protection. Another request can change the database without updating this context's objects, so keeping a context alive indefinitely is not a consistency strategy.
26. When should you use AsNoTracking, and what does it not guarantee?
Use AsNoTracking for entity results that will be read but not edited and saved through that context. EF avoids retaining those result entities in its normal change tracker, which can reduce allocation and tracking work. Editing the returned objects will not automatically make a later save persist those edits.
No-tracking does not mean immutable, authorized, transactionally isolated, or automatically faster in every case. Plain no-tracking queries also do not guarantee one object instance per repeated key; AsNoTrackingWithIdentityResolution offers that behavior without adding results to the context's tracker. Existing tracked entities are not detached by adding AsNoTracking to a later query. This fragment assumes the shared SQL Server context and token; projection would be preferable if only a few fields were needed.
C# example
27. How are one-to-many relationships represented in the model?
A one-to-many relationship has a principal and multiple possible dependents. The dependent holds the foreign key; optional navigation properties provide object references in either direction. A non-nullable foreign key normally makes the relationship required, meaning each dependent needs a principal, not that every principal must have a dependent.
The separate SQL Server blogging model below has one blog with many posts. Its classes require System.Collections.Generic. A blogging context can expose DbSet<Blog> Blogs, which also discovers Post through the collection navigation. The final statement belongs in that context's OnModelCreating, with Microsoft.EntityFrameworkCore imported. These navigations describe a relationship but do not automatically load related rows when the parent is queried.
C# example
28. What is the difference between a foreign-key property and a navigation property?
A foreign-key property stores the identifier linking a dependent to its principal, such as Post.BlogId. A navigation property represents the related object or collection, such as Post.Blog or Blog.Posts. In a relational mapping, the foreign key maps to a column; the navigation itself is not another database column.
An entity can know its foreign-key value while its navigation remains unloaded. Conversely, EF can model a relationship without a CLR foreign-key property by keeping a shadow foreign key in its metadata and tracker. For tracked entities, relationship fixup aligns known navigations and foreign-key values when relationships change or related entities materialize. Fixup does not query missing rows. A non-null navigation therefore does not prove that every related entity has been loaded.
29. How do Include and ThenInclude load related data?
Include requests a navigation alongside the root entity results. ThenInclude continues from that navigation to a deeper relationship. These operators describe the graph to materialize; they do not execute until the query is consumed. EF can load relationships through joins or split queries, depending on query configuration, so eager loading should not be defined as always one SQL command.
This SQL Server fragment uses a blogging context db with the Blog and Post model above, extended by a configured Post.Author reference to an Author entity. It also assumes integer blogId and CancellationToken ct. It loads that blog's posts and their authors. A missing blog yields null. Include only needed paths: large collections and sibling collection joins can substantially increase transferred data. Use a projection instead when the consumer needs a small read model.
C# example
30. How do you explicitly load a related collection?
Explicit loading starts with an entity and deliberately asks the context to load a particular navigation later. Use Entry(entity).Collection(...).LoadAsync for a collection or Reference(...).LoadAsync for a reference. This makes the database access visible at the call site and is useful when related data is needed only after a decision.
The SQL Server fragment assumes the blogging model from the relationship example, a tracking context exposing Blogs, integer blogId, and token ct. It normally performs one query for the blog and another for its posts. If only an aggregate is needed, the navigation entry's Query() can issue CountAsync without loading every post. Repeating explicit loads inside a loop can still create an N+1 query pattern.
C# example
31. What configuration is needed for lazy loading, and why can it surprise callers?
Lazy loading retrieves related entities when application code accesses an unloaded navigation. It is not enabled merely because a navigation property exists. Proxy-based loading requires the compatible Microsoft.EntityFrameworkCore.Proxies package, UseLazyLoadingProxies, and overridable navigation properties on inheritable entity classes. Another approach injects ILazyLoader and implements loading in navigation accessors.
The convenience hides I/O inside ordinary-looking property access. Iteration, logging, or serialization can trigger extra queries, including N+1 patterns; the associated context must also remain available. Eager loading or projections give a clearer boundary for API responses. This SQL Server configuration fragment assumes an existing DbContextOptionsBuilder<BloggingContext> optionsBuilder and external connectionString. For the earlier blogging model, make lazy-loaded navigations virtual and keep the entity classes unsealed.
C# example
32. What is the N+1 query problem, and how can a projection avoid it?
N+1 means one query loads a set of parent records, then an additional query runs for each parent to retrieve related data. Loading fifty blogs and lazily reading every Posts collection can therefore cause fifty-one database round trips. Explicit loading inside that same loop can produce the same problem.
Project the required related data in the database query instead. The SQL Server example uses the earlier Blog/Post relationship and an async method with blogging context db and token ct. It asks for each selected blog's post count; EF can translate the navigation aggregate into SQL without materializing post entities or accessing them later. Check actual command logs rather than guessing from the LINQ syntax, and keep the parent result bounded as well.
C# example
33. How do code-first development with migrations and reverse engineering differ in EF Core?
With code-first development, developers hand-code the entity model and mapping configuration, then create migrations to evolve the database schema. The model and reviewed migration history describe intended changes. This is sometimes loosely called model-first development, but it does not mean EF6's EDMX visual designer.
Reverse engineering starts with an existing database schema and scaffolds entity classes plus a context. It is useful when the database is owned or designed independently. Regeneration needs care so custom code is not overwritten; neither workflow automatically makes every schema change safe. The commands below are alternatives, not a sequence. They assume .NET 10, compatible EF Core 10 tools, Design and SQL Server packages, and startup configuration resolving ConnectionStrings:Catalog. The scaffold command avoids embedding configuration in the generated context.
Shell command example
34. What files does a migration add, and why is the model snapshot important?
Adding a migration creates a timestamped migration class containing Up and Down, a companion designer metadata file, and a context model snapshot if one does not exist. Later migrations update that snapshot. The snapshot represents the latest model known to migrations and is compared with the current model to generate the next change set.
This comparison is not a live comparison with the production database, so manual schema edits can cause drift that migration generation does not discover. Keep all migration files and the snapshot in source control. Review generated operations, especially renames that might be interpreted as drop-and-add. The command assumes Product.Description has just been added to a model that previously lacked it, with compatible EF Core 10 CLI and Design tooling. It generates files for inspection without applying them to a database.
Shell command example
35. Why should EnsureCreated not be mixed with a migrations-based lifecycle?
EnsureCreated creates a database and schema directly from the current model when appropriate. It is useful for disposable tests, prototypes, and other databases whose contents can be recreated. It does not establish the migration history needed for a migrations-based lifecycle and does not incrementally update an existing schema to match a changed model.
For relational applications that will evolve through migrations, begin with migrations rather than calling both APIs during startup. An initial migration applied after EnsureCreated can encounter tables that already exist. Moving a disposable database usually means recreating it through migrations; valuable production data requires a carefully planned baseline or transition, not an automatic drop. Provider capabilities differ, particularly for non-relational stores where migrations may not be available.
36. How can you inspect the SQL behind a LINQ query without confusing it with an execution plan?
ToQueryString returns a debugging representation of the SQL for an EF query without executing that query. It helps inspect predicates, joins, projection, and parameters, but is not guaranteed to be a directly executable script. Terminal operators can also change the command actually executed, so command logging is the better evidence for a real operation's SQL and timing.
An execution plan comes from the database optimizer and shows access strategies such as scans, seeks, and joins; ToQueryString does not produce one. Use SQL Server's plan tools to investigate a captured command with representative parameters and data. The fragment uses the shared context. Avoid exposing sensitive parameter values in production logs, and distinguish elapsed application time from database execution time.
C# example
37. How do you pass values safely into a raw SQL query?
Pass data values through EF's parameterizing APIs. With FromSql, interpolated values are captured as parameters rather than concatenated into SQL text. Supplying the interpolated expression directly preserves that behavior. Building a string first and sending it to FromSqlRaw can instead embed untrusted input in executable SQL.
Parameters represent values, not table names, column names, or SQL keywords. Dynamic identifiers require a trusted allowlist and appropriate handling; they cannot be made safe by a value parameter. This SQL Server example uses the shared dbo.Products mapping, decimal minimumPrice, context db, and token ct. An entity SQL query must return all required mapped properties, so the column list matches the shared product definition. Raw SQL entity results follow normal tracking rules unless configured otherwise.
C# example
38. What is optimistic concurrency, and when does EF Core report a conflict?
Optimistic concurrency checks whether data changed after it was read, rather than holding an application-level edit lock for the whole interaction. Configure a concurrency token so EF includes its original value in the update or delete predicate. If the expected row no longer matches, saving throws DbUpdateConcurrencyException.
On SQL Server, a rowversion column is a common automatically changing token. It is a binary version value, not a date or clock time; other providers need their own supported strategy. Without a token, a missing row may still cause a concurrency exception, but ordinary competing updates are not automatically detected. The configuration fragment requires adding public byte[] Version { get; set; } = []; to Product, creating the corresponding migration, and placing the statement in OnModelCreating. Resolve conflicts deliberately rather than blindly retrying stale values.
C# example
39. What transaction guarantee does one SaveChanges call normally provide?
With default configuration and a transaction-capable provider such as SQL Server, one SaveChanges call applies its database changes atomically: either all succeed or the operation is rolled back. EF creates a transaction when needed; a single database statement can already provide the necessary atomicity without an additional explicit transaction.
This guarantee does not automatically cover earlier reads, a second save call, another context, an HTTP request, or message publication. It also does not mean every business invariant is protected from concurrent transactions. An explicit transaction may be needed for several database steps, with suitable isolation and retry handling. If a transaction already exists, EF normally uses a savepoint before saving where supported. Non-relational providers and altered automatic-transaction settings require separate verification.
40. How would you write a bounded, ordered, read-only product search query?
Build the search on the database-backed query, validate inputs, specify a fully deterministic order, cap the result size, and project only response fields before materialization. A read-only query should not retain unnecessary entities in the tracker. The SQL Server example uses the shared CatalogContext db, token ct, a non-null string prefix, and integer requestedSize; its DTO is public sealed record ProductRow(int Id, string Name, decimal Price);.
The cap bounds returned records, not necessarily all database work. An appropriate index and actual execution plan still matter. SQL Server collation controls the case and accent semantics of the prefix comparison. This returns a first page only; a continuation design needs a cursor or deliberate pagination strategy rather than silently loading the remaining catalog.
C# example
Entity Framework Interview Questions and Answers for Intermediate
The intermediate answers connect implementation choices to tracking state, round trips, consistency, and deployment consequences. Provider-specific behavior is identified where relevant, and testing advice distinguishes application logic from actual database translation and execution.
41. How do you organize a large model with IEntityTypeConfiguration classes?
An IEntityTypeConfiguration<T> class keeps one entity's mapping separate from the context: keys, property facets, indexes, and relationships remain together. Apply it in OnModelCreating, either explicitly or through assembly scanning. Fluent configuration overrides conventions and annotations; later conflicting fluent calls win.
Assembly discovery does not define application order, so configurations should not depend on another configuration running first. Put deliberate cross-model overrides after discovery, or apply conflicting configurations explicitly. Keep tenant values on the context rather than capturing request-specific data in a reusable configuration. Splitting the files improves maintainability; it does not create separate EF models or database transactions.
C# example
42. When are backing fields and shadow properties useful?
A backing field stores a mapped value inside the entity while allowing the public API to enforce domain rules. EF can populate the field without invoking a validating setter during materialization. A shadow property has no CLR property or field; its tracked value lives in EF's change tracker. Shadow foreign keys and persistence-only timestamps are common uses.
These are different kinds of hidden state. A field remains part of a detached object, but shadow state does not travel with that object. Use EF.Property<T> inside LINQ to query a shadow property, and Entry(...).Property(...) for tracked access. Neither mapping automatically generates timestamps or enforces domain authorization.
C# example
43. How do you configure a unique or composite index, and what must the database enforce?
Use HasIndex with one property or an anonymous object containing multiple properties, then add IsUnique when duplicates must be rejected. Column order matters for composite-index access patterns. The example enforces one SKU per tenant, not globally unique SKUs.
The deployed database index must enforce uniqueness; an application-side existence check races with concurrent requests. Review the migration and handle duplicate-key failures as expected business conflicts. Requiredness, collation, normalization, and null treatment affect what the database considers equal. SQL Server normally adds an IS NOT NULL filter for nullable columns in a unique index. Do not assume another provider has identical null or case rules, or that index configuration takes effect before deployment.
C# example
44. What are value converters, and when is a value comparer also needed?
A value converter maps a property's model CLR type to a provider CLR type and back, such as an enum stored as text. Conversion controls persistence representation; it does not automatically teach the query translator how to inspect arbitrary members of the converted object.
A value comparer supplies equality, hashing, and snapshot behavior for change tracking. It is needed when default comparison cannot detect meaningful changes, especially an in-place mutation of a mutable collection serialized into one column. Deep comparison requires a matching deep snapshot; otherwise the original and current values can reference the same mutated object. Immutable value types usually avoid that problem. Standard enum-to-string conversion normally needs no custom comparer.
C# example
45. How do owned entity types differ from complex types in EF Core 10?
Owned types are entity types: they have identity, often through a hidden key tied to the owner, and participate in an ownership relationship. They can map to another table and can contain entity navigations. The same owned instance cannot belong to multiple owners or ownership navigations.
Complex types represent values without independent identity. They are embedded in their containing entity, not queried through their own DbSet, and cannot contain entity navigations. EF Core 10 supports complex-type JSON mapping, optional complex properties, and struct values, subject to documented limitations. Optional complex types require at least one required member. Prefer complex types for addresses or money; prefer owned entities when entity-style ownership and mapping capabilities are needed. Sharing a mutable CLR reference still has normal C# aliasing consequences.
C# example
46. When should a query result use a keyless entity type?
Use a keyless entity type when a database view or SQL result has no trustworthy unique key and needs reusable model mapping. Aggregated reports are a typical example. Configure it explicitly with HasNoKey; EF does not discover keyless types by convention and never tracks them for insert, update, or delete.
ToView treats the target as a read-only query source; it does not create that view. Its definition must be deployed separately. Keyless types also have restricted relationships and cannot be principals. Do not invent a nonunique key merely to silence model validation, because identity resolution could then merge different rows. For an ordinary one-off LINQ projection, an anonymous type or DTO usually needs no entity mapping at all.
C# example
47. How do you model a many-to-many relationship with payload on the join row?
Give the join row an explicit entity class when the association has data of its own, such as enrollment date or permission scope. Model two one-to-many relationships and use the pair of foreign keys as a composite primary key when each pair may occur only once. Skip navigations are optional; the join entity remains useful for directly reading and updating its payload.
For Enrollment, the payload belongs to the student-course association, not to either endpoint. Adding a row enrolls the student; deleting that row removes the association without deleting the student or course. Validate access to both endpoints and select delete behavior intentionally. If historical enrollments require repeated pairs, introduce a separate identity and a business-specific uniqueness rule instead.
C# example
48. How do you configure a one-to-one relationship and identify its dependent?
The dependent is the entity holding the foreign key. In a customer-profile relationship, CustomerProfile.CustomerId points to the principal customer's key. Configure both reference navigations and use HasForeignKey<CustomerProfile> to remove ambiguity. The relational schema needs a unique foreign-key value to prevent two profiles referencing the same customer; EF's one-to-one mapping normally creates that unique index.
A nonnullable foreign key makes the relationship required from the dependent to the principal: every profile must have a customer. It does not ensure that every customer has a profile. That reverse existence rule requires application workflow or another database mechanism. Shared-primary-key mappings are also possible, but should reflect the domain rather than serve as a workaround for unclear ownership.
C# example
49. How do cascade delete, client-side deletion, and restrictive foreign keys differ?
Database cascade deletion removes dependent rows when their principal is deleted, even if those rows were never loaded into EF. DeleteBehavior.Cascade also tells EF how to handle tracked dependents. ClientCascade instead cascades through tracked entities while leaving a noncascading database foreign key; deleting the principal without loading its dependents can therefore fail.
For optional relationships, client-side nulling can clear tracked foreign keys rather than delete rows. A restrictive database foreign key rejects a principal deletion while dependent references remain; it does not automatically remove children. Exact RESTRICT versus NO ACTION SQL depends on the provider. Review loaded and unloaded cases, required versus optional foreign keys, and database cascade-path limits. Soft deletion is an update, so ordinary database delete cascades do not implement a soft-delete policy.
C# example
50. How do you safely change an optional relationship to a required one?
Treat the change as a data migration as well as a model change. First identify null foreign keys and invalid references, then decide a legitimate remediation: assign a real principal, archive the row, or explicitly remove it under an approved retention rule. A fabricated default foreign-key value can corrupt meaning or violate the constraint.
For a rolling deployment, first release code that stops writing nulls while remaining compatible with the nullable column. Backfill existing rows, verify completion, and only then enforce NOT NULL and the foreign key. Finally align the CLR property and relationship configuration. Review generated SQL, locking, and rollback compatibility on production-sized data. Changing nullable-reference annotations alone can generate unexpected schema changes; it is not evidence that existing data meets the new invariant.
C# example
51. What can make filtered Include return unexpected entities in a tracking context?
A tracking query does not build an isolated object graph. Relationship fixup connects its results with related entities already tracked by the context, including entities that fail the new filtered Include. For example, previously loaded cancelled orders can reappear in a customer's collection even when the current include asks only for open orders.
EF also considers the filtered navigation loaded, so explicit or lazy loading will not automatically fetch the missing remainder. A fresh context or no-tracking query avoids contamination from earlier tracked results. For a response requiring a precise subset, project directly into a DTO rather than treating a tracked collection as a complete filtered report. The include filter limits related rows; it does not itself filter which customers are returned.
C# example
52. When should you choose AsSplitQuery over a single query?
Choose a split query when joining multiple sibling collections produces a large cross product or repeats expensive principal columns. A blog with many posts and many contributors is a classic case. Splitting retrieves the principal rows and included collections separately; it is not one query per principal and therefore is not the usual lazy-loading N+1 pattern.
The trade-off is additional round trips, possible buffering, and a consistency window between commands. Concurrent changes can make the assembled graph differ from a single-query snapshot. Use an appropriate snapshot or serializable transaction when that consistency matters, and measure its cost. Projection may remove the need to include full collections. EF Core 10 improves split-query ordering consistency, but explicitly unique ordering remains important for predictable pagination.
C# example
53. How does relationship fixup keep foreign keys and navigations aligned?
Relationship fixup synchronizes tracked foreign-key values with reference and collection navigations. When EF materializes a post whose blog is already tracked, it connects the objects. When a tracked relationship changes, change detection updates the corresponding foreign key and opposite navigation.
Changing one navigation is normally sufficient; manually changing both ends and the foreign key risks contradictory states. With ordinary snapshot tracking, plain CLR assignments may not be reconciled until change detection runs. SaveChanges normally triggers detection, and DetectChanges can make the result visible earlier for diagnostics. Fixup does not query for missing entities and is not lazy loading. Removing a dependent from a required relationship may trigger orphan deletion, whereas an optional relationship can normally be severed by nulling its foreign key.
C# example
54. How does AsNoTrackingWithIdentityResolution differ from tracking and plain no-tracking?
A tracking query uses the context's identity map, returning the same instance for a repeated key and retaining state for later updates. If that key is already tracked, a query normally reuses the instance without replacing its current values. Plain AsNoTracking avoids that state but may materialize multiple objects for the same database row.
AsNoTrackingWithIdentityResolution uses a separate temporary tracker during enumeration. Repeated keys share an instance within that result, but the entities are not attached to the application's context for saving. The temporary identity map has a cost and is not a cross-query cache. Choose it for read-only graphs with substantial duplication. A projection containing entity instances can still track those instances by default; an anonymous wrapper alone does not disable tracking.
C# example
55. How do you apply an authorized disconnected update without overwriting unrelated fields?
Load the target through an authorization-scoped query, then copy only fields the operation permits. A request DTO should not decide tenant ownership, audit values, keys, or unrelated relationship changes. Updating one tracked property preserves other fields because EF detects property-level changes rather than marking the whole incoming object modified.
The query must use trusted identity information, not a tenant or owner supplied in the request body. Handle a missing or unauthorized row without leaking its existence. Validate the new value before applying it. If stale clients must not overwrite a newer edit to the same field, also carry and enforce a concurrency token; selective assignment alone does not solve concurrency. Assume ownership is immutable during this operation, or protect changes to it with a token or database predicate.
C# example
56. Why can attaching or updating an entire disconnected graph be dangerous?
Attach and Update traverse reachable entities, so accepting an arbitrary client graph can affect far more than the root. With generated keys, nodes lacking a key may be marked for insertion; Update can mark existing nodes modified and overwrite fields that the client never intended to edit. A foreign key in the graph is not proof that the caller owns the referenced row.
A context also permits only one instance per entity key, so duplicated objects can cause identity conflicts. Prefer operation-specific DTOs and a short-lived context: load the authorized aggregate, validate every relationship change, and explicitly reconcile additions, edits, and deletions. An omitted child is not automatically an authorized deletion. Inspect intended states before saving, and retain concurrency protection when clients can submit stale data.
57. How would you resolve a DbUpdateConcurrencyException without silently losing an edit?
A concurrency exception means an expected row was not updated or deleted, commonly because its token changed or the row disappeared. Preserve the attempted edit and compare three versions: original values seen by the client, current proposed values, and current database values. If the row was deleted, treat that as a distinct conflict rather than silently recreating it.
Choose a documented policy: reject and return current data, let the user merge, or merge nonconflicting fields automatically. After an approved merge, update original values to the latest database snapshot before retrying, while keeping the chosen proposed values. A retry must be bounded because another conflict can occur. Blindly refreshing the token and resaving implements client-wins and can erase another user's work; transient connection retries do not resolve business conflicts.
C# example
58. How does ExecuteUpdateAsync differ from modifying tracked entities and saving?
ExecuteUpdateAsync sends a set-based update immediately, without loading entities or waiting for SaveChanges. Its setter expressions operate on database values, so a price adjustment can happen in one SQL statement. A tracked update instead retains entity state and uses SaveChanges for persistence and its associated hooks.
The bulk operation does not synchronize tracked instances or automatically apply configured concurrency tokens. Mixing it with stale tracked entities can later overwrite its changes. Use a fresh context, reload affected entities, or avoid mixing the two paths. Add authorization and concurrency conditions explicitly when needed, and check the affected-row count. Separate bulk calls are not automatically one atomic unit; wrap related commands in an explicit transaction. Domain validation and audit behavior implemented only in SaveChanges must be handled separately.
C# example
59. How do you make an ExecuteDeleteAsync operation bounded and safe?
Define the authorized population and an explicit maximum batch size before issuing a physical delete. A tenant predicate, retention cutoff, and stable selected IDs make the operation auditable and bounded. Reapply ownership and eligibility conditions in the delete itself: rows can change between selection and execution. The example never deletes more than the selected 100 IDs.
ExecuteDeleteAsync is immediate and bypasses tracked deletion rules, including soft-delete logic in SaveChanges. Global query filters normally participate, but they are not a substitute for an authorization boundary. Check the returned count, understand database cascades, and stop for unexpected results. If an exact immutable snapshot is required, use suitable transaction isolation or version predicates rather than assuming the preliminary query locks the selection. Do not disable tenant filters merely to include archived rows.
C# example
60. How does SaveChanges batching differ from set-based updates?
SaveChanges batching reduces round trips by sending multiple modification statements together. It does not turn individually tracked changes into one set-based update: changing 500 entities still normally requires a separate update statement for each entity. EF must also materialize or attach those entities, track their state, and detect changes.
ExecuteUpdate can express one database update across all matching rows, avoiding that per-entity pipeline. It is a good fit for uniform arithmetic or status changes, but not a transparent replacement for entity-level validation, concurrency handling, or save hooks. Batch sizes depend on the provider; SQL Server defaults to a maximum of 42 statements per batch. Larger batches are not automatically faster. Measure command count, network latency, server execution time, and memory under realistic data volumes before tuning.
61. How do explicit transactions and savepoints interact with SaveChanges?
A single SaveChanges is normally atomic when the provider supports transactions. An explicit transaction is useful when multiple saves or other database commands must succeed together. Saving inside that transaction does not commit it; the caller owns the final commit or rollback.
With a supported relational provider, EF normally creates a savepoint before saving into an existing transaction and rolls back to it on failure. That preserves earlier database work in the transaction, but it does not rewind every CLR object or accepted tracking state. SQL Server disables these automatic savepoints when MARS is enabled, even if MARS is not actively used; a failure can leave the transaction uncertain. Keep transactions short, dispose them reliably, and integrate user-managed transactions with the execution strategy when transient retries are enabled.
C# example
62. How do you combine an execution strategy with a user-managed transaction?
When retries are enabled, run the entire user-managed transaction inside the delegate passed to the context's execution strategy. A failure may replay every operation in that delegate, not merely the failed statement. Create the working context and transaction inside it so each attempt starts with clean tracking state.
Keep external effects such as email or payment calls outside the replayed block; use an outbox when they must follow a commit reliably. A lost connection during commit creates an unknown outcome, so ordinary retries are not an exactly-once guarantee. Use stable operation identifiers and database uniqueness, plus outcome verification or an appropriate ExecuteInTransactionAsync verification strategy. The example uses client-assigned keys to prevent duplicate inserts; a duplicate-key exception after an uncertain commit still needs explicit reconciliation.
C# example
63. Which LINQ expressions run on the server, and where is client evaluation allowed?
EF translates supported expression-tree operations into provider-specific database operations. Translation depends on the expression's shape and the provider, not simply whether it was written in LINQ. Ordinary captured values can become query parameters without making the query a client-side scan.
EF Core permits client evaluation in the top-level projection. A custom formatting method there can process the selected data after retrieval. An untranslatable filter, join condition, or ordering expression normally throws rather than silently downloading the entire table. AsEnumerable, AsAsyncEnumerable, or materialization can intentionally move subsequent work to the client, so apply selective server filters and projections first. Inspect generated SQL and test the production provider. A locally successful LINQ-to-Objects test does not establish EF translation or acceptable database cost.
C# example
C# example
64. How do you express an outer join, and why does query shape matter for translation?
A left outer join returns every row from the left side and matching rows from the right, with nulls when no match exists. EF Core 10 recognizes the .NET 10 LeftJoin operator, which expresses that intent directly. The older equivalent uses GroupJoin followed immediately by flattening with SelectMany and DefaultIfEmpty.
Shape matters because translation recognizes supported patterns, not arbitrary mathematical equivalence. Rearranging the older grouping and flattening can prevent recognition, and some correlated shapes require APPLY, which is not supported by every database. A right-side predicate placed after a left join can also remove unmatched rows, effectively changing its meaning. Keep eligibility conditions on the joined source when unmatched left rows must survive, and make the projected right-side values nullable.
C# example
65. When can GroupBy translate to SQL, and when is grouping performed after retrieval?
Grouping translates naturally to SQL when the result reduces each group to scalar keys and supported aggregates, such as count, sum, minimum, or maximum. Filtering those aggregated groups can translate to HAVING. The example returns one row per customer instead of materializing every order into a CLR grouping.
An IGrouping<TKey,TElement> is a .NET collection abstraction, not a single relational value. Some grouping shapes can be reconstructed by EF after ordered rows are retrieved, while unsupported compositions can still throw. That reconstruction is different from SQL aggregation and may transfer substantially more data. Inspect the generated SQL rather than assuming every GroupBy reduces rows on the server. Aggregate support, numeric behavior, and complex grouping translations vary by provider, so test the actual query against the production database engine.
C# example
66. How can SQL null semantics and database collation change LINQ query results?
SQL comparisons use three-valued logic: a comparison involving null can be unknown, and WHERE retains only true rows. EF normally adds null compensation where needed to approximate C# semantics. For example, a nullable value that differs from a nonnullable value may require an extra IS NULL branch. Enabling relational null semantics changes that behavior and can change results.
String equality and ordering use the database or column collation, not necessarily .NET's comparison rules. Case sensitivity, accents, and culture can therefore differ between providers or databases. Choose the intended collation in the schema where possible. Applying ToLower or a different explicit collation in each query can undermine index use. Test null, case, and accent edge cases against the actual configured database rather than an in-memory substitute.
SQL example
67. How do you implement keyset pagination with a fully unique ordering?
Use the last row's complete ordering key as the cursor, then query rows strictly after it. Ordering only by a timestamp is insufficient when several rows share that timestamp; add a unique key and use the same pair in the cursor predicate. The example sorts ascending by creation time and ID, then selects the next 20 records.
Create a matching composite index, including the tenant prefix when appropriate. Validate cursor values and bind them to the same tenant, filters, and sort direction; a cursor must not become an authorization bypass. Keyset pagination avoids progressively scanning skipped offsets and resists shifts caused by earlier deletions, but mutable ordering fields can still move rows. It supports next/previous navigation well, not arbitrary page numbers. For backwards navigation, reverse both comparisons and ordering consistently.
C# example
68. When does streaming reduce memory, and what can still cause EF to buffer results?
Streaming lets the application process rows incrementally instead of retaining a complete List. It reduces application memory when processing does not accumulate results, especially with a scalar DTO projection or no-tracking query. Tracking every entity can still grow the context's state even when enumeration is streamed.
Streaming is not a promise of zero buffering. A retrying execution strategy buffers internally so it can replay results consistently; split queries may buffer earlier result sets unless the provider can keep multiple readers active. A later ToList adds its own buffer. Keep the context and connection alive until enumeration finishes, honor cancellation, and dispose the enumerator on early exit. Avoid issuing concurrent queries on the same context while a reader is active. For long exports, bounded pages can offer simpler resource control.
C# example
69. What restrictions apply when composing LINQ over FromSql or a stored procedure?
FromSql starts directly from a DbSet, and subsequent LINQ composition treats the SQL as a subquery. The supplied SQL must therefore be composable: typically a SELECT without a trailing semicolon or disallowed query-level syntax. Entity results must provide all mapped properties using their mapped column names.
SQL Server stored-procedure calls are not composable. Enumerate immediately or switch to AsAsyncEnumerable before client-side processing; do not append a server-side Where over EXEC. Avoid model filters that would require SQL composition over that call. Pass values through interpolated FromSql or explicit parameters, not a prebuilt interpolated raw SQL string. Table and column names are not parameter values; dynamic identifiers require strict allowlisting. Authorization constraints must be included safely in the SQL or enforced by the procedure itself.
C# example
70. How do you produce and review an idempotent migration script for deployment?
Generate an idempotent script from the intended migration set and provider, then review it as a deployment artifact. The script checks the migrations history table and applies migrations that are missing. That handles different known migration starting points; it does not detect arbitrary schema drift or make destructive operations safe.
Review drops, type narrowing, data transformations, defaults, constraints, long-running index work, and transaction boundaries. Test against a restored representative database and verify the application remains compatible during rollout. Plan recovery and use a deployment identity with schema permissions rather than granting them to the runtime application. Serialize deployments: an external SQL runner does not receive EF's migration-lock orchestration or invoke UseSeeding delegates. SQLite cannot generate these idempotent scripts because it lacks the required procedural conditional language.
Shell command example
71. How do you avoid data loss when a generated migration treats a rename as drop-and-add?
EF compares the current model with the snapshot, but cannot reliably distinguish every rename from a dropped property followed by a new one. Review the generated migration before deployment. If the change is a true column rename, replace the destructive drop/add pair with RenameColumn and provide a corresponding reverse operation when reversal is valid.
A rename accompanied by a transformation may need a new nullable column, an explicit backfill, validation, and only then removal of the old column. Preserve data until the new representation is verified. Keep the model snapshot aligned with the final model, and inspect the provider's generated SQL. Once a migration has been applied to shared databases, do not casually rewrite its history; add a corrective migration. A reversible schema operation does not automatically make lost data recoverable.
C# example
72. How do you preserve customizations when re-scaffolding an existing database?
Treat reverse-engineered files as generated output. Put additional behavior in separate partial entity classes and place model overrides in the context's OnModelCreatingPartial implementation. The generated context calls that hook after its generated mapping, allowing intentional overrides without editing code that will be replaced.
Keep scaffolding options and naming choices reproducible, commit the current state, and inspect the diff after using --force. For systematic generation changes, customize supported templates rather than repeatedly hand-editing output. Do not embed production credentials in generated source; resolve the connection from configuration and suppress generated OnConfiguring when appropriate. Database schema inspection cannot recover every domain concept, such as application validation or all inheritance choices. Rebuild and run provider integration tests after regeneration so removed columns, changed nullability, and conflicting overrides are caught.
C# example
73. How do UseSeeding, UseAsyncSeeding, and HasData serve different purposes?
UseSeeding and UseAsyncSeeding run custom initialization logic during the corresponding EF database-creation or migration operations. They can inspect existing data and are protected by EF's migration locking. Implement equivalent synchronous and asynchronous paths because EF tooling uses the synchronous delegate; configuring only the async path leaves that execution path unseeded. Make the logic repeatable and avoid uncontrolled external side effects.
HasData instead describes model-managed data with fixed keys. Migrations compare that metadata and generate insert, update, or delete operations; it suits stable reference data, not arbitrary runtime initialization. Changing seeded keys can become delete-and-insert behavior. External execution of a generated SQL migration script runs neither seeding delegate, so required custom initialization needs a separate deployment step. Never generate nondeterministic values inside model-managed data.
C# example
74. How do global query filters implement soft deletion and tenant-scoped reads in EF Core 10?
Global query filters add a default predicate to queries for an entity type. A soft-delete filter excludes archived rows, while a tenant filter uses trusted tenant identity stored on the context. EF Core 10 supports named filters, allowing the soft-delete filter to be disabled without removing tenant isolation.
Calling parameterless IgnoreQueryFilters disables all filters and should be restricted to explicitly authorized operations. Filters are query behavior, not a complete security boundary: raw database access, detached writes, and privileged bypasses need separate controls. Required navigations can introduce inner joins, so filtering a related principal may also remove dependent results unexpectedly. Test the entity graph, not only the root query. A pooled context additionally needs correct per-lease tenant initialization; stale tenant state must never carry into another request.
C# example
75. How do you convert a tracked delete into a soft delete without assuming bulk deletes follow it?
Intercept the save pipeline or override it to replace a tracked Deleted state with an update to the soft-delete flag. Do not merely mark the entire entity modified, because that can write unrelated values from a stale or partially populated entity. The fragment resets the entry to unchanged, then marks only IsDeleted modified.
Run the shared logic on both synchronous and asynchronous saving paths, after change detection, and retain normal concurrency checking. Define what happens to dependents: cascade processing may already have marked them deleted, and a root-only conversion does not automatically implement aggregate-wide soft deletion. ExecuteDeleteAsync bypasses SaveChanges, so this policy will not intercept it. Use an explicit authorized ExecuteUpdateAsync path for bulk soft deletion, with separate audit and concurrency rules.
C# example
76. How do you enforce tenant ownership on writes as well as reads?
Read filters are only one layer of tenant isolation. Derive tenant identity from authenticated server-side context, set it on new entities, and prevent ordinary updates from changing it. For updates and deletes, query the authorized row or include the tenant predicate directly in a set-based command. Never treat an incoming entity's tenant field as evidence of ownership.
An attached stub can bypass the read path, and SaveChanges does not automatically add a global query filter to its update predicate. Reject unverified detached writes or enforce ownership through concurrency conditions and database controls. Validate that related rows belong to the same tenant; composite tenant-aware foreign keys can enforce that invariant. If ownership can change concurrently, protect the check-to-write interval with a token or suitable database predicate. Test cross-tenant IDs, relationship reassignment, administrative bypasses, and bulk operations explicitly.
77. How do you log useful database diagnostics without exposing sensitive values?
Capture command duration, command count, failures, connection activity, and correlation identifiers before exposing parameter values. EF's normal logging omits sensitive data values; keep EnableSensitiveDataLogging disabled outside tightly controlled diagnostic use. Production applications usually benefit from structured Microsoft.Extensions.Logging integration rather than unrestricted console output.
Disabling sensitive-data logging is not complete redaction. Raw SQL can contain literals, query tags appear in SQL, exception text can include data, and connection strings can carry credentials. Keep tags static, scrub exported traces, limit retention, and restrict log access. Use command logs to detect N+1 patterns and slow operations, then inspect database execution plans for the expensive queries. ToQueryString helps inspect translation but is not proof of executed timing, row counts, or the actual execution plan.
C# example
78. Why can InMemory or SQLite tests pass when the production provider fails?
The InMemory provider is not a relational database: it does not prove SQL translation, relational constraints, or production transaction behavior. SQLite is relational, but its type system, SQL features, collations, migrations, and concurrency support differ from SQL Server or PostgreSQL. Passing either substitute can therefore hide production failures.
For example, SQLite lacks SQL Server-style database-generated rowversion tokens and has limitations around schemas, sequences, and some numeric or temporal operations. Test important queries, migrations, uniqueness constraints, and concurrency races against an isolated database using the production provider and representative configuration. Fast unit tests remain valuable for pure business rules or repository consumers, but keep their claims narrow. A hand-written SQLite SQL fixture only checks that SQL scenario; it does not establish that EF generates equivalent SQL from a given LINQ expression.
79. When does a repository abstraction help testing, and when does it hide important EF behavior?
A repository helps when the application needs a stable domain-oriented boundary and tests should substitute completed data operations rather than emulate EF's query provider. Methods such as GetOpenOrdersAsync can return DTOs, letting service tests focus on decisions and error handling. Keep the actual repository implementation covered by integration tests.
A generic wrapper that simply exposes IQueryable often moves EF dependencies rather than hiding them: callers still choose translation-sensitive expressions, tracking behavior, and materialization timing. Conversely, an overly narrow wrapper can hide transactions, includes, concurrency handling, or efficient projections that the application genuinely needs. DbContext already supplies change tracking and a unit-of-work boundary. Add another abstraction for a concrete architectural benefit, not just to mock DbSet. A mocked result cannot validate generated SQL, provider semantics, or commit behavior.
80. How would you implement an authorized price update with a SQL Server rowversion token?
Configure a SQL Server rowversion property as a concurrency token and database-generated value. Load the product through a trusted tenant predicate, validate the requested price, and copy only that property. Set the token's original value to the client's supplied token, not the freshly loaded token; otherwise an earlier conflicting edit would be missed.
The generated update checks the key and original rowversion. If another writer changed or deleted the row, return a conflict rather than blindly retrying with a refreshed token. Return the new token after success for the client's next edit. The example assumes tenant ownership cannot change during the operation, a fresh tracking context, and an authenticated tenant with price-edit permission. Validate token encoding and length at the API boundary. Rowversion is SQL Server-specific and is not a timestamp or an authorization credential.
C# example
Entity Framework Interview Questions and Answers for Experienced
The experienced answers explain the boundaries of EF guarantees and the evidence needed to choose an approach. They separate context behavior from database isolation, retries from idempotency, and successful test doubles from validation against the intended provider.
81. How would you investigate a query that became slow after a data or deployment change?
Separate database execution time from connection waits, materialization, and response serialization. Compare the deployment's generated SQL and parameter types with the previous version, using representative slow and fast parameter values. On SQL Server, Query Store can reveal when plans or runtime statistics changed; the actual plan exposes estimate errors, scans, spills, and excessive reads.
Check data growth, statistics, indexes, blocking, and changed query shape before prescribing a fix. Capture command counts to detect newly introduced N+1 queries. Keep sensitive parameter values out of ordinary production logs. A stable query tag connects an endpoint to its SQL without including user input.
This EF Core 10 fragment assumes an existing AppDbContext db with Orders and an integer customerId; ct is a cancellation token.
C# example
82. How do you decide whether to change a LINQ predicate, an index, or both?
Start with the actual predicate and execution plan, not a rule that every slow query needs another index. Functions on an indexed column can prevent an efficient seek; a semantically equivalent range predicate may make an existing index useful. Conversely, a selective predicate still needs an appropriate access path. Composite index order should match the important filters and ordering, while extra indexes increase write and storage costs.
For SQL Server, this example filters a UTC DateTime CreatedAt column by a half-open year range rather than extracting its year. It assumes db.Orders, a validated year from 1 through 9998, and Microsoft.EntityFrameworkCore. The index is model configuration inside OnModelCreating, not executed per request. Validate the plan and boundary results before deployment.
C# example
83. How would you redesign an endpoint that loads a large object graph and times out?
Reduce the endpoint's contract before changing how its oversized graph is loaded. A list page usually needs a bounded projection, not every order, line, payment, and customer entity. Move large child collections to separately paginated detail endpoints, avoid lazy-loading loops, and remove unused binary or text columns.
Use split queries only when the required shape still contains collection includes: they avoid certain join multiplications but add commands, buffering, and consistency considerations. They do not make an unbounded result small. Measure rows, bytes, round trips, allocations, and end-to-end latency.
This SQL Server EF Core 10 query assumes integer order IDs, Total, and an Items collection. The cursor afterId is validated, and db is request-scoped; the scalar-only projection does not track entities.
C# example
84. How do you maintain a consistent view of data across the multiple commands of a split query?
A split query can observe changes committed between its commands. On SQL Server, a transaction using snapshot isolation provides a transaction-level versioned view; serializable isolation is another option with potentially greater blocking. Ordinary read-committed isolation, including statement-level read-committed snapshot, does not promise the same snapshot across all commands.
The example assumes SQL Server has ALLOW_SNAPSHOT_ISOLATION enabled, db.Orders has an Items navigation, and retrying execution strategies are not enabled. With retries enabled, execute the whole transaction through the configured execution strategy. Import System.Data and Microsoft.EntityFrameworkCore. Keep the transaction short: enumerate the complete result before committing, and perform response serialization afterward. Assess version-store or locking costs under production-like concurrency rather than choosing stronger isolation automatically.
C# example
85. When do explicit compiled queries help, and what are their limitations?
Explicit compiled queries help a frequently executed, stable query when profiling shows meaningful EF query-processing overhead. They bypass the normal query-cache lookup, but cannot rescue poor SQL, an absent index, or excessive network traffic. Compare them with ordinary parameterized LINQ under realistic load before adding maintenance complexity.
A compiled delegate supports one EF model and simple scalar parameters. It can run concurrently with separate context instances, never parallel operations on one context. Do not capture a request-scoped context or tenant service in a static delegate.
This class-level field assumes an EF Core 10 AppDbContext with Orders, integer Id, decimal Total, and Microsoft.EntityFrameworkCore. Enumeration still executes database I/O; callers supply a live context and can apply WithCancellation(ct) when consuming the async sequence.
C# example
86. How can dynamically built expression trees defeat EF and database query caching?
An expression tree containing a different literal constant for every request can create a new query shape each time. That increases EF compilation work and may produce distinct SQL texts that fragment the database's plan cache. Dynamic sorting and optional predicates also create legitimate shapes, so parameterization alone does not make every query identical.
Prefer captured scalar values and a bounded set of composable predicates. Allowlist property names and operators; values belong in parameters, not concatenated SQL. Watch EF query-cache metrics after warm-up, then inspect database plans because parameter reuse can still produce parameter-sensitive performance.
This fragment assumes SQL Server, EF Core 10, an existing db.Orders, and a validated decimal requestedMinimum. It deliberately uses ordinary LINQ rather than constructing an Expression.Constant from request data.
C# example
87. When can a compiled model reduce startup cost, and what trade-offs must you check?
A compiled model targets first-use model initialization, especially for a context with hundreds of entity types and relationships. It is different from a compiled query and does not optimize the database's execution plan. Measure cold-start time separately from steady-state requests to establish whether model construction is actually significant.
Generate the model during development or the build, configure the application to use the generated instance, and regenerate it after every model change. EF Core's documented limitations include global query filters, lazy-loading and change-tracking proxies, and custom model-cache-key factories. Those exclusions can rule it out for an application regardless of benchmark gains.
The illustrative command assumes a .NET 10 project with AppDbContext, EF Core 10 design-time services, and matching dotnet-ef tooling. It is a generation command, not a database migration or an execution result.
Shell command
88. What tenant-state and connection-state hazards arise with DbContext pooling?
A pooled context is reused across requests, so constructor initialization and OnConfiguring cannot establish changing tenant state for each lease. A scoped factory must obtain the authenticated tenant from a trusted source and assign it before exposing the context. Every lease must overwrite custom state, including failure paths; missing tenant identity should fail closed rather than mean all tenants.
EF resets its own context state, not every field or every underlying driver setting. Manually opened connections, transactions, and session settings need explicit ownership and cleanup before return. Never retain a context beyond its lease or share it concurrently.
Context pooling and connection pooling are independent. A tenant-specific connection string, session-based row-level security, or tenant-dependent model needs a deliberately tested design, not a mutable singleton configuration. If isolation cannot be demonstrated with alternating-tenant and exception-path tests, use ordinary short-lived contexts instead of pooling.
89. Why can row-level optimistic concurrency fail to protect a cross-row business invariant?
A concurrency token protects the row whose original token appears in an update or delete predicate. It does not automatically validate every row that influenced a business decision. Suppose two doctors are on call: separate transactions each see two available doctors and update only their own row to go off call. Both row versions can match, yet no doctor remains available. This is write skew.
Protect the invariant at its actual scope. Options include a database constraint where expressible, serializable execution with a suitable indexed read, or a shared coordination row that every participating transaction updates using a concurrency token. Snapshot isolation alone does not eliminate every write-skew case. Retry the entire decision using fresh data after a serialization or concurrency conflict, with bounded retries; never simply overwrite the other transaction's values.
90. How do you make a retried write operation idempotent?
Give each logical request a stable idempotency key before the first attempt and persist its outcome with the business changes in one transaction. A unique database constraint arbitrates simultaneous submissions; a preliminary existence check alone has a race. Store enough request identity or a payload fingerprint to reject reuse of the same key for different work, and return the previously recorded result for an exact replay.
The execution strategy must retry the whole transaction, not only the last command. Generate identifiers outside the replay delegate, avoid external effects inside it, and handle an ambiguous commit by verifying the durable operation record. A GUID by itself does not make arbitrary side effects idempotent.
This SQL Server EF Core 10 model fragment assumes ProcessedRequest has a primary key plus TenantId and RequestId; authorization determines the tenant. Duplicate-key handling must inspect provider-specific errors and reload the matching result.
C# example
91. How would you coordinate a database update and message publication with an outbox?
Persist the business mutation and an outbox message in the same relational database transaction. A separate dispatcher publishes committed outbox records and marks them delivered only after broker acknowledgement. A crash after publication but before marking delivery can cause publication again, so stable message IDs and idempotent consumers remain necessary; the pattern is not a blanket exactly-once guarantee.
Use bounded batches, retry scheduling, poison-message handling, and a concurrency-safe claim or lease for multiple dispatchers. Preserve ordering where the domain requires it, and monitor the age of the oldest pending event.
This SQL Server EF Core 10 fragment assumes a tracked order, mapped OutboxMessage fields, and a stable eventId chosen before any replay. Both changes use one SaveChangesAsync transaction. Publishing is intentionally absent from the request transaction.
C# example
92. How can two DbContext instances share a relational transaction, and what remains separate?
Two contexts share a local relational transaction only when they use the same connection object and the same database transaction. Matching connection strings are insufficient. Their identity maps, tracked instances, original values, and pending changes remain separate; saving one does not save the other. Avoid editing the same row through both without an explicit consistency strategy.
The fragment assumes SQL Server, Microsoft.Data.SqlClient, Microsoft.EntityFrameworkCore.Storage, and an AppDbContext constructor accepting the shown options. The outer scope owns the connection and transaction; contexts use the externally supplied connection without owning it. Operations are sequential. Retrying strategies are disabled here; if enabled, recreate and replay the entire coordinated unit through the strategy. A commit failure must still be handled as potentially ambiguous.
C# example
93. How would you diagnose deadlocks without treating longer timeouts as the fix?
A deadlock is a cycle of incompatible resource dependencies, not merely a slow command. On SQL Server, collect the deadlock graph, including the victim, participating statements, indexes, lock modes, and transaction boundaries. The system_health Extended Events session captures deadlock reports on SQL Server; use the appropriate database-scoped diagnostics for Azure SQL Database. Correlate these with application operation IDs and EF command logs.
Look for reversed update order, large scans that acquire many locks, lengthy transactions, and database work mixed with remote calls. Remedies include consistent access order, selective indexes, shorter transactions, and carefully justified isolation changes. Retry an aborted transaction as a complete, idempotent unit when appropriate. Raising command timeouts neither removes the dependency cycle nor substitutes for examining the graph; excessive retries can amplify load.
94. What should an application do when the connection fails during transaction commit?
A lost connection during commit leaves the outcome unknown: the server may have committed even though the client received an error. Treating the operation as definitely rolled back can duplicate work. Reconnect and verify a stable operation marker or unique application-generated identifier before replaying or reporting a final result. Verification failure is still uncertainty, not proof of rollback.
This EF Core 10 SQL Server example assumes retries are enabled, order is a newly added entity with a client-generated GUID primary key assigned before retrying, and its presence proves the transaction committed. Import Microsoft.EntityFrameworkCore. Keeping acceptance of tracked changes separate preserves the pending insert for replay. A production operation also needs request-level deduplication when clients may submit the same logical work with different row IDs.
C# example
95. How do you plan an expand-and-contract schema change for a rolling deployment?
Separate compatibility from cleanup. First add a nullable column, new table, or other backward-compatible structure without removing what the old application needs. Deploy code that can coexist with old instances, then backfill existing data in bounded batches and validate completeness. If both versions still write, define an authoritative representation and a synchronization strategy; a one-time backfill does not capture later old-version writes.
After old instances, background jobs, and rollback dependencies are retired, switch reads and enforce new constraints. Remove obsolete columns only in a later contract release. Test each intermediate schema against the versions that may run together. Review migration SQL for lock duration, table rewrites, defaults, and operational cost on the production provider. Application rollback may be safe during expansion while destructive schema rollback is not.
96. How would you detect and repair schema drift between migrations and a production database?
Compare three independent records: the intended model and migration files in source control, the database's migration history, and the actual database schema. A migration-history row means the migration was recorded, not that nobody later altered the schema. Likewise, pending-model-change checks compare the model with its snapshot; they are not a production schema diff.
Export or inspect the production schema read-only and compare columns, nullability, types, indexes, constraints, and provider-specific objects against a clean database built from the same migrations. Determine whether drift came from manual DDL, a partial deployment, or a wrong target database. Back up affected data and create a reviewed repair that works from the observed state. Do not delete history rows or blindly reapply migrations to make the lists match.
This SQL Server query assumes the default EF migration-history table and schema.
SQL example
97. When is a reverse migration unsafe, and how should a rollback plan handle data changes?
A migration's Down method is a set of reverse operations, not a time machine. Recreating a dropped column does not restore its old values; reversing a type conversion may fail or discard information. Backfilled or transformed data may also be unreadable by the previous application version even if the old schema is recreated.
Plan application rollback separately from schema rollback. Prefer a forward repair or temporarily retain expanded schema when older code remains compatible. For destructive changes, define backups, restoration tests, recovery-point expectations, and how writes made after deployment will be reconciled. A database restore can itself lose legitimate new transactions.
This illustrative EF Core CLI command generates a reverse script from existing migration AddNormalizedEmail to the older AddCustomerEmail migration. Review and rehearse it against a disposable production-like copy; generation does not establish reversibility.
Shell command
98. How do you manage migrations when the application supports more than one database provider?
Maintain a migration set for each supported provider because scaffolding uses the active provider's model and capabilities. A shared entity model does not imply portable DDL: identity generation, column types, computed expressions, indexes, and schema operations differ. Use separate context types or separate migration assemblies with deterministic provider selection at design time.
Generate and review corresponding migrations for every model change, then test both a clean install and upgrades from supported deployed versions on each actual engine. SQLite, for example, rebuilds tables for many operations and cannot support every SQL Server feature; do not treat its success as SQL Server validation.
These commands assume two configured EF Core 10 context types, SqlServerContext and SqliteContext, in the current .NET 10 project. Each context must select its own provider without relying on accidental environment defaults.
Shell command
99. What should a production-provider integration-test matrix cover beyond CRUD?
Use an isolated database running the same engine family and relevant compatibility settings as production, not the production database itself. CRUD tests alone miss translation and transactional differences that mocks or an in-memory substitute cannot establish.
Cover these risk groups:
Query semantics: nulls, collation, precision, time zones, JSON, pagination, and provider-specific functions.
Constraints: unique keys, foreign keys, cascades, computed values, and generated identifiers.
Concurrency: conflicting updates, cross-row invariants, deadlocks, retries, and uncertain commits.
Security: tenant isolation, filter bypasses, raw SQL paths, and least-privilege database credentials.
Deployment: fresh schema creation, upgrades with existing data, and old/new application compatibility.
Use deterministic fixtures and isolation between parallel tests. Add representative-volume performance tests separately, with explicit budgets for commands, latency, and allocations. Passing SQLite tests does not prove SQL Server behavior, nor does a successful unit test validate database enforcement.
100. How do you choose between tenant-discriminator and database-per-tenant designs?
A tenant discriminator in shared tables simplifies fleet-wide schema management and cross-tenant operations, but makes every access path part of the isolation design. Use trusted tenant resolution, tenant-aware constraints and relationships, authorization, and controls on writes as well as filtered reads. Capacity and noisy-neighbor behavior remain shared concerns.
Database-per-tenant strengthens operational separation and enables tenant-specific backup, restore, scaling, or placement, at the cost of provisioning, migrations across many databases, connection-pool growth, and cross-tenant reporting complexity. The application still must resolve the correct database from an authorized tenant identity; a client-supplied connection choice is not isolation.
Choose from compliance, recovery, scale, and operating requirements rather than query-filter convenience. EF Core supports discriminator filtering and connection-based database selection. Schema-per-tenant is not a directly supported EF Core multitenancy strategy and requires additional design beyond changing a schema name.
101. How can SQL Server row-level security complement EF tenant filters?
SQL Server row-level security applies a database security policy to access against protected tables, adding a layer below EF query filters. Filter predicates restrict visible rows; block predicates can prevent prohibited writes. EF filters remain useful for normal application query behavior but are bypassable and do not secure arbitrary SQL on their own.
A session-context design must set a trusted tenant identity on every opened connection before tenant data is accessed. Test connection-pool reuse, missing identity, exceptions, background jobs, and elevated maintenance paths. Define a deny-by-default predicate and appropriate insert/update block predicates rather than assuming read filtering enforces ownership changes.
The trust boundary matters: a caller who can choose arbitrary session context through the shared database credential can impersonate tenants unless another control prevents it. Restrict database permissions, authenticate tenant selection in the application, and audit privileged bypasses. RLS complements authorization; it does not replace it.
102. How do you control intentional query-filter bypasses without creating a cross-tenant data leak?
Treat filter bypass as a privileged capability, not a convenience extension available throughout the application. Put it behind a narrow operation with explicit authorization, purpose-specific output, audit records, and cross-tenant negative tests. Parameterless IgnoreQueryFilters() disables all configured filters, including tenant isolation; a soft-delete administration screen rarely needs that power.
EF Core 10 named filters permit disabling only the soft-delete filter while leaving the tenant filter active. The example assumes named filters SoftDelete and Tenant, a request-scoped context bound to the authenticated tenant, and an independently authorized authorizedTenantId. The explicit tenant predicate is defense in depth, not a substitute for authorization. Raw command execution and direct database access need their own controls, and tracked or manually attached entities must not be assumed safe merely because ordinary queries are filtered.
C# example
103. How do you design a SaveChanges interceptor for auditing without recursive saves or unsafe shared state?
An auditing interceptor should inspect the current context, detect changes, and capture a stable snapshot before adding audit entities. Do not call SaveChanges recursively on the intercepted context. Audit rows added to that context can participate in the same relational save transaction; a separate audit database needs a separate reliability design and is not automatically atomic with business changes.
Implement both synchronous and asynchronous interception paths, or reject the unsupported path deliberately. Keep per-save mutable state out of shared singleton interceptors; pooled contexts also require care because interceptor instances can outlive one request. Resolve actor and tenant identity from trusted operation context, not entity fields supplied by a client.
The fragment is the core of a handler, not a complete interceptor. AuditRow is an application audit entity. Snapshot entries first, copy needed values, exclude audit rows, and redact secrets. Deduplicate retry-generated audit rows by operation ID; SavedChanges does not prove an outer transaction committed.
C# example
104. How do TPH, TPT, and TPC inheritance mappings change query and schema costs?
TPH stores a hierarchy in one table with a discriminator. It usually offers straightforward polymorphic queries but can create a wide table with nullable subtype columns. TPT separates base and derived tables, giving a normalized-looking schema while often requiring joins for derived or polymorphic queries. Those joins can become expensive in deep hierarchies.
TPC creates a table for each concrete type, repeating inherited columns. Queries for a single concrete type can be simple, but base-type queries typically combine multiple tables, and enforcing relationships to the entire hierarchy is harder. Keys must be unique across the hierarchy; generation strategies are provider-dependent.
Choose using the application's query mix, integrity needs, and measured plans rather than object-oriented aesthetics. This relational EF Core 10 configuration assumes a mapped Payment hierarchy; select one strategy, not all three simultaneously.
C# example
105. When should structured JSON data use EF Core 10 complex-type mapping rather than a serialized string converter?
Use complex-type JSON mapping when the value has meaningful structure that queries or updates must address. EF Core 10 understands mapped members inside the JSON document, unlike a converter that serializes an entire object into an opaque string. Complex types fit values without independent entity identity; they are not a replacement for every relational relationship.
A string converter can be sufficient for a small opaque payload that is always read and written as a whole. Mutable converted objects may also need a value comparer for correct change tracking. Check provider translation, JSON storage type, indexing, schema evolution, and expected document size before choosing either approach.
This EF Core 10 SQL Server fragment assumes a required Order.Delivery complex value with string City. Native json requires SQL Server 2025/Azure SQL and appropriate provider configuration, such as compatibility level 170; JSON mapping does not universally imply native storage.
C# example
106. When should a database function be mapped into LINQ rather than called through client code?
Map a database function when its work must remain inside a translated query, especially a filter, join, or ordering over many rows. An arbitrary C# method in a nontranslatable predicate cannot simply run on the server; forcing client evaluation can transfer far more data than intended. A mapped function keeps the expression server-side but may still be expensive or inhibit index use, so inspect the plan.
The CLR method supplies a signature for translation; it need not implement the SQL function's behavior. Deploy the function separately and align parameter types, null handling, schema, and permissions.
This SQL Server EF Core 10 fragment belongs inside AppDbContext, assumes dbo.OrderTotal(int) already exists, and uses Microsoft.EntityFrameworkCore. Calling OrderTotal directly in ordinary C# intentionally fails; its purpose is use within a translatable LINQ expression.
C# example
107. How do SQL Server temporal tables help investigate historical changes, and what are their limits?
SQL Server temporal tables preserve prior row versions while system versioning is enabled, making them useful for reconstructing values at a point in time or investigating when data changed. EF Core exposes temporal query operators; period timestamps are generated by SQL Server in UTC. The mechanism records row history, not the authenticated user, business reason, or external side effects, so a separate audit trail may still be required.
Plan retention, history-table growth, access permissions, and recovery procedures. Temporal history is not a backup strategy, and restoring a previous entity value requires deliberate validation of current constraints and related data. A temporal query across relationships also requires compatible mappings.
This SQL Server EF Core 10 example assumes Orders is configured as temporal and asOfUtc is a UTC DateTime. The projection is an investigative read, not an automatic restore.
C# example
108. How should command timeouts and cancellation be handled in a long-running EF operation?
A command timeout limits a database command according to provider behavior; it is not an end-to-end request deadline. Cancellation expresses that the caller no longer wants the work, and EF forwards the token to the provider, which may not honor it promptly. Neither a timeout nor cancellation proves a write was rolled back, particularly near commit.
Propagate cancellation through queries, saves, and transaction calls. For genuinely long jobs, prefer an owned background workflow with progress and resumable batches rather than an unbounded HTTP request. Investigate blocking and query shape before increasing limits.
This SQL Server EF Core 10 fragment temporarily changes the timeout on an owned db context and restores it in finally. ct comes from the operation's deadline; Orders has a numeric ID. The sample demonstrates timeout scope, not a recommended duration for every query.
C# example
109. How would you diagnose connection-pool exhaustion in an EF application?
First distinguish waiting for a connection from waiting on a command that already has one. Inspect application concurrency, connection-open durations, active readers, transaction lifetimes, and database blocking. A slow query can exhaust the pool indirectly by occupying connections longer; undisposed connections or partially consumed streaming results can do the same.
On SQL Server, separate pools can arise from distinct connection strings and identity settings, so tenant-specific configuration may fragment capacity. Ensure contexts, explicitly opened connections, readers, and transactions have clear disposal owners. Bound background-worker concurrency and avoid holding a transaction while calling remote services.
Increasing the pool limit can move the bottleneck to the database rather than fix it. Context pooling is a different mechanism and does not increase available database connections. Validate the remedy under representative concurrent load while observing database capacity, request latency, and connection wait times.
110. How would you investigate excessive memory retention in a long-lived change tracker?
Measure retained objects, tracked entity counts, snapshots, and the paths keeping the context alive. Common causes include singleton services holding a context, a background loop that never ends its unit of work, repeated tracking queries, and large navigation graphs. Database result size and application caches may contribute even when change tracking is not the main culprit.
Prefer short-lived contexts per bounded unit of work. For read-only workloads, use scalar/DTO projections or no-tracking queries; for imports, save bounded batches and release their contexts. ChangeTracker.Clear() can discard tracking state efficiently when reusing a context is unavoidable, but it also abandons unsaved changes and does not free entities still referenced elsewhere.
This diagnostic fragment assumes EF Core 10 and an existing db. Entries() may trigger change detection, so it is not a zero-cost metric to run continuously on a huge tracker.
C# example
111. How do you distinguish a translation regression from a change in database query-plan behavior?
Capture the actual executed SQL, parameter types, and relevant settings before and after the deployment. If translation fails before a command is sent, or the SQL shape changes, investigate the LINQ expression, provider version, model configuration, and EF breaking changes. Do not hide a translation error with early AsEnumerable() without evaluating the resulting data transfer.
If SQL remains equivalent, compare database plans, statistics, cardinality estimates, waits, indexes, and representative parameter values. The same SQL can become slower after data skew or a plan change. SQL Server Query Store helps establish that timeline.
Reproduce with a controlled matrix: old and new EF/provider packages against the same sanitized data, then relevant database compatibility settings. A SQL difference is evidence to examine, not proof that the new translation is worse. Confirm both result correctness and end-to-end performance before selecting a workaround.
112. How do you roll out nullable-reference-type changes without accidentally making existing columns required?
Enabling nullable reference types changes EF conventions: a non-nullable string can become required where the previous model treated it as optional. Review entity properties and configure existing database nullability explicitly before generating a migration. The C# required keyword governs initialization by callers; it is not a data-cleaning operation.
For a column that should remain nullable, use string? and, where useful, explicit optional configuration. For a deliberate transition to required, find existing nulls, define a business-valid backfill, update every writer, and only then enforce NOT NULL. Do not accept a generated migration's broad nullability changes merely because the application compiles.
This EF Core 10 relational model fragment assumes an existing optional Customer.MiddleName. The property declaration and fluent configuration are shown in their respective class and OnModelCreating locations; neither changes production until a reviewed migration is applied.
C# example
113. How can orphan deletion and relationship fixup cause an unexpected delete during graph editing?
Relationship fixup keeps tracked navigation properties and foreign-key values aligned. Removing a dependent from a required relationship can make it an orphan, which EF may mark for deletion under the configured cascade behavior. Replacing an entire collection from a DTO can therefore delete children that the client merely omitted. Optional relationships often become null instead, depending on configuration.
Load the authorized aggregate, compare explicit add/update/remove intentions, and reparent dependents before saving when movement is intended. Inspect tracked states after change detection; navigation assignment is not just an in-memory presentation change. Delaying orphan processing can help staged graph edits, but an unresolved required relationship must still be repaired or deliberately deleted.
This diagnostic fragment assumes a tracked EF Core 10 graph. Keep the debug view out of ordinary production logs because property values may be sensitive.
C# example
114. How do alternate keys differ from unique indexes when other entities reference a business identifier?
A unique index enforces uniqueness but does not by itself make a property an EF relationship target. An alternate key provides an additional entity identity that foreign keys can reference. HasPrincipalKey selects that target and introduces an alternate key when needed. Use a unique index when uniqueness alone is the requirement.
Choose a stable business identifier for an alternate key: EF treats key values as identity, so frequently changing natural keys are poor candidates. A surrogate primary key with a unique business index is often easier when the business identifier can change. In a multitenant model, uniqueness and foreign keys may need to include the tenant ID.
This relational EF Core 10 configuration assumes Product.Sku is a required stable string and OrderLine.ProductSku has the matching type. It maps a navigation to the business identifier rather than the primary key.
C# example
115. What must be revalidated when migrating an EF6 application to EF Core?
EF6 and EF Core are different implementations, not interchangeable package versions. EF6 uses System.Data.Entity; EF Core uses Microsoft.EntityFrameworkCore. An EF Core 10 target also requires .NET 10, so runtime, hosting, and library compatibility belong in the migration plan. Do not mix APIs from both frameworks in a supposedly single-context example.
Inventory EDMX or code-first mappings, conventions, lazy-loading behavior, relationship and cascade rules, LINQ translation, provider-specific functions, transactions, concurrency handling, and interception. Revalidate generated SQL and result semantics against the actual database. EF6 migration history and generated migrations are not automatically an EF Core migration chain; establish a reviewed baseline for the existing schema without recreating populated tables.
Port incrementally where boundaries permit, compare important reads and writes using representative data, and test deployment and rollback. Database scaffolding can accelerate model discovery, but it does not recover every application rule or guarantee behavioral equivalence.
116. How would you validate an EF Core 10 upgrade before deploying it?
Inventory the current runtime, EF packages, provider, database engine, and compatibility level. Align supported EF Core 10 package versions and confirm that the selected provider supports the target major version; a successful package restore is not proof of runtime compatibility. Read breaking changes for every skipped version, not just the destination release.
Build a regression set covering business-critical queries, generated SQL, null and collation semantics, concurrency, transaction retries, JSON mappings, tenant filters, and migrations. For SQL Server, explicitly review changes involving parameterized collections and JSON storage rather than assuming identical SQL or schema. Test against representative data and deployment settings, including old/new application coexistence where needed.
Compare correctness, tail latency, command counts, allocations, and cold start. Roll out gradually with error and performance monitoring, and keep an application rollback path that remains compatible with any schema already applied. Patch-specific behavior should be verified for the exact deployed versions.
117. How do you choose between tracked inserts, set-based updates, and provider-specific bulk-import tooling?
Use tracked inserts when entity relationships, generated values, and ordinary change tracking are important. AddRange reduces API overhead but is not a promise of native bulk loading. Use ExecuteUpdate or ExecuteDelete for set-based relational changes that should run without loading entities; they execute immediately, bypass tracked state, and require explicit concurrency predicates when needed.
For very large imports, SQL Server's SqlBulkCopy or another provider's native tooling can be appropriate, often through a staging table followed by validated database-side processing. Check transaction ownership, constraints, triggers, identity handling, and error recovery; bulk tooling does not automatically reproduce SaveChanges interceptors or application validation.
This EF Core 10 SQL Server example assumes UTC DateTime CreatedAt, a matching DateTime cutoff, and Boolean IsArchived. Archival is authorized for the entire selected population, and no affected entities are already tracked. Group related set-based commands in an explicit transaction when they must succeed together.
C# example
118. When is disabling automatic change detection justified, and how do you restore correctness?
Disable automatic change detection only after profiling demonstrates that repeated tracker scans are significant. It is not a general speed switch: property or navigation mutations may otherwise remain invisible, leaving incorrect states or missed updates. Prefer bounded units of work and efficient query shape before taking manual control.
Save and restore the prior setting with finally, and explicitly call DetectChanges after ordinary CLR mutations and before the save that must include them. Consider interceptor behavior too: later mutations may require detection after that point. Explicit entry APIs and notification tracking have different detection needs, so test the actual model.
The fragment assumes an EF Core 10 context containing only the intended tracked changes and a list of tracked orders with Status. It demonstrates a correctness boundary; it does not claim that this simple loop benefits from disabling detection.
C# example
119. How would you benchmark an EF optimization without measuring a misleading microbenchmark?
State the hypothesis and preserve behavior before measuring. For example, a projection should return the same required fields while reducing transferred bytes and allocations; a compiled query should reduce EF overhead without changing results. Use production-like data volume, skew, indexes, provider version, database configuration, network latency, and concurrent request patterns.
Separate cold initialization from warmed steady-state measurements. Control or report connection-pool state, query and plan caches, database resource contention, and logging. Do not compare a cached result against a real database query or an in-memory provider against production SQL. Run enough independent samples to report distributions rather than one favorable timing.
Measure end-to-end latency, throughput, tail percentiles, commands, database reads/CPU, and application allocations. Include cancellation, errors, and correctness checks. An optimization that wins a single-row local microbenchmark may lose under network latency or concurrent load, so deploy only after representative validation.
120. How would you investigate and contain a production incident involving lost updates, duplicate writes, and slow queries?
Contain impact before optimizing: limit unsafe write paths, reduce overload, and preserve logs, operation IDs, database evidence, and affected time ranges. Separate the symptoms into hypotheses. Lost updates suggest missing or bypassed concurrency checks; duplicate writes suggest retry or request-deduplication gaps; slow queries may involve blocking, changed plans, excessive commands, or overloaded connection pools. One failure can amplify the others.
Correlate stable request IDs with commits and retry attempts, then verify ambiguous outcomes from durable records. Do not replay an entire queue blindly or overwrite conflicting rows to clear errors. Restore correctness with explicit conflict handling, unique idempotency constraints, and replay-safe transaction boundaries; repair data through a reviewed, auditable process.
Validate fixes using concurrent writers, transient-failure injection, and representative load on the production provider. Monitor duplicate rates, concurrency conflicts, outbox lag, database waits, and tail latency during gradual recovery. Close the incident only after data reconciliation and recurrence checks, not merely when exceptions stop.
Entity Framework Documentation and Further Reading
The default baseline is EF Core 10 on .NET 10, using Microsoft.EntityFrameworkCore; explicitly labeled EF6 examples and comparisons are separate. EF Core 10 is not a .NET Framework library. Relational examples assume SQL Server unless another provider is stated; server-specific features still require their stated versions. Match the EF packages, provider, database engine, and compatibility settings before applying an example. Query translation, data types, transactions, migrations, and feature availability can differ by provider and server version. A common LINQ API does not guarantee identical behavior across databases. Examples are focused excerpts and need the stated model, imports, dependencies, and configuration. Source or static checks alone do not establish C# compilation, EF execution, generated SQL, transaction behavior, or performance. Tests using SQLite, in-memory data, or mocked application boundaries do not by themselves prove behavior on another production database. Validate complete workflows against a matching test environment before deployment.
Use these curated primary references to verify EF Core API contracts, modeling and query behavior, persistence rules, testing limitations, and explicitly scoped EF6 differences. Check provider and database documentation when an answer depends on translation, SQL dialect, isolation, or deployment support.
Similar Posts