Querying with LINQ and loading related data
Most database work is reading. This lesson shows how EF Core builds a query from LINQ, when it actually runs, how to load related data with Include, and how projecting into a view model makes pages faster.
- StartBuild the querydb.Orders.Where(...).OrderBy(...): nothing has run yet
- Step 2Add Include or SelectChoose related data or exactly the columns you need
- Step 3ExecuteToListAsync, FirstOrDefaultAsync, CountAsync, AnyAsync
- Step 4Translate to SQLThe provider builds one SQL statement with parameters
- Step 5Database runs itRows come back over the connection
- EndObjects are createdEntities (tracked or not) or your view model objects
The idea in plain words
A LINQ query on a DbSet is an IQueryable: a description of a query, not the results. Adding Where, OrderBy or Select just extends the description. The query runs only when you ask for results, for example with ToListAsync. That is called deferred execution, and it means filters run in the database, not in your app's memory.
Related entities are not loaded unless you ask. Include loads a navigation property in the same query (eager loading). A Select projection loads only the columns your page needs, which is usually the fastest option of all.
Example
// Paging, newest first, read-only
var page = await db.Orders
.AsNoTracking()
.Where(o => o.Total > 1000)
.OrderByDescending(o => o.OrderedAt)
.Skip(20).Take(10)
.ToListAsync();
// Eager loading of related data
var customer = await db.Customers
.Include(c => c.Orders)
.ThenInclude(o => o.Lines)
.FirstOrDefaultAsync(c => c.Id == id);
// Projection into a view model: only these columns are queried
var rows = await db.Orders
.Select(o => new OrderRow(o.Id, o.Customer.Name, o.Total))
.ToListAsync();
public record OrderRow(int Id, string CustomerName, decimal Total);Common mistakes
- Calling ToList() before Where, which loads the whole table and filters in memory.
- The N+1 problem: looping over orders and touching order.Customer for each one, causing a query per row. Use Include or a projection.
- Using Skip and Take without OrderBy, so pages are not stable.
- Tracking entities you only display; use AsNoTracking for read-only queries.
Interview question
What is the N+1 query problem and how do you avoid it in EF Core?
It happens when code runs one query to load a list and then one more query per item to load related data, for example accessing order.Customer inside a loop with lazy loading. For N orders that is N+1 round trips. Avoid it by eager loading with Include, or better by projecting the needed related fields with Select so EF Core fetches everything in one query.
Want the full details? Read the official Microsoft documentation for this topic.