SQL Server Interview

ASP.NET Core, EF Core & SQL Server Real-World Scenarios

SQL Server interview questions covering asp.net core, ef core & sql server real-world scenarios.

20 Questions Filter by level

Interview questions

Open a question for the full answer, code and interview guidance.

20 Results
281
SeniorAdvanced

How does EF Core communicate with SQL Server?

EF Core is an object-relational mapper (ORM). Application code builds LINQ queries and entity operations, EF Core translates supported LINQ expressions into SQL, sends commands through the configured SQL Server provider, and materializes returned rows into .NET objects. EF Core does not eliminate the need to understand SQL Server. Developers still need to understand generated SQL, indexes, transactions, query plans, data types, concurrency, and database constraints because ORM-generated queries execute against the same database engine.

282
SeniorAdvanced

What is change tracking in EF Core?

Change tracking allows EF Core to keep track of entity state such as Added, Modified, Deleted, Unchanged, and Detached. When SaveChanges or SaveChangesAsync is called, EF Core uses tracked state to determine which INSERT, UPDATE, and DELETE commands are required. Tracking is useful when entities will be modified, but it has memory and CPU cost. For read-only queries, tracking may be unnecessary.

283
SeniorAdvanced

What does AsNoTracking() do?

AsNoTracking tells EF Core not to track entities returned by a query. It is useful for read-only operations such as reports, catalog pages, API GET endpoints, and dashboards where the entities will not be updated through the same DbContext. Removing tracking overhead can reduce memory usage and improve query-processing performance. However, it should not be applied blindly when tracked entity behavior is required.

284
SeniorAdvanced

What is the N+1 query problem?

The N+1 problem occurs when an application executes one query to load a set of parent records and then executes an additional query for each parent to load related data. For 100 parent rows, this can produce 101 database queries. Network round trips and repeated database work can make this much slower than an appropriately designed query. Solutions include projections, appropriate eager loading, explicit batch queries, or redesigning the data-access pattern. The correct approach depends on how much related data is actually required.

285
SeniorAdvanced

What is eager loading in EF Core?

Eager loading retrieves related data as part of the intended query operation, commonly using Include and ThenInclude. It can prevent repeated lazy-loading queries, but large Include graphs can create large result sets, duplicate relational rows, and expensive joins. EF Core can use split queries in appropriate scenarios to avoid some cartesian-explosion problems. For API and view-model queries, projection with Select is often more efficient when only a subset of columns is needed.

286
SeniorAdvanced

What is projection and why is it important for EF Core performance?

Projection means selecting only the fields required by the operation instead of loading complete entities. For example, a product-list page may need Id, Name, Price, and ImageUrl rather than every product column and navigation property. Projection reduces transferred data, materialization work, memory usage, and often improves SQL efficiency. DTO and ViewModel projections are particularly useful in ASP.NET Core applications.

287
SeniorAdvanced

What is the difference between IQueryable and IEnumerable in an EF Core query?

IQueryable represents a query expression that a provider such as EF Core can translate and execute remotely. Query operators added before materialization can become part of the SQL statement. After data is materialized into an in-memory collection, IEnumerable operations run in .NET memory. Calling ToList too early can therefore retrieve far more rows than required. A common performance principle is to apply translatable filtering, ordering, projection, and pagination before materialization.

288
SeniorAdvanced

How should pagination be implemented efficiently?

For ordinary page-number interfaces, SQL Server commonly uses OFFSET/FETCH generated through EF Core Skip and Take. It works well for many workloads when supported by an appropriate deterministic index. For very deep pagination, OFFSET must still process or skip preceding rows and can become expensive. Keyset or seek pagination can be more scalable by requesting rows after the last seen key. Whichever strategy is used, ordering should be deterministic, often by adding a unique key as a tiebreaker.

289
SeniorAdvanced

What is keyset pagination?

Keyset pagination retrieves the next page based on the last key values from the previous page rather than skipping an increasing number of rows. For example, when ordering by Id, the next query can request WHERE Id > @LastId ORDER BY Id. For composite ordering, the seek predicate must correctly represent the ordering keys. Keyset pagination performs particularly well for large sequential datasets, although it is less convenient when users must jump directly to arbitrary page numbers.

290
SeniorAdvanced

How does EF Core handle transactions?

SaveChanges automatically uses a transaction when required so the changes in that call are committed atomically. EF Core also supports explicit transactions for operations that must span multiple SaveChanges calls or database steps. Explicit transaction scope should remain short. Do not hold a database transaction open while waiting for user input, calling slow external APIs, or performing unrelated work. For retrying execution strategies, transaction handling must follow EF Core's supported pattern so the entire transaction can be retried safely when appropriate.

291
SeniorAdvanced

What is DbContext pooling?

DbContext pooling allows EF Core to reuse DbContext instances from a pool instead of allocating and initializing a completely new instance for every request or operation. It can reduce overhead in high-throughput applications, but pooled contexts require care because instance state can persist unless EF Core or the application resets it correctly. Request-specific mutable state should not be casually stored on a pooled context. DbContext pooling is different from SQL connection pooling.

292
SeniorAdvanced

What is SQL connection pooling in an ASP.NET Core application?

ADO.NET connection pooling reuses physical database connections instead of creating a new network/database session for every Open operation. SqlConnection objects should normally be opened late and disposed promptly. Disposing usually returns the physical connection to the pool rather than closing the underlying network connection permanently. Pools are separated by connection-string and identity-related characteristics. Connection leaks or long-running operations can exhaust the pool and cause application requests to wait or time out.

293
SeniorAdvanced

Why should DbContext usually be scoped per web request?

In ASP.NET Core, AddDbContext normally registers DbContext with scoped lifetime, which aligns well with one HTTP request and a logical unit of work. DbContext is not thread-safe and should not be shared concurrently across requests or long-lived singleton services. A very long-lived context can accumulate tracked entities and stale state. Background services or parallel operations should create appropriate independent scopes or contexts rather than sharing one request context.

294
SeniorAdvanced

What are EF Core migrations?

Migrations provide a versioned mechanism for evolving the database schema as the EF Core model changes. A migration can contain operations such as creating tables, adding columns, indexes, constraints, and data transformations. Production migrations should be reviewed before deployment. Automatically applying every migration at application startup can create availability, permission, locking, or rollback concerns in controlled production environments. Teams often generate and review migration SQL as part of deployment pipelines.

295
SeniorAdvanced

When would you use raw SQL or a stored procedure instead of LINQ?

LINQ is appropriate for a large portion of application data access because it is composable, type-aware, and integrated with EF Core. Raw SQL or stored procedures can be appropriate for specialized database operations, complex reporting, bulk processing, database-specific features, carefully tuned SQL, legacy database APIs, or operations that are awkward to express efficiently through LINQ. The decision should be based on maintainability, performance, security, transactional requirements, and team expertise—not on the assumption that stored procedures are always faster or that ORM queries are always sufficient.

296
SeniorAdvanced

How do you diagnose a slow EF Core query?

Capture the generated SQL and parameter values, then analyze the database execution rather than tuning only the LINQ syntax. Check the actual execution plan, Query Store, logical reads, row estimates, indexes, blocking, parameter types, result-set size, and server waits. In the application, check tracking, projection, Include usage, N+1 queries, query count, pagination, and premature ToList calls. EF Core logging and ToQueryString can help inspect generated SQL during development. Production diagnostics should avoid exposing sensitive parameter data unnecessarily.

297
SeniorAdvanced

How can an ASP.NET Core application safely handle transient SQL Server failures?

Transient failures include temporary connectivity problems, failovers, throttling, deadlock victims, and other short-lived conditions depending on deployment. EF Core's SQL Server provider can enable retrying execution strategies through EnableRetryOnFailure. Retries should be bounded and used only for operations that are safe under the strategy. Custom transaction code requires special care because retrying an individual statement inside a partially completed business operation can create incorrect behavior. The entire retriable unit should be designed consistently.

298
SeniorAdvanced

How do you prevent duplicate records when two requests arrive at the same time?

Do not rely only on an application-level 'check then insert' sequence because two concurrent requests can both pass the check before either inserts. Enforce the business uniqueness rule in SQL Server with a UNIQUE constraint or unique index. The application can perform a friendly pre-check for user experience, but the database constraint is the final concurrency-safe enforcement point. Catch the resulting database exception and convert it into the appropriate application response.

299
SeniorAdvanced

An ASP.NET Core API times out while SQL Server CPU is low. What would you investigate?

Low SQL Server CPU does not prove the database is healthy. The request may be waiting rather than consuming CPU. Check blocking and lock waits, storage latency, transaction-log flush latency, memory-grant waits, connection-pool exhaustion, network latency, external API calls, thread-pool pressure, command timeout settings, and long transactions. Correlate the ASP.NET Core request trace with SQL Server sessions, Query Store, Extended Events, and application telemetry. Determine where elapsed time is actually being spent before changing timeout values. Increasing CommandTimeout can hide the symptom without fixing the underlying bottleneck.

300
SeniorAdvanced

How would you design a high-performance ASP.NET Core + SQL Server data-access layer?

Start with clear transactional boundaries and database-enforced integrity. Use appropriate primary/foreign keys, constraints, data types, and indexes based on real query patterns. In EF Core, keep DbContext scoped appropriately, use async database operations for scalable I/O, project only required columns, use AsNoTracking for read-only entity queries when appropriate, avoid N+1 patterns, paginate large datasets, and inspect generated SQL for important paths. Use parameterized queries, least-privilege database credentials, optimistic concurrency where business workflows require it, and short transactions. Use stored procedures or raw SQL selectively when they provide a measurable or architectural advantage. For production operation, enable useful observability: structured application logging, request tracing, Query Store, database metrics, blocking/deadlock monitoring, backup verification, and performance baselines. Most importantly, performance should be measured end-to-end. A well-designed data-access layer balances correctness, security, maintainability, database efficiency, concurrency, and operational reliability rather than optimizing one layer in isolation.