Skip to main content

EF Core Interview Questions & Answers

DbContext & Lifecycle​

Q: What is DbContext and what is its purpose?

A: DbContext is the primary class for interacting with a database in EF Core. It:

  • Manages database connections
  • Tracks changes to entities
  • Queues and executes SQL commands
  • Provides LINQ queries against the database
  • Implements Unit of Work pattern

Q: Why should DbContext be scoped, not singleton?

A: DbContext is not thread-safe and maintains state (change tracking) for a single unit of work. Using it as singleton causes:

  • Thread safety issues when multiple requests access it
  • Change tracking conflicts between different operations
  • Memory leaks from accumulated tracked entities
  • Connection pool exhaustion

Q: What is DbContext pooling and how does it improve performance?

A: DbContext pooling reuses DbContext instances instead of creating new ones for each request. Benefits:

  • 20-30% performance improvement
  • Reduced object allocation overhead
  • Faster initialization
  • Lower GC pressure

Enabled with services.AddDbContextPool<AppDbContext>().

Q: When would you use IDbContextFactory?

A: Use IDbContextFactory for:

  • Singleton or long-lived services
  • Background services processing batches
  • Blazor Server (scoped to circuits)
  • Console applications
  • When you need multiple DbContext instances in parallel

Entity States & Change Tracking​

Q: What are the entity states in EF Core?

A:

  • Detached: Not tracked by context
  • Unchanged: Tracked, no modifications detected
  • Added: New entity, will be inserted on SaveChanges
  • Modified: Existing entity with changes, will be updated
  • Deleted: Marked for deletion, will be deleted on SaveChanges

Q: What's the difference between Attach and Update?

A:

  • Attach: Sets state to Unchanged. No update happens unless you modify properties.
  • Update: Sets state to Modified. All properties will be updated in database.

Use Attach when you'll modify specific properties. Use Update when all properties should be updated.

Q: When should you disable AutoDetectChanges?

A: Disable AutoDetectChanges for bulk operations:

context.ChangeTracker.AutoDetectChangesEnabled = false;
foreach (var item in largeList)
{
    item.Process();
    context.Add(item);
}
context.ChangeTracker.DetectChanges();
await context.SaveChangesAsync();

Benefits: Significant performance improvement when modifying many entities.

Query Optimization​

Q: Explain the N+1 query problem and three solutions.

A: Problem: Executing 1 query for N records, then N queries for related data = N+1 total queries.

Solutions:

  1. Eager Loading: Include(o => o.Customer) - Single query with JOIN
  2. Projection: Select(o => new { o.Id, o.Customer.Name }) - Only needed data
  3. Split Query: AsSplitQuery() - Separate queries for collections

Q: When should you use AsNoTracking?

A: Use AsNoTracking for:

  • Read-only queries (display data, reports, APIs)
  • When you won't modify the entities
  • When you don't need change tracking

Benefits: 20-30% faster, less memory usage.

Don't use when you need to update entities.

Q: What's the difference between AsSplitQuery and single query?

A:

  • Single Query: One SQL with multiple JOINs. Can cause cartesian explosion with multiple collections.
  • Split Query: Multiple SQL queries, one per include. Avoids cartesian explosion but more database round trips.

Use Split Query when including multiple collections to prevent huge result sets.

Q: How do compiled queries improve performance?

A: Compiled queries cache the query translation:

private static readonly Func<AppDbContext, int, Task<Product>> _getProduct =
    EF.CompileAsyncQuery((AppDbContext ctx, int id) =>
        ctx.Products.FirstOrDefault(p => p.Id == id));

Benefits: 2x faster for frequently executed queries. First execution compiles and caches the query plan.

Q: When would you use raw SQL instead of LINQ?

A: Use raw SQL for:

  • Complex queries LINQ can't express
  • Performance-critical queries
  • Stored procedures
  • Bulk operations
  • Database-specific features
  • When generated SQL is inefficient

Relationships​

Q: How do you configure a Many-to-Many relationship?

A: Two approaches:

Simple (EF Core 5+):

modelBuilder.Entity<Post>()
    .HasMany(p => p.Tags)
    .WithMany(t => t.Posts);

With Join Entity:

modelBuilder.Entity<StudentCourse>()
    .HasKey(sc => new { sc.StudentId, sc.CourseId });

modelBuilder.Entity<StudentCourse>()
    .HasOne(sc => sc.Student)
    .WithMany(s => s.StudentCourses)
    .HasForeignKey(sc => sc.StudentId);

Use join entity when you need additional properties like EnrollmentDate.

Q: Explain cascade delete behaviors.

A:

  • Cascade: Automatically deletes dependent entities
  • Restrict: Throws exception if dependents exist
  • SetNull: Sets foreign key to null (requires nullable FK)
  • NoAction: Database handles it (can cause constraint violations)

Advanced Patterns​

Q: Should you use Repository pattern with EF Core?

A: Controversial. DbContext already implements Repository and Unit of Work patterns.

Use Repository when:

  • Abstracting from EF Core for testing
  • Multiple data sources
  • Domain-driven design with aggregates
  • Team prefers the pattern

Don't use when:

  • Simple CRUD applications
  • Already using DI with DbContext
  • No plan to switch ORMs
  • Adds unnecessary abstraction layer

Q: What are global query filters and when would you use them?

A: Global query filters automatically apply to all queries:

modelBuilder.Entity<Product>()
    .HasQueryFilter(p => !p.IsDeleted);

Use cases:

  • Soft delete (filter deleted records)
  • Multi-tenancy (filter by tenant)
  • Active/inactive records
  • Row-level security

Q: What is optimistic concurrency and how does it work?

A: Optimistic concurrency assumes conflicts are rare. Uses a concurrency token (timestamp/rowversion):

[Timestamp]
public byte[] RowVersion { get; set; }

On update, EF checks if RowVersion matches. If not, throws DbUpdateConcurrencyException. You then resolve the conflict (client wins, database wins, or merge).

Q: When would you use TPH vs TPT inheritance?

A: TPH (Table-Per-Hierarchy):

  • Single table with discriminator column
  • Faster queries (no joins)
  • Nullable columns for subclass properties
  • Use when: Performance matters, shallow hierarchy

TPT (Table-Per-Type):

  • Separate table per type
  • More normalized
  • Slower (requires joins)
  • Use when: Normalization important, deep hierarchy

Performance​

Q: How do you monitor slow queries in production?

A: Multiple approaches:

  1. Interceptors: Log queries over threshold
  2. MiniProfiler: UI showing query performance
  3. Application Insights: Track query duration
  4. Query Tags: Tag queries for identification
  5. SQL Server Profiler: Database-level monitoring

Q: What's the impact of change tracking on performance?

A: Change tracking adds 20-30% overhead:

  • Memory for snapshots
  • CPU for change detection
  • Tracking graph complexity

Use AsNoTracking for read-only queries to eliminate this cost.

Q: How does DbContext pooling work internally?

A: Pooling maintains a pool of DbContext instances:

  1. Request arrives, gets context from pool
  2. Context state is reset (ChangeTracker cleared)
  3. Context used for request
  4. Context returned to pool (not disposed)
  5. Reused for next request

Reduces allocation and initialization overhead.

Migrations​

Q: What's the difference between EnsureCreated and Migrate?

A:

  • EnsureCreated: Creates database from model, no migration history. For testing/prototyping only.
  • Migrate: Applies migrations sequentially, maintains history. For production.

Never use EnsureCreated in production.

Q: How do you handle production migrations safely?

A:

  1. Generate idempotent SQL script: dotnet ef migrations script --idempotent
  2. Review script manually
  3. Test in staging environment
  4. Execute script in production
  5. Never auto-migrate in production code

Q: How do you handle migration conflicts in a team?

A: When two developers create migrations simultaneously:

  1. Second developer removes their migration
  2. Pull first developer's migration
  3. Recreate their migration on top
  4. Migrations will be in correct timestamp order

Common Anti-Patterns​

Q: Name three EF Core anti-patterns to avoid.

A:

  1. N+1 Queries: Loading related data in loops
  2. Tracking for read-only: Not using AsNoTracking
  3. Unbounded queries: Loading all records without pagination
  4. Cartesian explosion: Multiple Includes without AsSplitQuery
  5. Singleton DbContext: Thread safety issues

Coding Challenges​

See separate files for:

  • Coding challenges
  • System design scenarios
  • Performance optimization exercises