Entity Framework Core Interview Question #150

What are best practices for EF Core performance, raw SQL, and advanced queries?

Performance, Raw SQL, Stored Procedures & Advanced Queries Senior Advanced

Quick Interview Answer

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.

Detailed Explanation

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.

Use raw SQL, stored procedures, views, and keyless types when they provide a clear benefit, while keeping parameterization and SQL-injection prevention non-negotiable. Treat compiled queries, context pooling, split queries, and timeout changes as targeted tools. Measure first and optimize the actual bottleneck.

Code Example

var query = db.Products
    .TagWith("Catalog search")
    .AsNoTracking()
    .Where(p => p.IsPublished)
    .Select(p => new { p.Id, p.Name, p.Price })
    .Take(50);

var results = await query.ToListAsync(cancellationToken);