Learn / .NET API Engineering Path
EF Core Performance Boundaries
Load the data you need, track only what you change, and spot query problems before production.
Course: .NET API Engineering Path. Level: Advanced. Topic: Typed backend.
Stage: advanced - Advanced data access - EF Core query design and performance review. Treat data access as a reviewable system: projections, tracking choices, limits, and generated SQL are part of the contract.
Outcomes
- Use projections for read models.
- Understand tracking costs.
- Inspect generated SQL in review.
- Use Where, Select, OrderBy, GroupBy, Any, All, and Aggregate.
- Explain deferred execution and materialization.
- Avoid common performance and correctness mistakes in collection queries.
Concepts
- projection
- tracking
- include
- compiled query
- LINQ and Collections
- LINQ reads like a data pipeline
- Deferred execution is powerful and surprising
- Modern C# and .NET foundations
- Guided practice
Concept flow
Show how ef core query design and performance review moves from trigger to implementation outcome in .NET APIs.
- Controller
- Query object
- Projection
- Database
- DTO
Session flow
- Model projection (concept, 10 min) — Name the decisions behind projection before writing code.
- Use projections for read models.
- Explain where projection belongs in order management API.
- Build the vertical slice (walkthrough, 18 min) — Implement the smallest useful slice in OrdersQuery.cs.
- Understand tracking costs.
- Connect tracking to a working example.
- Verify and harden (exercise, 12 min) — Add pagination to a list route.
- Inspect generated SQL in review.
- Record one risk or follow-up before moving on.
- LINQ, Projection, Grouping, and Deferred Execution: LINQ reads like a data pipeline (concept, 40 min) — LINQ methods transform sequences. Where filters, Select projects, OrderBy sorts, GroupBy creates buckets, and terminal operations such as ToList, Count, First, Any, and Sum force evaluation.
- Retained source example: Projection and filtering
var activeCustomers = customers
.Where(customer => customer.IsActive)
.OrderBy(customer => customer.Name)
.Select(customer => new CustomerSummary(customer.Id, customer.Name))
.ToList();
This filters first, sorts the remaining items, projects to a smaller shape, and materializes the result.
- LINQ, Projection, Grouping, and Deferred Execution: Deferred execution is powerful and surprising (walkthrough, 40 min) — Most LINQ operators do not run immediately. They create a query object that runs when enumerated. This allows composition, but it also means a query can see later mutations or execute multiple times.
- Retained source example: Materialize when the snapshot matters
var numbers = new List<int> { 1, 2, 3 };
var query = numbers.Where(n => n > 1);
numbers.Add(4);
var snapshot = query.ToArray();
Console.WriteLine(string.Join(", ", snapshot));
Expected output: 2, 3, 4
Because the query is evaluated after Add, the new value appears. Calling ToArray earlier would have captured a snapshot before the mutation.
- LINQ, Projection, Grouping, and Deferred Execution: practices and mistakes (review, 5 min) — Retained guidance from the legacy PTesting lesson.
- Practice: Filter before projecting expensive objects when possible.
- Practice: Materialize with ToList or ToArray when you need a stable snapshot.
- Practice: Use Any instead of Count when you only need to know whether at least one item exists.
- Practice: Keep database-backed LINQ queries simple enough for the provider to translate.
- Avoid: Enumerating the same expensive query multiple times.
- Avoid: Using Single when the data may legitimately contain zero matches.
- Avoid: Calling ToList too early and moving filtering from the database into memory.
- LINQ, Projection, Grouping, and Deferred Execution: references (review, 2 min) — Original references retained from the legacy library.
- LINQ in C#: https://learn.microsoft.com/dotnet/csharp/linq/
- Standard query operators: https://learn.microsoft.com/dotnet/csharp/linq/standard-query-operators/
Code example
C# in OrdersQuery.cs.
public static Task<List<OrderRow>> RecentOrders(AppDbContext db, CancellationToken ct) =>
db.Orders
.AsNoTracking()
.OrderByDescending(order => order.CreatedAt)
.Select(order => new OrderRow(order.Id, order.CustomerEmail, order.TotalCents))
.Take(50)
.ToListAsync(ct);
Walkthrough examples
- EF Core Performance Boundaries in a order management API — A team is extending a production-grade ASP.NET Core order API and needs this lesson's pattern to be clear enough for review, testing, and future maintenance.
- File: OrdersQuery.cs
- File: src/Orders.Application/ef-core-performance.cs
- File: tests/Orders.Api.Tests/ef-core-performance.Tests.cs
- File: docs/dotnet-advanced/ef-core-performance.md
- Start from the provided C# snippet and make the intent visible in names and boundaries.
- Apply the checklist item "Project to DTO" before adding extra behavior.
- Write down how the implementation changes when tracking fails or becomes slow.
- Projection and filtering — This filters first, sorts the remaining items, projects to a smaller shape, and materializes the result.
- Retained source code:
var activeCustomers = customers
.Where(customer => customer.IsActive)
.OrderBy(customer => customer.Name)
.Select(customer => new CustomerSummary(customer.Id, customer.Name))
.ToList();
- Explain the expected behavior.
- Compare the example with the canonical PTLearn implementation.
- Materialize when the snapshot matters — Because the query is evaluated after Add, the new value appears. Calling ToArray earlier would have captured a snapshot before the mutation.
- Retained source code:
var numbers = new List<int> { 1, 2, 3 };
var query = numbers.Where(n => n > 1);
numbers.Add(4);
var snapshot = query.ToArray();
Console.WriteLine(string.Join(", ", snapshot));
- Expected output: 2, 3, 4
- Compare the example with the canonical PTLearn implementation.
- Group totals — Grouping is useful for reports, but be careful with provider-specific translation when the source is a database query.
- Retained source code:
var revenueByRegion = orders
.GroupBy(order => order.Region)
.Select(group => new
{
Region = group.Key,
Revenue = group.Sum(order => order.Total)
})
.OrderByDescending(row => row.Revenue)
.ToList();
- Explain the expected behavior.
- Compare the example with the canonical PTLearn implementation.
Practice
- Replace one Include-heavy read with a projection.
- Log SQL for the endpoint.
- Add pagination to a list route.
- Name a terminal operation: List one LINQ operation that forces evaluation of a deferred query.
- Starter code: Operation: ____
- Expected output: ToList
- Hint: Think of methods that return a concrete value instead of another IEnumerable.
- Reference solution: Examples include ToList, ToArray, Count, Any, First, Single, Sum, and Max.
- Accepted answers: ToList | ToArray | Count | Any | First | Single | Sum | Max
Checklist
- Project to DTO
- Use AsNoTracking
- Limit result size
- Inspect SQL
Quiz prompts
- Why use AsNoTracking for read-only queries? — Read-only projections usually do not need EF Core change tracking.
- A teammate wants to hide projection inside a convenient helper. What should you check first? — Place projection at the boundary that keeps order management API behavior explicit, testable, and reviewable.
- Which artifact best proves this .NET APIs lesson is ready for review? — Production-ready learning needs evidence: a test, trace, command, screenshot, or log that catches the risk again.
- Advanced data access: a teammate says the happy path works, but "Read model projections" is still implicit. What should you ask for before merging? — Read model projections belongs in the advanced stage only when the decision is visible, testable, and tied to a realistic failure mode.
- A reviewer has five minutes to evaluate this advanced .NET APIs slice. Which evidence is strongest? — Upgrade the order list query into an advanced read model with pagination, no-tracking projection, SQL review notes, and one performance regression guard.
- What does deferred execution mean? — Most LINQ operators return a sequence that is evaluated later by enumeration or a terminal operator.
Flashcards
- Advanced data access: what decision does "Read model projections" force you to make? Build DTO-shaped queries that load only what the screen or API contract needs. Evidence prompt: Replace an entity graph read with a projection and capture the generated SQL.
- Advanced data access: what decision does "Query risk review" force you to make? Identify tracking overhead, unbounded result sets, and accidental includes before they reach production. Evidence prompt: Write a query review note that calls out cardinality, indexes, and tracking behavior.
- Advanced data access: what decision does "Indexes, pagination, and N+1 review" force you to make? Tie API pagination rules to database indexes and prove no hidden per-row query appears under load. Evidence prompt: Add an index or pagination constraint and capture before/after SQL for the riskiest list endpoint.
- In .NET APIs, what should you remember about projection? projection matters here because it supports "Use projections for read models.".
- In .NET APIs, what should you remember about tracking? tracking matters here because it supports "Understand tracking costs.".
- In .NET APIs, what should you remember about include? include matters here because it supports "Inspect generated SQL in review.".
- In .NET APIs, what should you remember about compiled query? compiled query matters here because it supports "Use projections for read models.".
Labs
- Ship a ef core performance boundaries slice — Extend a production-grade ASP.NET Core order API with a small but reviewable feature that proves the lesson's architecture in code.
- Upgrade the order list query into an advanced read model with pagination, no-tracking projection, SQL review notes, and one performance regression guard.
- Replace an entity graph read with a projection and capture the generated SQL.
- Write a query review note that calls out cardinality, indexes, and tracking behavior.
- Add an index or pagination constraint and capture before/after SQL for the riskiest list endpoint.
- Replace one Include-heavy read with a projection.
- Log SQL for the endpoint.
- The lab demonstrates the advanced data access outcome without skipping earlier contract evidence.
- Each subtopic has a concrete artifact: Read model projections, Query risk review, Indexes, pagination, and N+1 review.
- The review notes explain how this level changes ownership, verification, or operational risk.
Challenge
- Review-ready ef core performance boundaries (Stretch) — Upgrade the order list query into an advanced read model with pagination, no-tracking projection, SQL review notes, and one performance regression guard.
- All checklist items are either implemented or documented with a reason.
- The change can be understood from OrdersQuery.cs plus one short note.
- The concept diagram names ownership, failure handling, and verification points.
- A teammate could run the verification steps without asking for hidden context.
Canonical lesson URL