C# Academy · Lesson

Compiled Queries & Performance

Boost query performance using EF.CompileQuery, AsNoTracking, and projection with Select.

Compiled Queries & Performance is a free C# Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the C# Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

EF Core Query Compilation

Every time EF Core executes a LINQ query, it must parse the expression tree, translate it to SQL, and validate it. For hot paths this overhead adds up. EF Core provides several tools to minimize it.

AsNoTracking for Read-Only Queries

AsNoTracking() skips change tracker registration. For read-only queries (API responses, reports), this is the single biggest easy win — typically 2–5x faster and less memory.

// With tracking (default) — for entities you'll modify
var tracked = await _db.Products.FirstOrDefaultAsync(p => p.Id == 1);

// Without tracking — for read-only
var dto = await _db.Products
    .AsNoTracking()
    .Where(p => p.IsActive)
    .Select(p => new ProductDto { Id = p.Id, Name = p.Name })
    .ToListAsync();

Compiled Queries

EF.CompileAsyncQuery pre-compiles a LINQ query into a delegate. The translation happens once at startup; subsequent calls skip the compilation step entirely.

// Define once, reuse many times
private static readonly Func<AppDbContext, int, Task<Product?>> GetProductById =
    EF.CompileAsyncQuery(
        (AppDbContext db, int id) =>
            db.Products.AsNoTracking().FirstOrDefault(p => p.Id == id));

// Usage
var product = await GetProductById(_db, 42);

Projection with Select for Performance

Always project to the exact fields you need. Selecting a DTO instead of a full entity reduces the columns fetched, network payload, and GC pressure.

// Load entire entity (over-fetch)
var all = await _db.Products.ToListAsync();

// Projection: only fetch needed columns
var names = await _db.Products
    .AsNoTracking()
    .Select(p => new { p.Id, p.Name, p.Price })
    .ToListAsync();
// SQL: SELECT Id, Name, Price FROM Products

Index Hints and Raw Query Optimization

EF Core respects database indexes you configure with HasIndex(). Compound indexes, filtered indexes, and included columns all affect query plan performance.

// Configure compound index in OnModelCreating
modelBuilder.Entity<Order>()
    .HasIndex(o => new { o.CustomerId, o.Status })
    .HasFilter("[Status] = 0")  // filtered index
    .IncludeProperties(o => o.PlacedAt);

// The query below will use this index efficiently
var pending = await _db.Orders
    .AsNoTracking()
    .Where(o => o.CustomerId == 5 && o.Status == OrderStatus.Pending)
    .ToListAsync();

Pagination with Skip and Take

Always paginate large result sets. Skip and Take translate to SQL OFFSET/FETCH, avoiding loading thousands of rows into memory.

int page = 2, pageSize = 25;

var products = await _db.Products
    .AsNoTracking()
    .Where(p => p.IsActive)
    .OrderBy(p => p.Name)
    .Skip((page - 1) * pageSize)
    .Take(pageSize)
    .ToListAsync();
// SQL: ... ORDER BY Name OFFSET 25 ROWS FETCH NEXT 25 ROWS ONLY

Counting Efficiently

Use CountAsync() or AnyAsync() instead of loading records just to count them. AnyAsync() is even faster than COUNT when you just need to check existence.

// Total count for pagination
int totalCount = await _db.Products
    .Where(p => p.IsActive)
    .CountAsync();

// Existence check — faster than Count > 0
bool hasExpensive = await _db.Products
    .AnyAsync(p => p.Price > 1000m);
// SQL: IF EXISTS (SELECT 1 FROM ...) SELECT 1

Bulk Inserts with AddRange

Use AddRange to add multiple entities at once. EF Core batches the INSERTs into fewer round-trips, controlled by MaxBatchSize in the provider.

var newProducts = Enumerable.Range(1, 1000)
    .Select(i => new Product { Name = $"Product {i}", Price = i * 1.5m })
    .ToList();

_db.Products.AddRange(newProducts);
await _db.SaveChangesAsync();
// Batched: typically 100-1000 rows per round-trip

EF.Functions for Database Functions

EF.Functions exposes database-specific functions (LIKE, date parts, full-text search) that EF Core translates to SQL rather than evaluating in memory.

// LIKE pattern search (SQL-side)
var results = await _db.Products
    .Where(p => EF.Functions.Like(p.Name, "%keyboard%"))
    .AsNoTracking()
    .ToListAsync();

// Date difference (SQL-side)
var recentOrders = await _db.Orders
    .Where(o => EF.Functions.DateDiffDay(o.PlacedAt, DateTime.UtcNow) <= 7)
    .ToListAsync();

Diagnosing Slow Queries

Enable EF Core query logging to see generated SQL. Use EnableSensitiveDataLogging() in development to see parameter values.

builder.Services.AddDbContext<AppDbContext>(opt =>
    opt.UseSqlServer(connStr)
       .LogTo(Console.WriteLine, LogLevel.Information)
       .EnableSensitiveDataLogging() // dev only!
       .EnableDetailedErrors());     // dev only!

Real-World: Optimized List Endpoint

A production-ready list endpoint combines AsNoTracking, projection, indexes, and pagination for maximum performance.

app.MapGet("/products", async (
    AppDbContext db,
    string? search,
    int page = 1, int size = 20) =>
{
    var query = db.Products
        .AsNoTracking()
        .Where(p => p.IsActive);

    if (!string.IsNullOrEmpty(search))
        query = query.Where(p => EF.Functions.Like(p.Name, $"%{search}%"));

    var total = await query.CountAsync();
    var items = await query
        .OrderBy(p => p.Name)
        .Skip((page - 1) * size).Take(size)
        .Select(p => new { p.Id, p.Name, p.Price })
        .ToListAsync();

    return Results.Ok(new { total, items });
});

Quick Check

What is the main benefit of EF.CompileAsyncQuery over a regular LINQ query?

Recap: Compiled Queries & Performance

Key takeaways:

AsNoTracking(): skip change tracker for read-only — fastest easy win
EF.CompileAsyncQuery: pre-compile hot queries to delegates
Projection with Select: fetch only needed columns
Proper indexes (HasIndex) dramatically improve query plans
Paginate with Skip/Take; use AnyAsync for existence checks
Enable query logging in dev to catch slow or unexpected SQL

Frequently asked questions

Is the “Compiled Queries & Performance” lesson free?

Yes — the full text of “Compiled Queries & Performance” is free to read here on the web, and the C# Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the C# Academy course, upgrade to CoddyKit PRO.

What will I learn in “Compiled Queries & Performance”?

Boost query performance using EF.CompileQuery, AsNoTracking, and projection with Select. You practise C# Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start C# Academy?

No prior experience is required. C# Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Compiled Queries & Performance” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this C# Academy lesson?

Yes. Every C# Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

Eager, Lazy & Explicit Loading
Raw SQL, Stored Procedures & Interpolation
Compiled Queries & Performance
Global Query Filters & Owned Entities

← Back to C# Academy