EF Core — Querying & Projections

Key Points

• AsNoTracking() is your friend for read-only queries — skips change tracking, ~30-50% faster.
• Include / ThenInclude for eager loading. AsSplitQuery() to avoid Cartesian explosion.
• Projection (Select) retrieves only the columns you need — usually faster than loading whole entities.
• IQueryable composes; only materializes on ToListAsync/FirstAsync/etc.
• Avoid Find+lazy-load .Lines — N+1.
• ToQueryString() prints the generated SQL — use it during development.

Concepts (deep dive)

AsNoTracking for read-only queries

var orders = await db.Orders.AsNoTracking().Where(o => o.IsActive).ToListAsync();

Skipping change tracking: - No identity map updates. - No tracking entries created. - Returned entities can't be modified and saved (or rather: SaveChanges won't notice modifications).

For pure reads (controller GET endpoints, reports), always AsNoTracking(). Make it the default if you can — see "default tracking behavior" below.

Default no-tracking

public class AppDb : DbContext
{
    public AppDb(DbContextOptions<AppDb> opts) : base(opts) { }

    protected override void OnConfiguring(DbContextOptionsBuilder b)
        => b.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);
}

Apps that are 95%+ reads benefit. For writes, use .AsTracking() per-query.

Include and the Cartesian explosion

var orders = await db.Orders
    .Include(o => o.Lines)
    .Include(o => o.Customer)
    .Where(o => o.CreatedAt > yesterday)
    .ToListAsync();

EF 6 / older EF Core: emits a single SQL with multiple JOINs — duplicate rows for parents (Cartesian explosion).

EF Core 5+: defaults to single query but warns if it detects explosive cardinality. Switch to split query:

.AsSplitQuery()
// Or globally:
b.UseSqlServer(connStr, o => o.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery));

Split query: one SQL per Include, joined in memory. Slower for small results; vastly better for many-to-many or deep hierarchies.

Projection — only what you need

var summaries = await db.Orders
    .Where(o => o.CreatedAt > yesterday)
    .Select(o => new OrderSummary(o.Id, o.Total, o.Customer.Name))
    .ToListAsync();

Projects to a DTO; SQL selects only the needed columns. Often faster than loading full entities because: - Fewer columns transferred. - No change-tracking entries. - DTOs are simpler than the full entity graph.

IQueryable composition

public static class OrderQueries
{
    public static IQueryable<Order> Active(this IQueryable<Order> q)
        => q.Where(o => o.IsActive);

    public static IQueryable<Order> Recent(this IQueryable<Order> q)
        => q.Where(o => o.CreatedAt > DateTime.UtcNow.AddDays(-7));

    public static IQueryable<OrderSummary> ToSummary(this IQueryable<Order> q)
        => q.Select(o => new OrderSummary(o.Id, o.Total));
}

var data = await db.Orders.Active().Recent().ToSummary().ToListAsync();

Composable extension methods build complex queries without losing translation. The whole chain becomes one SQL.

ToQueryString for debugging

var query = db.Orders.Where(o => o.IsActive).Select(o => o.Id);
Console.WriteLine(query.ToQueryString());
// SELECT [o].[Id] FROM [Orders] AS [o] WHERE [o].[IsActive] = CAST(1 AS bit)

Look at the generated SQL during development. Catches: - Operations that don't translate (run in memory after partial pull). - Missing indexes (compare to query plans). - Cartesian explosions.

Avoid client-side evaluation

var orders = db.Orders.Where(o => MyHelper(o.Status)).ToList();   // ❌ throws (since EF Core 3+)

Older EF would fall back to client evaluation: pull all rows, filter in memory. Modern EF throws unless you explicitly opt in. Sane default.

var ids = new[] { 1, 2, 3 };
var orders = db.Orders.Where(o => ids.Contains(o.Id)).ToList();   // ✅ translates to IN (1,2,3)

Modern EF Core handles in-memory collection parameters efficiently (translated to SQL IN or JOIN based on size).

N+1 — the classic trap

foreach (var order in db.Orders.AsNoTracking().ToList())
{
    foreach (var line in order.Lines)   // ❌ lazy load: N queries
        Process(line);
}

Fix: eager Include:

var orders = db.Orders.Include(o => o.Lines).AsNoTracking().ToList();
foreach (var o in orders)
    foreach (var l in o.Lines) Process(l);   // ✅ data already loaded

Or projection:

var data = db.Orders.AsNoTracking()
    .Select(o => new { OrderId = o.Id, Lines = o.Lines.Select(l => l.Quantity) })
    .ToList();

Compiled queries (.NET 6+ AOT-friendly)

private static readonly Func<AppDb, int, Task<Order?>> _getById =
    EF.CompileAsyncQuery((AppDb db, int id) => db.Orders.SingleOrDefault(o => o.Id == id));

public Task<Order?> GetByIdAsync(int id) => _getById(_db, id);

Compiled queries skip the parser/translator on each call — measurable for hot paths. EF Core 8+ has improved auto-compilation; profile before manual compilation.

Async vs sync

// Async — preferred for I/O
await db.Orders.ToListAsync();

// Sync (legacy)
db.Orders.ToList();

Always async in ASP.NET Core. Sync blocks threadpool threads — see Async/Await — SyncContext & Deadlocks.

Filtered Include

var orders = await db.Orders
    .Include(o => o.Lines.Where(l => l.Quantity > 0))
    .ToListAsync();

Filter inside Include to load only matching children.

Code: correct vs wrong

❌ Wrong: tracking on read-only

var orders = db.Orders.Where(o => o.IsActive).ToList();   // tracked

✅ Correct

var orders = db.Orders.AsNoTracking().Where(o => o.IsActive).ToList();

❌ Wrong: loading whole entities for a list view

var view = db.Orders.Include(o => o.Customer).Include(o => o.Lines).ToList();
// transfers all columns including unused ones

✅ Correct: project to DTO

var view = db.Orders.Select(o => new OrderListItem(o.Id, o.Customer.Name, o.Total)).ToList();

❌ Wrong: N+1

var orders = db.Orders.ToList();
foreach (var o in orders) Console.WriteLine(o.Customer.Name);   // lazy: N queries

✅ Correct: eager

var orders = db.Orders.Include(o => o.Customer).ToList();

Design patterns for this topic

Pattern 1 — "AsNoTracking by default for reads"

• Intent: less memory, less CPU.

Pattern 2 — "Project to DTO for views"

• Intent: narrow data transfer.

Pattern 3 — "AsSplitQuery for collection includes"

• Intent: avoid Cartesian explosion.

Pattern 4 — "IQueryable extension methods"

• Intent: composable query fragments.

Pattern 5 — "ToQueryString in dev"

• Intent: verify SQL.

Pros & cons / trade-offs

When to use / when to avoid

• Use AsNoTracking for read-only queries.
• Use projection for view models.
• Use AsSplitQuery when including collections.
• Avoid loading entities for views — project.
• Avoid lazy loading — explicit Include.

Interview Q&A

Q1. What does AsNoTracking do? Skips the change tracker. Faster for read-only queries; entities returned aren't tracked.

Q2. What's a Cartesian explosion? SQL JOIN duplicates parent rows once per child. With 100 orders × 50 lines each = 5,000 rows where 100 + 5000 = 5,100 with split query.

Q3. Difference between single and split queries? Single: one SQL with JOINs, in-memory dedup. Split: one SQL per Include, in-memory join. Split avoids Cartesian; single avoids round-trips.

Q4. What's projection? Select(o => new DTO(o.X, o.Y)) — generates SQL that returns only those columns. Faster than loading entities.

Q5. How do you debug generated SQL? query.ToQueryString() prints the SQL. Or enable EF Core logging for Microsoft.EntityFrameworkCore.Database.Command.

Q6. What's the N+1 problem? Loading 1 parent row, then issuing N queries to load related children. Fix: Include (eager) or projection.

Q7. Why does client-side evaluation throw in modern EF Core? EF 3+ disabled it by default to prevent silently slow queries. Use translatable expressions or load and filter explicitly.

Q8. When should you use compiled queries? For very hot query paths where parsing overhead matters. Profile first — EF Core 8+ auto-compiles many cases.

Q9. How does Include interact with AsNoTracking? Both work together. AsNoTracking with Include returns the graph but without tracking entries.

Q10. Filtered Include? .Include(o => o.Lines.Where(l => l.Quantity > 0)) — load only matching children.

Gotchas / common mistakes

• ⚠️ Tracking on reads — wasted memory.
• ⚠️ Cartesian explosion without AsSplitQuery.
• ⚠️ Lazy loading — N+1.
• ⚠️ Loading entities for view models — project instead.
• ⚠️ Helper methods inside Where — translation fails.

Further reading

• Querying data
• Change tracking
• AsSplitQuery
• Compiled queries