LINQ queries in EF Core are deferred — writing a query with Where() and OrderBy() builds an expression tree but does not execute any SQL. SQL is only sent to the database when a terminal operator (ToListAsync(), FirstOrDefaultAsync(), CountAsync()) is called. This deferred execution lets you compose queries incrementally. Understanding eager loading (Include()), projection (Select()), and the difference between tracked and untracked queries is what separates efficient EF Core usage from accidental N+1 query disasters.
Essential Query Patterns
// ── AsNoTracking — for all read-only queries ───────────────────────────────
var posts = await db.Posts
.AsNoTracking() // skip change tracking — ~30% faster for reads
.Where(p => p.IsPublished)
.OrderByDescending(p => p.PublishedAt)
.Skip((page - 1) * size)
.Take(size)
.ToListAsync(ct);
// ── Include — eager loading of navigation properties ─────────────────────
var post = await db.Posts
.AsNoTracking()
.Include(p => p.Author) // JOIN → Users table
.Include(p => p.Tags) // JOIN → PostTags + Tags tables
.Include(p => p.Comments)
.ThenInclude(c => c.Author) // further include on Comments
.FirstOrDefaultAsync(p => p.Slug == slug, ct);
// ── AsSplitQuery — avoid Cartesian product for multiple collection Includes ──
var post = await db.Posts
.AsNoTracking()
.Include(p => p.Tags) // collection navigation
.Include(p => p.Comments) // another collection navigation
.AsSplitQuery() // run separate SQL per Include instead of one huge JOIN
.FirstOrDefaultAsync(p => p.Id == id, ct);
// ── Select — project to DTO directly in SQL (best performance) ────────────
var summaries = await db.Posts
.AsNoTracking()
.Where(p => p.IsPublished)
.OrderByDescending(p => p.PublishedAt)
.Select(p => new PostSummaryDto
{
Id = p.Id,
Title = p.Title,
Slug = p.Slug,
AuthorName = p.Author!.DisplayName, // SQL JOIN, no entity load
PublishedAt = p.PublishedAt!.Value,
CommentCount = p.Comments.Count(), // SQL COUNT
Tags = p.Tags.Select(t => t.Name).ToList(),
})
.Skip((page - 1) * size)
.Take(size)
.ToListAsync(ct);Note: When you call
Include(p => p.Tags).Include(p => p.Comments), EF Core by default generates a single SQL query with JOINs for both collections. If a post has 10 tags and 20 comments, the JOIN produces 200 result rows (10 × 20 Cartesian product), which EF Core then deduplicates in memory. For large collections this is very inefficient. AsSplitQuery() splits this into 3 separate SQL queries (one per entity), trading extra database round trips for dramatically fewer result rows. Use AsSplitQuery() when including multiple collection navigations on the same query.Tip: Use
FindAsync(id) instead of FirstOrDefaultAsync(p => p.Id == id) when looking up by primary key. FindAsync() checks the change tracker first — if the entity is already loaded in the current DbContext, it returns it from memory without a database query. This is the correct way to access entities by PK in a request that may have loaded the entity earlier. For all other queries (by non-PK columns), use FirstOrDefaultAsync() or SingleOrDefaultAsync().Warning: The N+1 query problem is the most common EF Core performance issue. It occurs when you load a list of entities and then, for each entity, execute a query to load related data:
foreach (var post in posts) { var comments = await db.Comments.Where(c => c.PostId == post.Id).ToListAsync(); }. This is 1 query for posts + N queries for comments. The fix: use Include(p => p.Comments) to load both in one query, or use a Select() projection. Enable SQL logging in development — N+1 is immediately visible as repeated identical queries in the log.Common Query Operations
// ── Count ─────────────────────────────────────────────────────────────────
int total = await db.Posts.CountAsync(p => p.IsPublished, ct);
// ── Any (existence check) ─────────────────────────────────────────────────
bool slugExists = await db.Posts.AnyAsync(p => p.Slug == slug, ct);
// ── Single item by PK ─────────────────────────────────────────────────────
var post = await db.Posts.FindAsync(new object[] { id }, ct);
// ── Single item with navigation ───────────────────────────────────────────
var post = await db.Posts
.Include(p => p.Author)
.FirstOrDefaultAsync(p => p.Id == id, ct);
// ── Filtered navigation ───────────────────────────────────────────────────
// EF Core 5+ supports filtered includes
var post = await db.Posts
.Include(p => p.Comments.Where(c => !c.IsDeleted)
.OrderByDescending(c => c.CreatedAt)
.Take(10))
.FirstOrDefaultAsync(p => p.Id == id, ct);Common Mistakes
Mistake 1 — Loading entities for read-only operations without AsNoTracking()
❌ Wrong — every loaded entity is tracked; waste of memory and change tracking overhead for read-only lists.
✅ Correct — always add .AsNoTracking() for read-only queries.
Mistake 2 — Using Include() for multiple collections without AsSplitQuery() (Cartesian explosion)
❌ Wrong — 50 posts × 10 tags × 20 comments = 10,000 result rows for what should be 80 rows of data.
✅ Correct — add .AsSplitQuery() when including more than one collection navigation.