Entity Framework Core interview questions covering query performance, raw SQL, stored procedures, views, keyless entities, pooling and diagnostics.
Open a question for the full answer, code and interview guidance.
Filter early, project only required columns, paginate large result sets, and use AsNoTracking for read-only entity queries. Avoid N+1 queries, unnecessary Include graphs, repeated round trips, and client-side processing of large datasets. Ensure important filter, join, and ordering columns have suitable indexes. For critical queries, inspect generated SQL and database execution plans and optimize from measurements.
Unnecessary rows and columns increase database work, network traffic, materialization, memory use, garbage collection, and possibly change-tracking overhead. Apply filtering and projection before materialization and paginate large collections rather than loading everything into application memory.
AsNoTracking prevents query results from being added to the change tracker. It reduces tracking metadata and change-detection overhead for read-only queries. It is useful for list pages, reports, and read-only APIs, but should not be used when the same context must track and save modifications to those entities.
Projection with Select retrieves only the fields needed by the application instead of materializing complete entities. This reduces transferred data, memory usage, and materialization work. DTO projection is especially useful for read-only pages and APIs and also separates persistence entities from presentation contracts.
EF Core generates SQL, but the database optimizer determines how it is executed. Appropriate indexes can reduce work for filters, joins, uniqueness checks, and ordering. Indexes should reflect real query patterns because excessive indexes consume storage and make writes more expensive. Use execution plans and workload evidence to guide index design.
The N+1 problem occurs when one query retrieves a collection and an additional query is then executed for each item to load related information. It commonly appears with careless lazy loading or explicit loading inside loops. Avoid it with projection, suitable Include operations, batching, or another set-based query shape. Database-command logging can reveal repeated queries.
Cartesian explosion can occur when one SQL query joins multiple collection navigations at the same level. Rows from one collection can be multiplied by rows from another, creating a much larger result set than expected. Projection, purpose-specific queries, or AsSplitQuery can reduce the problem depending on the required data shape.
AsSplitQuery can help when eager-loading multiple collections would create a very large joined result and duplicate substantial data. It uses multiple SQL queries for relevant included collections instead of one large join. The trade-off is additional round trips and possible consistency considerations between queries, so measure both strategies for important workloads.
EF Core processes a LINQ expression tree into a database command representation before execution. It automatically caches query compilation information for recurring query shapes. This built-in behavior is sufficient for most applications. Database execution, network I/O, indexing, and materialization are usually larger performance factors than compilation.
Compiled queries explicitly precompile an EF Core query delegate so repeated executions can avoid some query-processing overhead. They are most useful for very hot, stable query shapes where profiling shows compilation overhead matters. They add complexity, so first address inefficient SQL, missing indexes, excessive retrieval, and unnecessary round trips.
ToQueryString can display the SQL representation of an IQueryable for diagnostics. EF Core logging can also record executed commands and timings. Inspecting generated SQL helps identify unnecessary joins, selected columns, repeated queries, and unexpected query shapes. Production logging should avoid exposing sensitive information.
TagWith adds a comment to generated SQL, making it easier to connect statements seen in database traces or monitoring tools to the application query that produced them. This is useful in large applications with many similar queries. Do not put sensitive user data in tags because they can appear in database logs.
EF Core provides relational APIs such as FromSql for querying mapped types from raw SQL when LINQ is insufficient or existing SQL must be reused. Raw SQL should be deliberate because it reduces provider independence and can be harder to refactor. Parameterization is essential whenever external values are included.
Parameterized SQL sends values separately from command structure, helping prevent SQL injection and allowing values to be treated as data. Concatenating untrusted input directly into SQL can let an attacker change command meaning. Use EF Core APIs that parameterize values and never build SQL by concatenating untrusted input.
Raw SQL can be appropriate for complex provider-specific queries, carefully tuned reporting SQL, database features not conveniently exposed through LINQ, or integration with existing stored procedures and views. LINQ remains preferable for many application queries because it is strongly typed and composable. Choose based on maintainability, required database features, and measured performance.
Relational EF Core APIs can execute SQL commands that do not return entity query results, such as controlled UPDATE or DELETE commands. Values should be parameterized. Direct SQL bypasses normal tracked-entity SaveChanges behavior, so already tracked entities may contain stale values afterward.
EF Core can work with stored procedures through relational SQL APIs, and supported provider features can also map certain persistence operations to stored procedures. Stored procedures may be useful for existing database logic or provider-specific functionality. Their parameters, result shape, deployment, and versioning should be treated as an explicit contract.
A database view can be mapped to an EF Core type, often as a keyless entity type when the view has no suitable primary key. EF Core can then query the mapped type with LINQ. Keyless view mappings are generally used for read-only reporting unless a separate update strategy is intentionally designed.
A keyless entity type represents query data without a primary key in the EF Core model. It is useful for database views, reporting projections, and raw SQL result shapes. Because EF Core has no key with which to identify a persistent entity instance, keyless types are not used for normal tracked insert, update, or delete operations.
A command timeout limits how long EF Core waits for a database command before the provider treats it as timed out. Raising the timeout can be appropriate for known long-running administrative or reporting operations, but it should not hide inefficient queries, blocking, or missing indexes. Investigate the reason for slowness first.
Database providers commonly pool physical connections. Opening a connection typically obtains one from the pool, and closing or disposing it normally returns it for reuse. Connection pooling is different from DbContext pooling: DbContext is EF Core's unit of work, while connection pooling is managed primarily by the database provider. Avoid holding connections or transactions longer than necessary.
DbContext pooling allows EF Core to reuse DbContext instances after their internal state has been reset, reducing some context allocation and initialization overhead. It is configured with AddDbContextPool. Because instances are reused, mutable per-request state should not be stored on a pooled context as though a brand-new instance were always created. Use it when its lifetime implications are understood and measurements justify it.
IDbContextFactory creates DbContext instances on demand. It is useful when a DI scope does not naturally correspond to one unit of work or when separate contexts are needed in background work, UI components, or carefully designed parallel operations. Each created context should be disposed. A DbContext itself is not thread-safe and should not be shared concurrently.
Use application metrics, structured logs, distributed traces, database monitoring, and execution plans to determine where database time is spent. Look for slow queries, N+1 patterns, excessive rows, lock waits, timeouts, missing indexes, high-frequency queries, and expensive plans. Diagnostics must protect sensitive data; detailed or sensitive-data logging should only be enabled when appropriate.
Start with efficient query design: filter in the database, project only required columns, paginate large results, use AsNoTracking for read-only entity queries, and prevent N+1 queries and oversized Include graphs. Build appropriate indexes and verify behavior with generated SQL and execution plans.