Querying with LINQ

The mental model to lock in: a LINQ query is not code that runs - it's a description that EF Core turns into SQL. When you write ctx.Posts.Where(...), nothing touches the database. You're building an expression tree, a blueprint that says "I want posts, filtered like this, sorted like that." EF holds that blueprint and translates it into a single SQL statement only when you actually ask for the results.

Once you internalize "LINQ describes, SQL executes, and execution happens later than you'd think," EF Core querying stops surprising you. Every weird behavior in this phase - why your query didn't run, why it ran twice, why it suddenly broke - traces back to that one idea.

We'll keep using the running blog schema: a Blog has many Posts, each Post has a BlogId, Title, and Id.

A query is a blueprint, not a result

Look at this chain and the SQL it becomes:

var recent = ctx.Posts
    .Where(p => p.BlogId == blogId)
    .OrderByDescending(p => p.Id)
    .Take(10)
    .ToList();

EF translates that into one statement:

SELECT "p"."Id", "p"."BlogId", "p"."Title"
FROM "Posts" AS "p"
WHERE "p"."BlogId" = @blogId
ORDER BY "p"."Id" DESC
LIMIT 10

What just happened: you wrote four C# method calls, and EF folded all of them into a single round-trip to the database. Where became WHERE, OrderByDescending became ORDER BY ... DESC, Take(10) became LIMIT 10. The database filters, sorts, and limits - your app gets back only the 10 rows it asked for, not the whole table.

📝 There are two ways to write LINQ. The method syntax above (.Where(...).OrderBy(...)) is what you'll see most. There's also query syntax, reading more like SQL: from p in ctx.Posts where p.BlogId == blogId select p. They compile to the same thing - pick whichever you find clearer. This guide uses method syntax throughout.

The operators that map to SQL

Most LINQ operators you'd reach for have a direct SQL translation. The common ones:

// Filtering and sorting
var titles = ctx.Posts.Where(p => p.Title.Contains("EF")).ToList();

// Paging: skip the first 20, take the next 10 (page 3, 10 per page)
var page = ctx.Posts.OrderBy(p => p.Id).Skip(20).Take(10).ToList();

// Aggregates - these run in SQL and return a single value
int total = ctx.Posts.Count(p => p.BlogId == blogId);
bool anyDrafts = ctx.Posts.Any(p => p.Title == "");

// Grouping: count posts per blog
var perBlog = ctx.Posts
    .GroupBy(p => p.BlogId)
    .Select(g => new { BlogId = g.Key, Count = g.Count() })
    .ToList();

What just happened: each operator pushed work down into the database. Contains became a SQL LIKE; an In-style filter (Where(p => ids.Contains(p.Id))) becomes a SQL IN (...). Skip/Take became OFFSET/LIMIT - that's your paging. Count and Any came back as COUNT(*) and an EXISTS check, returning one number or one boolean instead of shipping rows across the wire. GroupBy became GROUP BY. None of these loaded the table into memory.

⚠️ Deferred execution: nothing runs until you enumerate

This is the part that bites everyone at least once. A LINQ query over IQueryable<T> is lazy. Building it does nothing. The SQL fires only when you enumerate the results - a specific list of things count as enumerating.

// No database call yet. This is just a blueprint.
var query = ctx.Posts.Where(p => p.BlogId == blogId);

// Still nothing - you can keep composing.
query = query.OrderByDescending(p => p.Id);

// NOW the SQL runs - ToList() enumerates.
var results = query.ToList();

What just happened: the first two lines built and refined an expression tree without touching the database. You could pass query around, add more .Where(...) calls conditionally, and EF would fold them all into one statement. Only ToList() triggered the actual SQL. The triggers that force execution: ToList()/ToListAsync(), First()/FirstOrDefault(), Single(), Count(), Any(), Sum(), and a plain foreach. Until one of those, you're composing - not querying.

⚠️ The flip side of laziness: if you enumerate the same query twice (two foreach loops, or .Count() then .ToList()), you hit the database twice. When you need the results more than once, call ToList() once and reuse the list.

💡 In real apps, prefer the async versions - ToListAsync(), FirstOrDefaultAsync() - so the thread isn't blocked waiting on the database. You'll need using Microsoft.EntityFrameworkCore; for those extension methods.

Select: project to exactly the columns you need

By default, querying ctx.Posts selects every column and materializes full Post entities. Often you don't need the whole row - just an id and a title for a list view. That's what projection with Select is for.

public record PostDto(int Id, string Title);

var list = await ctx.Posts
    .Where(p => p.BlogId == blogId)
    .Select(p => new PostDto(p.Id, p.Title))
    .ToListAsync();

The generated SQL is narrower:

SELECT "p"."Id", "p"."Title"
FROM "Posts" AS "p"
WHERE "p"."BlogId" = @blogId

What just happened: instead of SELECT Id, BlogId, Title, EF generated SELECT Id, Title - only the columns your PostDto actually uses. Less data crosses the wire, and EF skips building full entity objects. For a posts table with a big Content or Body column you don't need in a list, this is a real, measurable win.

💡 For read-only API endpoints (your typical GET /posts), project straight to a DTO. You get a leaner query and avoid leaking your internal entity shape into your API response - two birds, one Select.

AsNoTracking and keeping queries translatable

When EF hands you full entities, it tracks them - keeping a snapshot of each one so it can detect edits later when you call SaveChanges (Phase 5's topic). For a read-only query, that bookkeeping is pure overhead: you're never going to save these objects back.

var posts = await ctx.Posts
    .AsNoTracking()
    .Where(p => p.BlogId == blogId)
    .ToListAsync();

What just happened: AsNoTracking() told EF "don't bother watching these for changes." The query runs faster and uses less memory because EF skips creating change-tracking snapshots. Trade-off: if you edit one of these objects and call SaveChanges, nothing happens - EF isn't watching them. That's exactly what you want for GET endpoints and any read you won't modify. (Projections with Select to a DTO are effectively untracked already.)

⚠️ Client vs server evaluation is the sharp edge here. Almost everything in your LINQ runs as SQL on the database (the server). But if your predicate calls a C# method EF can't translate, it can't push that into SQL.

// EF can translate this - it knows StartsWith → LIKE 'EF%'
var ok = ctx.Posts.Where(p => p.Title.StartsWith("EF")).ToList();

// EF CANNOT translate a custom C# method - this throws at runtime
var bad = ctx.Posts.Where(p => MyCustomCheck(p.Title)).ToList();

What just happened: the first query translated cleanly - StartsWith maps to a SQL LIKE. The second referenced MyCustomCheck, a method that exists only in C#, with no SQL equivalent. Modern EF Core won't silently pull the whole table into memory to run it (older ORMs did, causing brutal performance surprises) - instead it throws, telling you the expression couldn't be translated. The fix: keep Where predicates built from things EF understands (entity properties, comparisons, StartsWith/Contains/==), or pull the data first and do the C# logic afterward on the in-memory list, knowing you've now loaded more rows.

The habit that saves you: watch the SQL EF generates. If a query is slow or behaving oddly, the logged SQL tells you whether the work is happening in the database or accidentally in your app. For a deep dive on diagnosing slow queries, see Why Is My Query Slow?.

Recap

• A LINQ query is a description, not a result. EF builds an expression tree and translates it to one SQL statement.
• Execution is deferred - the SQL fires only when you enumerate: ToList()/ToListAsync(), First(), Count(), Any(), or foreach. Enumerate twice, hit the database twice.
• Where, OrderBy, Skip/Take, Count, Any, Sum, GroupBy, and Contains all map to SQL - the database does the work and returns just the answer.
• Select projects to a DTO, generating a narrower SELECT - fewer columns, less data, leaner read endpoints.
• AsNoTracking() skips change-tracking for read-only queries: faster and lighter.
• ⚠️ Keep predicates translatable - a C# method EF can't turn into SQL throws. Watch the generated SQL to stay in command.

Quick check

[
  {
    "q": "You write `var q = ctx.Posts.Where(p => p.BlogId == 1);` and stop there. What has hit the database?",
    "choices": ["The full filtered result set", "A COUNT of matching rows", "Nothing - execution is deferred until you enumerate", "An empty query that errors"],
    "answer": 2,
    "explain": "Building a LINQ query only creates an expression tree. No SQL runs until you enumerate with ToList(), First(), Count(), foreach, etc."
  },
  {
    "q": "For a read-only GET endpoint that returns posts you won't modify, which is the best fit?",
    "choices": ["ctx.Posts.AsNoTracking().Where(...)", "ctx.Posts.Where(...) with full tracking", "Load every column and filter in C#", "ctx.Posts.Find(id) in a loop"],
    "answer": 0,
    "explain": "AsNoTracking() skips the change-tracker bookkeeping you don't need for read-only data - faster and less memory."
  },
  {
    "q": "What does adding `.Select(p => new PostDto(p.Id, p.Title))` change about the generated SQL?",
    "choices": ["It adds a JOIN to every related table", "It narrows the SELECT to only the Id and Title columns", "It forces the query to run in memory", "It disables deferred execution"],
    "answer": 1,
    "explain": "Projection tells EF to select only the columns the DTO uses, producing a narrower SELECT that ships less data."
  }
]