Querying

Write LINQ that EF Core turns into one efficient SQL query: filters, sorting, paging, DTO projections, no-tracking reads and async execution.

Syntax

var page = await db.Orders.AsNoTracking()
    .Where(o => o.CustomerId == id)
    .OrderByDescending(o => o.PlacedAt)
    .Skip(20).Take(20)
    .Select(o => new OrderRow(o.Id, o.Total))
    .ToListAsync(ct);

A LINQ query against a DbSet is an IQueryable<T>: a description of a query held as an expression tree. Nothing reaches the database until you enumerate it, with ToListAsync, FirstOrDefaultAsync, CountAsync, AnyAsync or await foreach. EF translates the whole tree into one SQL statement, caches the translation by the tree's shape, and sends captured local variables as parameters, so Where(o => o.CustomerId == customerId) reuses one query plan for every customer.

IQueryable versus IEnumerable

Operators on IQueryable become SQL. Operators on IEnumerable run in memory over rows already fetched. The boundary is silent: a repository method that returns IEnumerable<Order>, or a call to AsEnumerable(), means every later Where, OrderBy and Take runs on the full table in your process. It works in development with 50 rows and takes the API down with 5 million. Keep composition on IQueryable until the final materialising call.

What cannot be translated

A call EF cannot map to SQL inside Where or OrderBy, such as your own Normalize(name), throws InvalidOperationException: The LINQ expression ... could not be translated when the query runs. EF evaluates on the client only in the final Select, where calling a method on columns already fetched is harmless. Fix a failing filter by expressing it with translatable operations (string.ToUpper, EF.Functions.ILike, date parts) or by storing a normalised column.

Projection and tracking

Querying entities loads every mapped column and, by default, registers each entity in the change tracker with a snapshot of its values. For read endpoints that is wasted work. Select into a DTO fetches only the columns you name, can pull related data without Include, and is never tracked. When you do need entities but will not modify them, add AsNoTracking().

LINQ	SQL (PostgreSQL)	Note
`Where`	`WHERE`	Local variables become parameters
`OrderBy` / `ThenBy`	`ORDER BY`	Required for stable paging
`Skip(40).Take(20)`	`LIMIT @p1 OFFSET @p`	Cost grows with the offset
`ids.Contains(o.Id)`	`o."Id" = ANY (@ids)`	`IN (@ids1, @ids2, ...)` on SQL Server
`AnyAsync`	`EXISTS`	Cheaper than `CountAsync() > 0`
`SingleAsync`	`LIMIT 2`	Throws on zero or two rows

Async and cancellation

Use the async operators in ASP.NET Core and pass the request's CancellationToken, so a client that disconnects cancels the SQL command instead of leaving it running. Synchronous ToList() blocks a thread-pool thread for the whole round-trip. FindAsync(id) is the one lookup that checks the change tracker before querying, which matters inside a unit of work and not at all with no-tracking reads.

Paging with Skip/Take without OrderBy gives rows in whatever order the database returns, so pages overlap and skip items; EF logs a warning about it. Deep pages get slower because the database still reads and discards the skipped rows, which is why the production lessons move to keyset pagination.

Example

using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddDbContext<ShopDb>(o => o.UseNpgsql(builder.Configuration.GetConnectionString("Shop")));
var app = builder.Build();

// GET /customers/42/orders?status=Paid&minTotal=100&page=2&pageSize=20
app.MapGet("/customers/{customerId:int}/orders", async (
    int customerId, string? status, decimal? minTotal, int? page, int? pageSize,
    ShopDb db, CancellationToken ct) =>
{
    var size = Math.Clamp(pageSize ?? 20, 1, 100);
    var number = Math.Max(page ?? 1, 1);

    // Composed as IQueryable: nothing runs until ToListAsync.
    var query = db.Orders.Where(o => o.CustomerId == customerId);
    if (status is not null) query = query.Where(o => o.Status == status);
    if (minTotal is not null) query = query.Where(o => o.Total >= minTotal);

    var total = await query.CountAsync(ct);

    var items = await query
        .OrderByDescending(o => o.PlacedAt).ThenByDescending(o => o.Id) // unique tiebreaker: stable pages
        .Skip((number - 1) * size)
        .Take(size)
        .Select(o => new OrderSummary(
            o.Id,
            o.Number,
            o.Status,
            o.Total,
            o.PlacedAt,
            o.Lines.Count,           // becomes a correlated COUNT, no Include needed
            o.Customer.Email))       // becomes a JOIN, only one column fetched
        .ToListAsync(ct);

    return Results.Ok(new { total, page = number, pageSize = size, items });
});

app.Run();

public sealed record OrderSummary(int Id, string Number, string Status, decimal Total, DateTime PlacedAt, int LineCount, string CustomerEmail);

public sealed class ShopDb(DbContextOptions<ShopDb> options) : DbContext(options)
{
    public DbSet<Order> Orders => Set<Order>();
}

public sealed class Customer
{
    public int Id { get; set; }
    public required string Email { get; set; }
}

public sealed class Order
{
    public int Id { get; set; }
    public required string Number { get; set; }
    public int CustomerId { get; set; }
    public Customer Customer { get; set; } = null!;
    public required string Status { get; set; }
    public decimal Total { get; set; }
    public DateTime PlacedAt { get; set; }
    public List<OrderLine> Lines { get; } = [];
}

public sealed class OrderLine
{
    public int Id { get; set; }
    public int OrderId { get; set; }
    public required string Sku { get; set; }
    public int Quantity { get; set; }
}

When to use it

Serve GET /customers/42/orders?page=3 as one SQL query that returns 20 DTO rows and the total count
Find why a list endpoint loads the entire Orders table: a repository returned IEnumerable and the caller filtered it
Cancel a slow report query when the browser disconnects by passing the request CancellationToken to ToListAsync

More examples

IEnumerable silently moves the filter into memory

<code>IEnumerable<T>.Where</code> and <code>IQueryable<T>.Where</code> look identical at the call site; which one the compiler picks depends only on the static type. Returning a finished <code>List<T></code> from a method that takes the criteria as parameters makes the query boundary explicit and stops callers from composing in the wrong place.

using Microsoft.EntityFrameworkCore;

public sealed class OrderRepository(ShopDb db)
{
    // Broken: the return type is IEnumerable, so the caller's Where/Take run in C#.
    // SQL sent: SELECT ... FROM "Orders"  (every row, every column, all tracked)
    public IEnumerable<Order> AllOrdersSlow() => db.Orders;

    // Fixed: keep it IQueryable, or better, take the criteria and return a finished list.
    public Task<List<Order>> RecentForCustomerAsync(int customerId, int take, CancellationToken ct) =>
        db.Orders
            .AsNoTracking()
            .Where(o => o.CustomerId == customerId)
            .OrderByDescending(o => o.PlacedAt)
            .Take(take)
            .ToListAsync(ct);
}

public static class Usage
{
    public static async Task Show(OrderRepository repository, CancellationToken ct)
    {
        // Sends SELECT with no WHERE: the database scans every order and EF reads and tracks
        // rows until 10 match, which is all of them when the customer has fewer than 10 orders,
        // or when an OrderBy is added.
        var slow = repository.AllOrdersSlow().Where(o => o.CustomerId == 42).Take(10).ToList();

        // One query: WHERE "CustomerId" = @customerId ORDER BY ... LIMIT @p
        var fast = await repository.RecentForCustomerAsync(42, 10, ct);
        Console.WriteLine($"{slow.Count} {fast.Count}");
    }
}

public sealed class ShopDb(DbContextOptions<ShopDb> options) : DbContext(options)
{
    public DbSet<Order> Orders => Set<Order>();
}

public sealed class Order
{
    public int Id { get; set; }
    public int CustomerId { get; set; }
    public DateTime PlacedAt { get; set; }
}

Query syntax and method syntax are the same query

The C# compiler rewrites query expressions into the method calls before EF ever sees them, so both forms produce the same expression tree and the same SQL: <code>GROUP BY</code> with <code>ORDER BY sum(...) DESC</code> and a join for <code>line.Order</code>. <code>ToQueryString()</code> prints the SQL without executing it, which is the quickest way to check a translation in a test or a debugger.

using Microsoft.EntityFrameworkCore;

public static class TopProducts
{
    public static IQueryable<ProductSales> QuerySyntax(ShopDb db, DateTime since) =>
        from line in db.OrderLines
        where line.Order.PlacedAt >= since
        group line by line.Sku into g
        orderby g.Sum(l => l.Quantity) descending
        select new ProductSales(g.Key, g.Sum(l => l.Quantity));

    // What the compiler turns the query expression above into.
    public static IQueryable<ProductSales> MethodSyntax(ShopDb db, DateTime since) =>
        db.OrderLines
            .Where(line => line.Order.PlacedAt >= since)
            .GroupBy(line => line.Sku)
            .OrderByDescending(g => g.Sum(l => l.Quantity))
            .Select(g => new ProductSales(g.Key, g.Sum(l => l.Quantity)));

    public static void PrintSql(ShopDb db)
    {
        var since = new DateTime(2026, 10, 1, 0, 0, 0, DateTimeKind.Utc);
        Console.WriteLine(QuerySyntax(db, since).Take(10).ToQueryString());
        Console.WriteLine(MethodSyntax(db, since).Take(10).ToQueryString()); // identical SQL
    }
}

public sealed record ProductSales(string Sku, int Units);

public sealed class ShopDb(DbContextOptions<ShopDb> options) : DbContext(options)
{
    public DbSet<OrderLine> OrderLines => Set<OrderLine>();
}

public sealed class Order
{
    public int Id { get; set; }
    public DateTime PlacedAt { get; set; }
}

public sealed class OrderLine
{
    public int Id { get; set; }
    public int OrderId { get; set; }
    public Order Order { get; set; } = null!;
    public required string Sku { get; set; }
    public int Quantity { get; set; }
}

The SQL behind the main example

Every captured variable became a parameter, so the database reuses one plan for all customers and pages. EF pages in a subquery first and joins afterwards, so the join and the correlated line count touch only 20 rows. Only projected columns are read, the required <code>Customer</code> navigation became an <code>INNER JOIN</code> (the table is <code>Customer</code>, singular, because that model has no <code>DbSet</code> for it), and filters that were not applied are simply absent.

-- page=3&pageSize=20 on the Npgsql provider (ToQueryString output)
-- @customerId='42'
-- @status='Paid'
-- @minTotal='100' (Nullable = true)
-- @p4='20'
-- @p='40'
SELECT o0."Id", o0."Number", o0."Status", o0."Total", o0."PlacedAt", (
    SELECT count(*)::int
    FROM "OrderLine" AS o1
    WHERE o0."Id" = o1."OrderId"), c."Email"
FROM (
    SELECT o."Id", o."CustomerId", o."Number", o."PlacedAt", o."Status", o."Total"
    FROM "Orders" AS o
    WHERE o."CustomerId" = @customerId AND o."Status" = @status AND o."Total" >= @minTotal
    ORDER BY o."PlacedAt" DESC, o."Id" DESC
    LIMIT @p4 OFFSET @p
) AS o0
INNER JOIN "Customer" AS c ON o0."CustomerId" = c."Id"
ORDER BY o0."PlacedAt" DESC, o0."Id" DESC

Discussion

Be the first to comment on this lesson.