.NET & C# Development · Lesson 23 of 92

Build Paginated, Sorted & Searchable Endpoints

How EF Core Translates Queries

When you write a LINQ query against a DbSet<T>, EF Core translates it into SQL at the point you materialise the results (ToListAsync, FirstOrDefaultAsync, SingleAsync, etc.). Until then it's just building an expression tree.

// This line does NOT hit the database yet
IQueryable<Order> query = _db.Orders.Where(o => o.Status == OrderStatus.Confirmed);

// This line executes the SQL
List<Order> orders = await query.ToListAsync(ct);

Understanding this deferred execution is the single most important concept for writing efficient queries.

Level 1 — The Basics

Filtering

// WHERE clause
var pending = await _db.Orders
    .Where(o => o.Status == OrderStatus.Pending)
    .ToListAsync(ct);

// Multiple conditions — AND
var recentHighValue = await _db.Orders
    .Where(o => o.CreatedAt > DateTime.UtcNow.AddDays(-7)
             && o.TotalAmount > 500)
    .ToListAsync(ct);

// OR condition
var urgentOrVip = await _db.Orders
    .Where(o => o.Priority == Priority.Urgent || o.Customer.IsVip)
    .ToListAsync(ct);

// IN — use Contains
var statusList = new[] { OrderStatus.Confirmed, OrderStatus.Shipped };
var inStatuses = await _db.Orders
    .Where(o => statusList.Contains(o.Status))
    .ToListAsync(ct);

// LIKE — use EF.Functions.Like
var search = await _db.Customers
    .Where(c => EF.Functions.Like(c.Name, "%smith%"))
    .ToListAsync(ct);

// Case-insensitive contains (SQL translation depends on DB collation)
var byName = await _db.Customers
    .Where(c => c.Name.Contains("smith"))
    .ToListAsync(ct);

Sorting

// ORDER BY ASC
var byDate = await _db.Orders
    .OrderBy(o => o.CreatedAt)
    .ToListAsync(ct);

// ORDER BY DESC
var newest = await _db.Orders
    .OrderByDescending(o => o.CreatedAt)
    .ToListAsync(ct);

// Multiple sort keys — ThenBy
var sorted = await _db.Orders
    .OrderBy(o => o.CustomerId)
    .ThenByDescending(o => o.TotalAmount)
    .ToListAsync(ct);

Taking and Skipping (Offset Pagination)

var page   = 3;
var size   = 20;
var orders = await _db.Orders
    .OrderByDescending(o => o.CreatedAt)
    .Skip((page - 1) * size)
    .Take(size)
    .ToListAsync(ct);

Warning: EF Core requires OrderBy before Skip. Without ordering, SQL Server can return results in any order — your pages will be inconsistent.

Finding by Primary Key

// FindAsync checks the change tracker first, then the DB — best for single PK lookups
var order = await _db.Orders.FindAsync(orderId, ct);

// FirstOrDefaultAsync is better when you need eager loading or complex conditions
var order2 = await _db.Orders
    .Include(o => o.Items)
    .FirstOrDefaultAsync(o => o.Id == orderId, ct);

Single Result Queries

// Returns null if not found
var order = await _db.Orders.FirstOrDefaultAsync(o => o.Id == id, ct);

// Throws if not found — use when absence is an error
var order2 = await _db.Orders.SingleAsync(o => o.Id == id, ct);

// Throws if more than one result — use for uniqueness checks
var exists = await _db.Orders.AnyAsync(o => o.ReferenceNumber == ref, ct);

Level 2 — Loading Related Data

Eager Loading with Include

// Load orders with their customer
var orders = await _db.Orders
    .Include(o => o.Customer)
    .ToListAsync(ct);

// Multiple includes
var orders2 = await _db.Orders
    .Include(o => o.Customer)
    .Include(o => o.Items)
    .ToListAsync(ct);

// ThenInclude — nested navigation
var orders3 = await _db.Orders
    .Include(o => o.Items)
        .ThenInclude(i => i.Product)
            .ThenInclude(p => p.Category)
    .ToListAsync(ct);

// Include a filtered collection (EF Core 5+)
var orders4 = await _db.Orders
    .Include(o => o.Items.Where(i => i.Quantity > 1))
    .ToListAsync(ct);

Explicit Loading

Use when you conditionally need related data for an entity already in memory:

var customer = await _db.Customers.FindAsync(customerId, ct);

// Only load orders if needed
if (includeOrders)
{
    await _db.Entry(customer)
        .Collection(c => c.Orders)
        .LoadAsync(ct);
}

// Load a reference navigation
await _db.Entry(order)
    .Reference(o => o.Customer)
    .LoadAsync(ct);

Lazy Loading

Avoid lazy loading in production web apps — it causes N+1 queries silently.

// ❌ N+1: for each order, a separate SQL call loads the customer
foreach (var order in orders)
{
    Console.WriteLine(order.Customer.Name);  // fires a query per order
}

// ✅ Load everything upfront
var orders = await _db.Orders
    .Include(o => o.Customer)
    .ToListAsync(ct);

Level 3 — Projections (Select Only What You Need)

Loading entire entities when you only need 2 fields wastes memory and bandwidth. Use Select to project into DTOs.

// ❌ Loads every column of Order + Customer
var orders = await _db.Orders
    .Include(o => o.Customer)
    .ToListAsync(ct);

// ✅ Selects only what the caller needs
var summaries = await _db.Orders
    .Where(o => o.Status == OrderStatus.Confirmed)
    .Select(o => new OrderSummaryDto(
        o.Id,
        o.ReferenceNumber,
        o.Customer.Name,       // EF Core joins automatically — no Include needed
        o.TotalAmount,
        o.CreatedAt))
    .ToListAsync(ct);

When you use Select, EF Core generates a targeted SQL SELECT with only the columns you reference. You do not need Include when projecting — EF Core handles the JOIN automatically.

Projecting nested collections

var detail = await _db.Orders
    .Where(o => o.Id == orderId)
    .Select(o => new OrderDetailDto
    {
        Id          = o.Id,
        Reference   = o.ReferenceNumber,
        CustomerName = o.Customer.Name,
        Status      = o.Status.ToString(),
        Items       = o.Items.Select(i => new OrderItemDto
        {
            ProductName = i.Product.Name,
            Quantity    = i.Quantity,
            UnitPrice   = i.UnitPrice,
            LineTotal   = i.Quantity * i.UnitPrice,   // computed in SQL
        }).ToList(),
        TotalAmount = o.Items.Sum(i => i.Quantity * i.UnitPrice),
    })
    .FirstOrDefaultAsync(ct);

Level 4 — AsNoTracking

EF Core tracks every entity it loads so it can detect changes and call UPDATE in SaveChanges. For read-only queries, this tracking wastes memory.

// ❌ Tracked — EF Core stores a snapshot of every entity for change detection
var orders = await _db.Orders.ToListAsync(ct);

// ✅ Not tracked — faster, less memory, correct for any read-only query
var orders = await _db.Orders
    .AsNoTracking()
    .ToListAsync(ct);

// ✅ Even better — use AsNoTrackingWithIdentityResolution when you have Includes
// (prevents duplicate instances of the same entity in navigation properties)
var orders = await _db.Orders
    .AsNoTrackingWithIdentityResolution()
    .Include(o => o.Customer)
    .ToListAsync(ct);

Rule: always use AsNoTracking() in query handlers. Only omit it in command handlers where you will call SaveChangesAsync.

Level 5 — Aggregates and Grouping

// COUNT
var pendingCount = await _db.Orders
    .CountAsync(o => o.Status == OrderStatus.Pending, ct);

// SUM, MAX, MIN, AVG
var totalRevenue = await _db.Orders
    .Where(o => o.Status == OrderStatus.Confirmed)
    .SumAsync(o => o.TotalAmount, ct);

var maxOrder = await _db.Orders.MaxAsync(o => o.TotalAmount, ct);

// GROUP BY — results in memory (EF Core can translate simple GroupBy to SQL)
var byStatus = await _db.Orders
    .GroupBy(o => o.Status)
    .Select(g => new
    {
        Status = g.Key,
        Count  = g.Count(),
        Total  = g.Sum(o => o.TotalAmount),
    })
    .ToListAsync(ct);

// Orders per customer — with count and last order date
var customerStats = await _db.Orders
    .GroupBy(o => new { o.CustomerId, o.Customer.Name })
    .Select(g => new
    {
        g.Key.CustomerId,
        g.Key.Name,
        OrderCount   = g.Count(),
        TotalSpend   = g.Sum(o => o.TotalAmount),
        LastOrderDate = g.Max(o => o.CreatedAt),
    })
    .OrderByDescending(x => x.TotalSpend)
    .ToListAsync(ct);

Level 6 — Cursor-Based Pagination

Offset pagination (Skip/Take) is simple but degrades as the offset grows — the database must skip millions of rows. Cursor pagination is stable and fast at any scale.

// Cursor: ID of last item seen
public record OrdersPage(List<OrderSummaryDto> Items, Guid? NextCursor, bool HasMore);

public async Task<OrdersPage> GetOrdersPageAsync(Guid? afterId, int pageSize, CancellationToken ct)
{
    var query = _db.Orders
        .AsNoTracking()
        .OrderBy(o => o.Id);   // stable sort key — must be indexed

    if (afterId.HasValue)
        query = query.Where(o => o.Id > afterId.Value);

    // Fetch one extra to determine if there's a next page
    var items = await query
        .Take(pageSize + 1)
        .Select(o => new OrderSummaryDto(o.Id, o.ReferenceNumber, o.TotalAmount))
        .ToListAsync(ct);

    var hasMore    = items.Count > pageSize;
    var page       = items.Take(pageSize).ToList();
    var nextCursor = hasMore ? page.Last().Id : (Guid?)null;

    return new OrdersPage(page, nextCursor, hasMore);
}

Level 7 — Raw SQL Inside EF Core

Sometimes LINQ can't express what you need — complex window functions, CTEs, full-text search. Use raw SQL while keeping EF Core's materialisation:

// FromSql — maps results to an entity type
// ✅ Safe: parameterised automatically when using $"..." interpolation
var orders = await _db.Orders
    .FromSql($"SELECT * FROM Orders WHERE CustomerId = {customerId}")
    .Include(o => o.Items)   // can still chain Include after FromSql
    .ToListAsync(ct);

// ⚠️ FromSqlRaw — you manage parameters manually
var orders2 = await _db.Orders
    .FromSqlRaw("SELECT * FROM Orders WHERE CustomerId = {0}", customerId)
    .ToListAsync(ct);

// For non-entity results, use a keyless entity type
// or drop to raw ADO.NET / Dapper (see next article)

// ExecuteSql — for INSERT, UPDATE, DELETE that bypass change tracking
var affected = await _db.Database.ExecuteSqlAsync(
    $"UPDATE Orders SET Status = 'Archived' WHERE CreatedAt < {cutoff}", ct);

CTE + Window Function Example

var sql = $"""
    WITH RankedOrders AS (
        SELECT *,
               ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY CreatedAt DESC) AS rn
        FROM Orders
        WHERE Status = 'Confirmed'
    )
    SELECT * FROM RankedOrders WHERE rn = 1
    """;

var latestPerCustomer = await _db.Orders
    .FromSqlRaw(sql)
    .AsNoTracking()
    .ToListAsync(ct);

Level 8 — Compiled Queries

If a query runs thousands of times per second, the LINQ-to-SQL translation overhead adds up. Compiled queries cache the translation:

// Compiled query — translation happens once, cached forever
private static readonly Func<AppDbContext, Guid, Task<Order?>> GetOrderById =
    EF.CompileAsyncQuery((AppDbContext db, Guid id) =>
        db.Orders
          .AsNoTracking()
          .Include(o => o.Items)
          .FirstOrDefault(o => o.Id == id));

// Usage — zero translation overhead on every call
public async Task<Order?> GetByIdAsync(Guid id)
    => await GetOrderById(_db, id);

Use compiled queries for hot paths: health checks, cache-warming, high-frequency reads. Overhead without them is ~0.1ms per query — only matters above ~5k queries/sec.

Level 9 — Query Splitting

When you Include multiple collections on a single query, EF Core produces a large Cartesian JOIN. A 100-order result with 10 items each returns 1,000 rows. AsSplitQuery fires separate SQL statements instead:

// ❌ Cartesian explosion — one huge query, lots of duplicate data transferred
var orders = await _db.Orders
    .Include(o => o.Items)
    .Include(o => o.Notes)
    .ToListAsync(ct);

// ✅ Split into 3 SQL queries — efficient, no duplicates
var orders2 = await _db.Orders
    .AsSplitQuery()
    .Include(o => o.Items)
    .Include(o => o.Notes)
    .ToListAsync(ct);

// Set globally for the entire DbContext
protected override void OnConfiguring(DbContextOptionsBuilder options)
    => options.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery);

Trade-off: split queries are no longer a single atomic read. If data changes between the two SQL calls, you may get inconsistent results. Use it for dashboards and read-only views, not for business-critical consistency checks.

Level 10 — Query Performance Checklist

// ✅ 1. Project with Select — never load what you don't use
_db.Orders.Select(o => new { o.Id, o.Status })

// ✅ 2. AsNoTracking on all read queries
_db.Orders.AsNoTracking()

// ✅ 3. Filter early — Where before Include
_db.Orders.Where(o => o.CustomerId == id).Include(o => o.Items)
// NOT:
_db.Orders.Include(o => o.Items).Where(o => o.CustomerId == id)
// Both produce same SQL but the intent is clearer when Where comes first

// ✅ 4. Index your filter columns — EF Core can't fix a missing index
// (in Fluent API):
entity.HasIndex(e => e.Status);
entity.HasIndex(e => new { e.CustomerId, e.CreatedAt });

// ✅ 5. AsSplitQuery for multiple collection Includes
_db.Orders.AsSplitQuery().Include(o => o.Items).Include(o => o.Tags)

// ✅ 6. Use compiled queries for hot paths
EF.CompileAsyncQuery(...)

// ✅ 7. Use AnyAsync instead of Count > 0
await _db.Orders.AnyAsync(o => o.CustomerId == id)
// NOT:
await _db.Orders.Count(o => o.CustomerId == id) > 0

// ✅ 8. Paginate — never return unbounded results
_db.Orders.Take(pageSize).Skip(...)

// ✅ 9. Check generated SQL during development
// Register query logging:
optionsBuilder.LogTo(Console.WriteLine, LogLevel.Information)
              .EnableSensitiveDataLogging();  // dev only

Common Query Anti-Patterns

N+1 problem:

// ❌ N+1: one query for orders, then one query per order for its customer
var orders = await _db.Orders.ToListAsync();
foreach (var order in orders)
    Console.WriteLine(order.Customer.Name);  // fires a query here — N times

// ✅ One query with a JOIN
var orders = await _db.Orders.Include(o => o.Customer).ToListAsync();

Client-side evaluation:

// ❌ EF Core fetches ALL orders to memory, THEN filters in C#
var orders = await _db.Orders.ToListAsync();
var filtered = orders.Where(o => MyCustomMethod(o)).ToList();

// ✅ Keep filtering in the IQueryable pipeline so it becomes a SQL WHERE clause
var filtered = await _db.Orders
    .Where(o => o.Status == OrderStatus.Confirmed)
    .ToListAsync();

Loading too much for a list view:

// ❌ Loads all 50 columns including large text fields, deep navigations
var orders = await _db.Orders
    .Include(o => o.Customer)
    .Include(o => o.Items).ThenInclude(i => i.Product)
    .Include(o => o.Notes)
    .ToListAsync();

// ✅ Project only what the list view renders
var orders = await _db.Orders
    .Select(o => new OrderListDto(
        o.Id, o.ReferenceNumber, o.Status,
        o.Customer.Name, o.TotalAmount))
    .ToListAsync();

Key Takeaways

Deferred execution: LINQ builds a query — .ToListAsync() fires it. Keep everything in the IQueryable pipeline to push logic to SQL
AsNoTracking() on every read-only query — faster, less memory, no accidental updates
Select projections are the single biggest performance lever — load only the columns the caller needs
Include is not needed when projecting — EF Core handles the JOIN automatically
N+1 is the most common bug: always check your Include strategy; eager-load in one query
AsSplitQuery for multiple collection includes — eliminates Cartesian blowup
Compiled queries for hot paths where LINQ translation overhead is measurable
Raw SQL with FromSql for window functions, CTEs, and full-text search that LINQ can't express
Log your SQL in development — the generated SQL is the ground truth for what's actually happening

Lesson Checkpoint

Quick CheckQuestion 1 of 2

When is `AsSplitQuery()` the right tool?

Relationships Done Right: 1-to-1, 1-to-Many, Many-to-Many

Next Lesson

Add Soft Deletes in 5 Minutes With Global Query Filters