LINQ Queries & Relationships

Querying a DbSet with LINQ#

Any DbSet<T> is an IQueryable<T> — LINQ operators compose into a single SQL query, only actually executing against the database once you enumerate the result (ToListAsync(), FirstOrDefaultAsync(), a foreach, etc.):

var cheapProducts = await db.Products
    .Where(p => p.Price < 50)
    .OrderBy(p => p.Name)
    .ToListAsync();

This is EF Core's deferred execution — the query variable up to .ToListAsync() doesn't run anything; it builds up an expression tree that EF Core translates into a single SQL SELECT ... WHERE ... ORDER BY statement only when you actually ask for the results.

Projecting with Select#

.Select(...) shapes the result into exactly the fields you need — critical for performance, since EF Core generates SQL that only selects those specific columns instead of every column on the entity:

public record ProductSummary(int Id, string Name, decimal Price);

var summaries = await db.Products
    .Where(p => p.StockQuantity > 0)
    .OrderByDescending(p => p.Price)
    .Select(p => new ProductSummary(p.Id, p.Name, p.Price))
    .ToListAsync();

Composing Where, OrderBy, and Select like this — filter, then sort, then shape — is the everyday realistic query pattern in EF Core, and all three fold into one round trip to the database.

Modeling a relationship#

Add a Category that a Product belongs to — a classic one-to-many:

public class Category
{
    public int Id { get; set; }
    public string Name { get; set; } = "";

    public List<Product> Products { get; set; } = new();   // one category has many products
}

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public decimal Price { get; set; }

    public int CategoryId { get; set; }              // foreign key
    public Category Category { get; set; } = null!;  // navigation property
}

EF Core's conventions infer the whole relationship from this shape alone: CategoryId is recognized as a foreign key because it matches {NavigationProperty}Id, and Category.Products / Product.Category are recognized as the two sides of one relationship.

Eager loading with Include#

By default, querying Products does not load each product's Category — the Category navigation property is left null unless you explicitly ask for it with .Include(...):

// Without Include: product.Category is null
var products = await db.Products.ToListAsync();

// With Include: product.Category is fully loaded, in the same query
var productsWithCategory = await db.Products
    .Include(p => p.Category)
    .ToListAsync();

Include generates a SQL JOIN so the related data comes back in the same round trip, instead of one query per product (see "Common mistakes" below for what happens if you forget it and access the navigation property anyway).

A realistic composed query#

Filter, include a relationship, sort, and project — all in one query:

var results = await db.Products
    .Include(p => p.Category)
    .Where(p => p.Category.Name == "Electronics" && p.Price < 500)
    .OrderBy(p => p.Price)
    .Select(p => new
    {
        p.Name,
        p.Price,
        CategoryName = p.Category.Name
    })
    .ToListAsync();

EF Core translates all of this — the join, the filter (including one that reaches through the relationship into Category.Name), the sort, and the projection — into a single SQL statement.

Loading multiple levels deep with ThenInclude#

For a chain of relationships (say, Order → OrderItem → Product), .ThenInclude(...) continues loading one more level:

var orders = await db.Orders
    .Include(o => o.Items)
        .ThenInclude(i => i.Product)
    .ToListAsync();

Common mistakes#

• Accessing a navigation property that wasn't Included and getting null (or an empty collection) instead of an exception — easy to misread as "there's no related data" when really it just wasn't loaded.
• Triggering N+1 query patterns by looping over a result and separately querying each item's related data inside the loop, instead of a single Included query up front.
• Filtering after materializing (.ToList() then .Where(...)) — that pulls every row into memory first and filters in C#, instead of letting EF Core translate the filter into SQL and only return matching rows.

Interview questions#

Q: What does .Include() do, and what happens if you forget it? It tells EF Core to eagerly load a related navigation property in the same query (via a SQL JOIN), instead of leaving it null/empty. Forgetting it doesn't error — the related property is simply left unpopulated, which is a common source of silent NullReferenceExceptions or missing data later in the code.

Q: What's the difference between deferred execution and something like ToList()? Building a query with Where/OrderBy/Select doesn't touch the database at all — it builds an expression tree. The query only actually executes, as one SQL statement, when you enumerate it: calling ToList()/ToListAsync(), FirstOrDefault(), iterating with foreach, and so on.

Q: Why should you avoid filtering after calling ToList()? ToList() (or ToListAsync()) materializes every row the query currently describes into memory, and any LINQ called after that point runs in plain C#, not translated SQL. Filtering before materializing lets the database do the filtering, transferring far less data over the wire and letting it use indexes.