Querying Interview Questions & Answers

IQueryable<T> builds an expression tree that EF Core translates to SQL — filtering happens in the database. IEnumerable<T> operates in memory — the full result set is loaded first, then filtered in C#.

// IQueryable — WHERE clause runs on the DB:
IQueryable<Order> query = _db.Orders          // still IQueryable — no SQL yet
    .Where(o => o.CustomerId == customerId)    // added to SQL: WHERE CustomerId = @p0
    .Where(o => o.Total > 100);               // added to SQL: AND Total > @p1

// Materialise — this is when SQL executes:
var orders = await query.ToListAsync();       // SELECT ... WHERE CustomerId=? AND Total>?

// IEnumerable — loads ALL rows, then filters in memory:
IEnumerable<Order> allOrders = _db.Orders.AsEnumerable(); // SELECT * FROM Orders (all rows!)
var expensive = allOrders.Where(o => o.Total > 100);      // filtered in C# — too late

// Practical consequence:
public IQueryable<Order> GetOrdersByCustomer(int id)
    => _db.Orders.Where(o => o.CustomerId == id); // callers can compose further SQL

public IEnumerable<Order> GetOrdersByCustomerBad(int id)
    => _db.Orders.Where(o => o.CustomerId == id).ToList(); // materialised — composition lost

Rule of thumb: Keep queries as IQueryable<T> until the last moment. Never call .AsEnumerable() or .ToList() mid-chain unless you intentionally want in-memory processing of a small set.

EF Core queries are deferred — they build an expression tree but don't hit the database until a terminal operator materialises the results.

// Building the query — NO SQL yet:
var query = _db.Orders
    .Where(o => o.Status == "Pending")
    .OrderBy(o => o.CreatedAt)
    .Take(50);

// Terminal operators — each generates and executes SQL:
var list   = await query.ToListAsync();            // SELECT ... (returns List<Order>)
var first  = await query.FirstOrDefaultAsync();    // SELECT TOP 1 ...
var count  = await query.CountAsync();             // SELECT COUNT(*)
var any    = await query.AnyAsync();               // SELECT CASE WHEN EXISTS(...)
var single = await query.SingleAsync();            // SELECT ... (throws if 0 or 2+)
var array  = await query.ToArrayAsync();
var dict   = await query.ToDictionaryAsync(o => o.Id);

// foreach also triggers execution (but prefer ToListAsync for async):
await foreach (var order in query.AsAsyncEnumerable()) // streaming — no full buffer
    Process(order);

// Common mistake — calling the DB twice:
var q = _db.Products.Where(p => p.IsActive);
var count    = await q.CountAsync();  // SELECT COUNT(*) — first trip
var products = await q.ToListAsync(); // SELECT * — second trip
// Fix: do both in one query or materialise once.

Rule of thumb: A LINQ chain on DbSet<T> is just a description. SQL executes only at ToListAsync, FirstOrDefaultAsync, CountAsync, AnyAsync, etc. Avoid materialising the same query twice.

Projection via .Select() instructs EF Core to generate a SELECT with only the specified columns, reducing data transfer and avoiding unnecessary object overhead.

// Loads all columns including large blobs:
var orders = await _db.Orders
    .Where(o => o.CustomerId == customerId)
    .ToListAsync(); // SELECT Id, CustomerId, Status, Total, Notes, Blob, ...

// Project to a DTO — only fetches needed columns:
var summaries = await _db.Orders
    .Where(o => o.CustomerId == customerId)
    .Select(o => new OrderSummaryDto
    {
        Id     = o.Id,
        Status = o.Status,
        Total  = o.Total
    })
    .ToListAsync(); // SELECT Id, Status, Total FROM Orders WHERE CustomerId = @p0

// Anonymous type projection (quick/local use):
var ids = await _db.Orders
    .Where(o => o.Status == "Pending")
    .Select(o => new { o.Id, o.CreatedAt })
    .ToListAsync();

// Nested projection — joins happen in one query:
var result = await _db.Orders
    .Select(o => new OrderDetailDto
    {
        Id           = o.Id,
        CustomerName = o.Customer.Name,  // JOIN generated automatically
        Items        = o.Items.Select(i => new ItemDto
        {
            ProductName = i.Product.Name,
            Quantity    = i.Quantity
        }).ToList()
    })
    .ToListAsync();

Rule of thumb: Always project to a DTO or anonymous type when the query result is read-only. Loading full entities just to read two columns wastes bandwidth and memory, especially for large tables.

Eager loading fetches related entities in the same query via SQL JOIN using Include and ThenInclude, avoiding separate round trips to the database.

// Without Include — navigation properties are null:
var order = await _db.Orders.FindAsync(42);
Console.WriteLine(order.Customer.Name); // NullReferenceException — not loaded

// Include — adds a JOIN for the related table:
var order = await _db.Orders
    .Include(o => o.Customer)                  // JOIN Customers
    .Include(o => o.Items)                     // JOIN OrderItems
    .FirstOrDefaultAsync(o => o.Id == 42);

// ThenInclude — navigate deeper into the graph:
var orders = await _db.Orders
    .Include(o => o.Items)                     // JOIN OrderItems
        .ThenInclude(i => i.Product)           // JOIN Products
    .Include(o => o.Customer)
        .ThenInclude(c => c.Address)           // JOIN Addresses
    .Where(o => o.Status == "Pending")
    .ToListAsync();

// Filtered include (EF Core 5+) — only include a subset of related items:
var orders = await _db.Orders
    .Include(o => o.Items.Where(i => !i.IsCancelled))
    .ToListAsync();

// Include with AsNoTracking for read-only operations:
var orders = await _db.Orders
    .AsNoTracking()
    .Include(o => o.Items)
    .ToListAsync();

Rule of thumb: Use Include for 1–2 levels of related data when you know you'll need it. Deep or conditional includes can generate complex SQL — consider splitting into multiple queries for very deep graphs.

The N+1 problem occurs when code issues one query to load N parents, then N additional queries — one per parent — to load a related collection. Total: N+1 queries.

// N+1 — lazy loading or iterating without Include:
var customers = await _db.Customers.ToListAsync(); // Query 1: SELECT * FROM Customers

foreach (var customer in customers)
{
    // Query 2..N+1: SELECT * FROM Orders WHERE CustomerId = @id (once per customer!)
    var count = customer.Orders.Count;
    Console.WriteLine($"{customer.Name}: {count} orders");
}
// 100 customers = 101 queries

// Fix 1 — eager load with Include:
var customers = await _db.Customers
    .Include(c => c.Orders)
    .ToListAsync(); // 1 query with JOIN

// Fix 2 — project the count into the initial query:
var summary = await _db.Customers
    .Select(c => new
    {
        c.Name,
        OrderCount = c.Orders.Count  // COUNT(*) in a subquery — 1 SQL statement
    })
    .ToListAsync();

// Fix 3 — split query (EF Core 5+) for very wide result sets:
var customers = await _db.Customers
    .Include(c => c.Orders)
    .AsSplitQuery()   // 2 queries: one for Customers, one for Orders (no cartesian)
    .ToListAsync();

// Detect N+1 in development with EF Core logging:
options.LogTo(Console.WriteLine, LogLevel.Information);
// or use MiniProfiler, EF Core interceptors, or Application Insights

Rule of thumb: Never loop over a collection and access a navigation property inside the loop without first loading it via Include. Enable query logging in development to catch N+1 patterns before they reach production.

Lazy loading automatically loads navigation properties the first time they are accessed by issuing an additional query — convenient but dangerous in web APIs because it hides N+1 queries.

// Enable lazy loading via proxies:
// Install: dotnet add package Microsoft.EntityFrameworkCore.Proxies
builder.Services.AddDbContext<AppDbContext>(options =>
    options
        .UseSqlServer(connectionString)
        .UseLazyLoadingProxies()); // wraps entities in dynamic proxies

// Navigation properties must be virtual:
public class Order
{
    public int Id { get; set; }
    public virtual Customer Customer { get; set; } // loaded lazily on first access
    public virtual ICollection<OrderItem> Items { get; set; } // same
}

// Lazy loading in a controller — silent N+1:
var orders = await _db.Orders.ToListAsync(); // 1 query
foreach (var o in orders)
    Console.WriteLine(o.Customer.Name); // +1 query PER ORDER — hidden!

// Lazy loading after DbContext is disposed:
Order GetOrder()
{
    using var db = new AppDbContext(opts);
    return db.Orders.Find(1);
} // context disposed here
var order = GetOrder();
Console.WriteLine(order.Customer.Name); // ObjectDisposedException

// Prefer explicit eager loading or projections:
var orders = await _db.Orders.Include(o => o.Customer).ToListAsync();

Rule of thumb: Disable lazy loading in web APIs. Use explicit Include or projections so every query is visible and intentional. Lazy loading belongs only in desktop or interactive applications where the context lives long enough.

EF Core provides raw SQL APIs for queries that LINQ can't express — full-text search, stored procedures, database-specific functions, and bulk operations.

// FromSqlRaw — raw SQL that returns entity rows (can compose LINQ on top):
var orders = await _db.Orders
    .FromSqlRaw("SELECT * FROM Orders WHERE Status = {0}", "Pending")
    .Where(o => o.Total > 100)    // LINQ composed on top of the raw SQL
    .OrderBy(o => o.CreatedAt)
    .ToListAsync();

// FromSqlInterpolated — safer, uses parameterized queries automatically:
string status = "Pending";
var orders = await _db.Orders
    .FromSqlInterpolated($"SELECT * FROM Orders WHERE Status = {status}")
    .ToListAsync();

// ExecuteSqlRawAsync — non-query statements (no entity return):
int rows = await _db.Database.ExecuteSqlRawAsync(
    "DELETE FROM Orders WHERE CreatedAt < {0} AND Status = {1}",
    cutoff, "Cancelled");

// ExecuteSqlInterpolatedAsync — same but parameterized via interpolation:
await _db.Database.ExecuteSqlInterpolatedAsync(
    $"UPDATE Products SET Stock = Stock - {quantity} WHERE Id = {productId}");

// Calling a stored procedure that returns entities:
var results = await _db.Orders
    .FromSqlRaw("EXEC sp_GetPendingOrders @CustomerId = {0}", customerId)
    .ToListAsync();

// SQL injection risk — NEVER string-concatenate user input:
string unsafe = $"SELECT * FROM Orders WHERE Status = '{userInput}'"; // vulnerable!
// Always use parameterized overloads.

Rule of thumb: Use parameterized FromSqlInterpolated and ExecuteSqlInterpolatedAsync — not Raw variants — whenever the SQL includes any user-provided values. EF Core's interpolation converts {variable} to DB parameters automatically.

Async EF Core queries (ToListAsync, FirstOrDefaultAsync, etc.) release the thread to the thread pool while waiting for the database, improving throughput under concurrent load.

// Async — thread released to pool while DB executes:
var orders = await _db.Orders
    .Where(o => o.Status == "Pending")
    .ToListAsync(cancellationToken); // thread free during DB wait

// Sync — thread blocks for the entire DB round trip:
var orders = _db.Orders
    .Where(o => o.Status == "Pending")
    .ToList(); // thread occupied — in ASP.NET Core this wastes a thread pool slot

// .Result / .Wait() — causes deadlocks in ASP.NET Core:
var orders = _db.Orders.ToListAsync().Result; // potential deadlock

// Use CancellationToken throughout:
public async Task<List<Order>> GetPendingAsync(CancellationToken ct)
    => await _db.Orders
        .Where(o => o.Status == "Pending")
        .AsNoTracking()
        .ToListAsync(ct); // honours request cancellation (client disconnect)

// Stream large result sets with AsAsyncEnumerable:
await foreach (var order in _db.Orders.AsAsyncEnumerable())
{
    await ProcessAsync(order); // process one at a time — no full buffer in memory
}

Rule of thumb: Always use *Async EF Core methods in ASP.NET Core and pass the CancellationToken from the action or endpoint. Sync calls waste thread pool threads and block under load.

Compiled queries (EF.CompileAsyncQuery) pre-compile the LINQ expression to SQL once at startup and cache the result, skipping query translation on every call. Useful for hot paths called thousands of times per second.

// Standard query — translates LINQ to SQL on every call:
public async Task<Order?> GetOrderAsync(int id)
    => await _db.Orders.FirstOrDefaultAsync(o => o.Id == id);
// EF Core caches most queries internally, but compilation still has overhead.

// Compiled query — translation happens once at class initialisation:
private static readonly Func<AppDbContext, int, Task<Order?>> GetOrderById =
    EF.CompileAsyncQuery(
        (AppDbContext db, int id) =>
            db.Orders.FirstOrDefault(o => o.Id == id));

// Usage — no translation overhead:
var order = await GetOrderById(_db, id);

// With a collection parameter:
private static readonly Func<AppDbContext, IEnumerable<int>, IAsyncEnumerable<Order>> GetOrdersByIds =
    EF.CompileAsyncQuery(
        (AppDbContext db, IEnumerable<int> ids) =>
            db.Orders.Where(o => ids.Contains(o.Id)));

await foreach (var order in GetOrdersByIds(_db, new[] { 1, 2, 3 }))
    Process(order);

When to use compiled queries:

• Hot paths: endpoint called 1,000+ times/second where query planning overhead is measurable.
• The query shape is fixed — no dynamic Where conditions or Include changes.
• Profiling confirms EF's internal cache isn't enough.

Rule of thumb: Don't reach for compiled queries prematurely. EF Core caches most queries internally. Compiled queries pay off only on very hot, fixed-shape paths; measure first.

Split queries (AsSplitQuery) execute a single LINQ query as multiple SQL queries rather than one query with multiple JOINs, avoiding the Cartesian explosion that occurs when including multiple collections.

// Single query with multiple collection includes — Cartesian explosion:
// Orders × Items × Tags → each combination is a row; rows multiply rapidly
var orders = await _db.Orders
    .Include(o => o.Items)   // 10 items each
    .Include(o => o.Tags)    // 5 tags each
    .ToListAsync();
// Generates: SELECT ... FROM Orders JOIN OrderItems JOIN Tags
// 100 orders × 10 items × 5 tags = 5,000 rows transferred

// Split query — 3 separate SQL statements:
var orders = await _db.Orders
    .Include(o => o.Items)
    .Include(o => o.Tags)
    .AsSplitQuery()          // 3 SQL queries: Orders, Items, Tags
    .ToListAsync();
// Each table queried once; no Cartesian product

// Set split query as the global default:
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connectionString,
        sql => sql.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery)));

// Override back to single query when needed:
var orders = await _db.Orders
    .Include(o => o.Customer)    // just one navigation — no explosion
    .AsSingleQuery()
    .ToListAsync();

Trade-offs of split queries:

• Pro: avoids Cartesian explosion for multiple collection includes.
• Con: multiple round trips — inconsistent if data changes between them.
• Con: no transaction by default — wrap in one if consistency matters.

Rule of thumb: Use AsSplitQuery when including two or more collections on the same entity. Use single query when including only reference navigations or one collection.

EF Core translates GroupBy with aggregate functions (Count, Sum, Average, Min, Max) directly to GROUP BY SQL. Without aggregation, it falls back to client-side grouping after loading all rows — a common performance trap.

// GroupBy + aggregate — translated to SQL GROUP BY:
var ordersByStatus = await _db.Orders
    .GroupBy(o => o.Status)
    .Select(g => new
    {
        Status = g.Key,
        Count  = g.Count(),
        Total  = g.Sum(o => o.Total)
    })
    .ToListAsync();
// SQL: SELECT Status, COUNT(*), SUM(Total) FROM Orders GROUP BY Status

// Multiple keys:
var byCustomerAndMonth = await _db.Orders
    .GroupBy(o => new { o.CustomerId, Month = o.CreatedAt.Month })
    .Select(g => new
    {
        g.Key.CustomerId,
        g.Key.Month,
        Count = g.Count(),
        Revenue = g.Sum(o => o.Total)
    })
    .ToListAsync();
// SQL: GROUP BY CustomerId, MONTH(CreatedAt)

// GroupBy without aggregate — EF falls back to client evaluation:
var groups = await _db.Orders
    .GroupBy(o => o.Status)
    .ToListAsync(); // SELECT * FROM Orders — loads ALL rows, groups in memory
// In EF Core 3+, this throws or warns. Always project to aggregates.

// HAVING clause — filter groups by aggregate:
var activeCustomers = await _db.Orders
    .GroupBy(o => o.CustomerId)
    .Where(g => g.Count() >= 5)     // translated to HAVING COUNT(*) >= 5
    .Select(g => new { CustomerId = g.Key, OrderCount = g.Count() })
    .ToListAsync();

Rule of thumb: Always combine GroupBy with at least one aggregate function (Count, Sum, etc.) in the Select. Without it, EF Core 3+ either throws or loads the entire table into memory.

AsNoTracking() instructs EF Core to skip change tracking for a query's results. The returned entities are disconnected from the context — changes to them are not detected by SaveChanges.

// Default (tracked) — entities registered with the change tracker:
var order = await _db.Orders.FindAsync(42);
order.Status = "Shipped";
await _db.SaveChangesAsync(); // detects change, generates UPDATE

// AsNoTracking — entities are plain C# objects, no change tracker registration:
var orders = await _db.Orders
    .AsNoTracking()
    .Where(o => o.Status == "Pending")
    .ToListAsync();
// Faster: no identity map lookup, no snapshot copy, lower memory

// AsNoTrackingWithIdentityResolution (EF Core 5+) — no tracking BUT
// returns the same object instance for the same PK within the query:
var orders = await _db.Orders
    .AsNoTrackingWithIdentityResolution()
    .Include(o => o.Customer)
    .ToListAsync();
// Multiple orders sharing the same customer → same Customer object in memory

// Global default for a context (read-heavy reporting context):
public class ReportDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder b)
        => b.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);
}

Benchmarks typically show 10–20% faster query materialisation and lower GC pressure with AsNoTracking on large result sets.

Rule of thumb: Add AsNoTracking() to every query in a read-only path (API GET endpoints, reports, search). Only track entities you plan to modify and save.

Pagination in EF Core is achieved with .Skip() and .Take() which translate to OFFSET / FETCH NEXT (or equivalent) in SQL. Always combine with OrderBy so results are deterministic.

// Offset-based pagination — page 1 = skip 0, page 2 = skip pageSize, etc.:
public async Task<PagedResult<OrderSummaryDto>> GetOrdersAsync(int page, int pageSize)
{
    var query = _db.Orders
        .AsNoTracking()
        .Where(o => o.Status == "Pending")
        .OrderByDescending(o => o.CreatedAt); // required — results are non-deterministic without it

    var total = await query.CountAsync(); // SELECT COUNT(*) — same filter

    var items = await query
        .Skip((page - 1) * pageSize) // OFFSET
        .Take(pageSize)              // FETCH NEXT
        .Select(o => new OrderSummaryDto
        {
            Id     = o.Id,
            Status = o.Status,
            Total  = o.Total
        })
        .ToListAsync();

    return new PagedResult<OrderSummaryDto>(items, total, page, pageSize);
}
// SQL: SELECT Id, Status, Total FROM Orders WHERE Status='Pending'
//      ORDER BY CreatedAt DESC OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY

// Keyset (cursor) pagination — faster for large offsets:
// Instead of SKIP, filter by the last seen value:
var items = await _db.Orders
    .AsNoTracking()
    .Where(o => o.CreatedAt < lastSeenCreatedAt) // cursor
    .OrderByDescending(o => o.CreatedAt)
    .Take(pageSize)
    .ToListAsync();
// No OFFSET scan — index seek directly to the cursor position

Rule of thumb: Use offset pagination for small datasets where any page can be jumped to. Use keyset (cursor) pagination for large tables or infinite scroll — it scales to millions of rows without degrading as the page number grows.

EF Core translates .Contains(), .StartsWith(), and .EndsWith() to SQL LIKE patterns. For full-text search, use EF.Functions.Like or database-specific functions.

// Contains → LIKE '%value%' (substring search — can't use an index efficiently):
var results = await _db.Products
    .AsNoTracking()
    .Where(p => p.Name.Contains("widget"))
    .ToListAsync();
// SQL: WHERE Name LIKE '%widget%'

// StartsWith → LIKE 'value%' (prefix — can use a B-tree index):
var results = await _db.Products
    .Where(p => p.Sku.StartsWith("WGT-"))
    .ToListAsync();
// SQL: WHERE Sku LIKE 'WGT-%'

// EF.Functions.Like — more control over the pattern:
var results = await _db.Products
    .Where(p => EF.Functions.Like(p.Name, "%wid_et%")) // _ matches one character
    .ToListAsync();

// Case-insensitive comparison (depends on DB collation; explicit on SQLite):
var results = await _db.Products
    .Where(p => EF.Functions.Like(p.Name, "%widget%")) // case-insensitive on SQL Server default collation

// SQL Server full-text search (requires FTS index on the column):
var results = await _db.Products
    .Where(p => EF.Functions.Contains(p.Description, "widget OR gadget"))
    .ToListAsync();
// SQL: WHERE CONTAINS(Description, 'widget OR gadget')

// Warning: never pass unvalidated user input directly into EF.Functions.Like —
// the pattern itself is parameterized, but wildcard injection is still possible:
string safe = $"%{userInput.Replace("%", "[%]").Replace("_", "[_]")}%";

Rule of thumb: Use StartsWith for prefix search on indexed columns. Use Contains sparingly — LIKE '%value%' causes full table scans. Use database full-text search (EF.Functions.Contains) for production text search requirements.

Explicit loading loads a navigation property on demand after the entity is already in memory — using LoadAsync on the entry's reference or collection. Unlike lazy loading it is intentional and visible in code; unlike eager loading it's a separate SQL round trip.

// Load the entity first (navigation not included):
var order = await _db.Orders.FindAsync(42); // SELECT * FROM Orders WHERE Id=42

// Explicit load — second query, triggered intentionally:
await _db.Entry(order)
         .Reference(o => o.Customer)  // reference navigation (single entity)
         .LoadAsync();
// SQL: SELECT * FROM Customers WHERE Id = @customerId

await _db.Entry(order)
         .Collection(o => o.Items)    // collection navigation
         .LoadAsync();
// SQL: SELECT * FROM OrderItems WHERE OrderId = 42

// Filter the collection while loading (EF Core 5+):
await _db.Entry(order)
         .Collection(o => o.Items)
         .Query()                                  // IQueryable on the collection
         .Where(i => !i.IsCancelled)
         .LoadAsync();
// SQL: SELECT * FROM OrderItems WHERE OrderId=42 AND IsCancelled=0

// Comparison:
// Eager  (Include)   — one query with JOIN, loaded before you need it
// Lazy               — automatic, invisible, N+1 risk
// Explicit (LoadAsync) — extra query, but intentional and filterable

// Check before loading to avoid a redundant query:
var entry = _db.Entry(order);
if (!entry.Collection(o => o.Items).IsLoaded)
    await entry.Collection(o => o.Items).LoadAsync();

Rule of thumb: Use explicit loading when you don't know at query time whether you'll need the related data — for example, conditional logic that only sometimes needs the collection. For always-needed data, prefer Include to load everything in one round trip.