How EF Core Translates LINQ to SQL

Why querying knowledge matters in interviews

Querying is where most EF Core performance problems originate. Interviewers focus on it because the patterns that cause N+1 queries, Cartesian explosions, and full table scans are easy to write accidentally and hard to spot in code review without understanding how EF translates LINQ to SQL.

IQueryable vs IEnumerable — the most important distinction

IQueryable<T> builds a SQL expression; IEnumerable<T> loads data into memory first.

// IQueryable — WHERE runs in the database:
IQueryable<Order> query = _db.Orders
    .Where(o => o.CustomerId == id)   // added to SQL
    .Where(o => o.Total > 100);       // AND added to SQL
var result = await query.ToListAsync(); // SELECT ... WHERE CustomerId=? AND Total>?

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

Returning IQueryable<T> from a method lets callers compose additional filters and projections before the SQL is executed. Returning IEnumerable<T> or List<T> freezes the query at that point.

Deferred execution

EF Core queries are descriptions, not database calls. SQL executes only at terminal operators:

// No SQL yet — just building an expression tree:
var query = _db.Orders
    .Where(o => o.Status == "Pending")
    .OrderBy(o => o.CreatedAt)
    .Take(50);

// Terminal operators — each triggers a SQL call:
var list  = await query.ToListAsync();
var first = await query.FirstOrDefaultAsync();
var count = await query.CountAsync();
var any   = await query.AnyAsync();

Avoid materialising the same query twice — two ToListAsync calls on the same IQueryable produce two SQL round trips.

Projections — load only what you need

Always project to a DTO for read-only paths. Projection reduces the columns in SELECT:

// Loads all columns including blobs:
var orders = await _db.Orders.ToListAsync();

// Only fetches Id, Status, Total:
var summaries = await _db.Orders
    .Where(o => o.CustomerId == id)
    .Select(o => new OrderSummaryDto
    {
        Id     = o.Id,
        Status = o.Status,
        Total  = o.Total
    })
    .ToListAsync();

// Nested projection — one query with automatic JOINs:
var details = await _db.Orders
    .Select(o => new OrderDetailDto
    {
        Id           = o.Id,
        CustomerName = o.Customer.Name,
        Items        = o.Items.Select(i => new ItemDto
        {
            ProductName = i.Product.Name,
            Quantity    = i.Quantity
        }).ToList()
    })
    .ToListAsync();

Eager loading with Include and ThenInclude

Include adds a SQL JOIN for related entities. Without it, navigation properties are null.

// Single level:
var orders = await _db.Orders
    .Include(o => o.Customer)
    .Include(o => o.Items)
    .ToListAsync();

// Deep graph with ThenInclude:
var orders = await _db.Orders
    .Include(o => o.Items)
        .ThenInclude(i => i.Product)
    .Include(o => o.Customer)
        .ThenInclude(c => c.Address)
    .ToListAsync();

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

The N+1 problem

The N+1 problem is the most common EF Core performance issue interviewers ask about.

// N+1 — one query per customer:
var customers = await _db.Customers.ToListAsync(); // Query 1

foreach (var c in customers)
    Console.WriteLine(c.Orders.Count); // Query 2..N+1 (one per customer)
// 100 customers = 101 queries

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

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

Enable SQL logging in development to detect N+1 before it reaches production:

options.LogTo(Console.WriteLine, LogLevel.Information);

Lazy loading — why it's dangerous in web APIs

Lazy loading automatically fires a query when a navigation property is first accessed. In web APIs this means N+1 queries per endpoint call, invisible in the code:

// Enable:
options.UseLazyLoadingProxies();
// Navigations must be virtual.

// Silent N+1 — one query per order:
var orders = await _db.Orders.ToListAsync();
foreach (var o in orders)
    Console.WriteLine(o.Customer.Name); // +1 query per order

// Disposed context exception:
Order GetOrder() { using var db = ...; return db.Orders.Find(1); }
var o = GetOrder();
Console.WriteLine(o.Customer.Name); // ObjectDisposedException

Disable lazy loading in web APIs. Use explicit Include or projections so every database call is visible and intentional.

Split queries — avoiding Cartesian explosion

Including two collection navigations multiplies rows: Orders × Items × Tags.

// Cartesian explosion — 100 orders × 10 items × 5 tags = 5,000 rows:
var orders = await _db.Orders
    .Include(o => o.Items)
    .Include(o => o.Tags)
    .ToListAsync();

// AsSplitQuery — 3 SQL queries, one per table, no Cartesian product:
var orders = await _db.Orders
    .Include(o => o.Items)
    .Include(o => o.Tags)
    .AsSplitQuery()
    .ToListAsync();

// Set as global default:
options.UseSqlServer(conn, sql =>
    sql.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery));

Split query trade-off: multiple round trips, no transaction between them. Use it for multiple collection includes; stick to single query for reference navigations.

Raw SQL

Use raw SQL when LINQ can't express the operation:

// FromSqlInterpolated — returns entities, parameterised automatically:
string status = "Pending";
var orders = await _db.Orders
    .FromSqlInterpolated($"SELECT * FROM Orders WHERE Status = {status}")
    .Where(o => o.Total > 100)   // LINQ composable on top
    .ToListAsync();

// ExecuteSqlInterpolatedAsync — non-query statements:
await _db.Database.ExecuteSqlInterpolatedAsync(
    $"UPDATE Products SET Stock = Stock - {quantity} WHERE Id = {productId}");

// Never concatenate user input:
var sql = $"SELECT * FROM Orders WHERE Status = '{userInput}'"; // SQL injection

Always use parameterised overloads (Interpolated or {0} placeholders, not string concat).

Async queries

Always use async EF Core methods in ASP.NET Core:

// Thread released to pool while DB executes:
var orders = await _db.Orders.ToListAsync(cancellationToken);

// Thread blocks the entire DB round trip:
var orders = _db.Orders.ToList();

// Deadlock risk in ASP.NET Core:
var orders = _db.Orders.ToListAsync().Result;

Pass CancellationToken from the endpoint so abandoned requests (client disconnect) release the connection:

public async Task<List<Order>> GetAsync(CancellationToken ct)
    => await _db.Orders.AsNoTracking().ToListAsync(ct);

Stream large results with AsAsyncEnumerable() — one row at a time, no full buffer:

await foreach (var order in _db.Orders.AsAsyncEnumerable())
    await ProcessAsync(order);

AsNoTracking — the easiest performance win

AsNoTracking() skips change tracker registration. It's 10–20% faster and uses less memory:

var orders = await _db.Orders
    .AsNoTracking()
    .Where(o => o.Status == "Pending")
    .ToListAsync();

Use it on every read-only path (GET endpoints, reports, search). Only track entities you plan to modify and save.

Compiled queries — for very hot paths

Pre-compile LINQ to SQL once at startup for fixed-shape queries called thousands of times per second:

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 per call:
var order = await GetOrderById(_db, 42);

EF Core caches most queries internally; compiled queries help only on very hot, fixed-shape paths. Measure before using.

Recap

The key querying rules for EF Core interviews:

1. Keep queries IQueryable<T> until the final materialisation.
2. Use projections (Select) for read-only paths — only load the columns you need.
3. Use Include / ThenInclude to avoid null navigation properties and N+1 queries.
4. Use AsSplitQuery when including two or more collection navigations.
5. Use AsNoTracking() on every read-only query.
6. Always use *Async methods and pass CancellationToken.
7. Disable lazy loading in web APIs — it hides N+1 queries.
8. Use parameterised raw SQL overloads to prevent SQL injection.