EF Core Performance: Measured on 10,000 Students – Data Access | Lesson 61

Published / updated7 Oct 2026

Introduction

"EF Core is slow" is one of the most repeated sentences in .NET teams. Usually, the tool is not slow; the query is. EF Core does exactly what the LINQ asks, and sometimes the LINQ asks for far more work than the screen needs.

This lesson measures six common situations on SchoolDb's 10,012 students and 120,024 marks. Each one compares the usual way with the faster way, using the same data and the same answer.

By the end of this lesson, you will be able to:

Explain what change tracking costs, and when to switch it off.
Read only the columns a screen needs with projections.
Find and fix the N+1 problem.
Choose between single and split queries.
Change many rows with ExecuteUpdateAsync and ExecuteDeleteAsync.
Use compiled queries and paging where they help.

Why this matters

In the Web API, every request runs EF Core queries. A screen that takes 400 ms instead of 60 ms feels slow to one parent; with 500 parents at 8:45 in the morning, it can take the server down.

Performance work also shows seniority in interviews. "I measured it, found the cause, and fixed it" is a far stronger answer than a list of tips, so this lesson measures everything.

The concept

Example: where the time goes

Every EF Core query pays four costs, and each fix in this lesson removes one of them:

Cost	What it is	Reduced by
Preparing the query	Turning LINQ into SQL (cached after the first time)	Compiled queries
Database work	SQL Server finding and reading rows	Good SQL, indexes (Lessons 26 and 27)
Round trips and data	Each query's trip, and every byte sent back	One query instead of many; fewer columns
Objects in memory	Creating entities, and snapshots for change tracking	`AsNoTracking`, projections

Example: change tracking

When EF Core loads an entity normally, it also stores a snapshot, a copy of every value. Later, SaveChanges compares against it to find what changed (Lesson 59). That is useful when you will edit the data.

Most queries never edit anything: lists, reports, and search results. For those, AsNoTracking() skips the snapshot and the bookkeeping. A projection with Select goes further: it creates no entities at all, only the DTOs you asked for, from only the columns you named.

Example: the N+1 problem

The N+1 problem is one query for a list, then one more query for each item in it: 1 + N queries in total. It usually hides in a loop that looks innocent, or behind lazy loading (Lesson 60).

Think of a teacher who needs 50 students' averages. Walking to the office 50 times, once per student, takes longer than asking once for all 50, even if each walk is short.

Apply it to the SMS

Step 1: a small measuring tool

Every measurement in Program.cs runs twice, each time in a new DbContext. The first run warms up EF Core and SQL Server; the second is timed. A logger counts the SQL commands EF Core sends.

Show src/SchoolApp.Console/Program.cs

src/SchoolApp.Console/Program.cs
// Lesson 61: EF Core performance, measured on 10,012 students and 120,024 marks.
// Every measurement runs once to warm up, then once timed, each in a new DbContext.
// The numbers come from one laptop; yours will differ, but the differences between them will not.
using System.Diagnostics;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Storage;
using SchoolApp.Core.Common;
using SchoolApp.Core.Dtos;
using SchoolApp.Core.Entities;
using SchoolApp.Core.Enums;
using SchoolApp.Data;
using SchoolApp.Data.EfCore;

SqlConnectionFactory connections = SqlConnectionFactory.FromEnvironment();
int queries = 0;  // counts every SQL command EF Core sends
SchoolDbContext NewContext() => SchoolDbContextFactory.Create(connections, _ => queries++);

async Task<T> Measure<T>(string label, Func<SchoolDbContext, Task<T>> work, Func<T, string> describe)
{
    await using (SchoolDbContext warmUp = NewContext())
    {
        await work(warmUp);
    }
    await using SchoolDbContext db = NewContext();
    queries = 0;
    Stopwatch timer = Stopwatch.StartNew();
    T result = await work(db);
    Console.WriteLine($"{label,-34} {timer.ElapsedMilliseconds,5} ms {queries,3} queries  {describe(result)}");
    return result;
}

// 1. Tracking, no tracking, and a projection: the same 10,012 students three ways.
await Measure("Tracked entities", async db =>
    (await db.Students.ToListAsync()).Count + " students, tracked: " + db.ChangeTracker.Entries().Count(), r => r);
await Measure("AsNoTracking entities", async db =>
    (await db.Students.AsNoTracking().ToListAsync()).Count + " students, tracked: " + db.ChangeTracker.Entries().Count(), r => r);
await Measure("Projection to StudentDto", async db =>
    (await db.Students.Select(s => new StudentDto(s.Id, s.AdmissionNumber, s.FirstName + " " + s.LastName,
        s.DateOfBirth, s.Gender.ToString(), s.Status.ToString())).ToListAsync()).Count + " DTOs", r => r);

// 2. The N+1 problem: section 10-C (Id 201) has 50 students. Average mark per student:
//    one query for the list, then one more per student...
await Measure("N+1: one query per student", async db =>
{
    List<Enrollment> enrollments = await db.Enrollments
        .Where(e => e.SectionId == 201 && e.AcademicYear!.IsCurrent).ToListAsync();
    List<decimal?> averages = new List<decimal?>();
    foreach (Enrollment enrollment in enrollments)
    {
        averages.Add(await db.Marks.Where(m => m.EnrollmentId == enrollment.Id && !m.IsAbsent)
                                   .AverageAsync(m => m.MarksObtained));
    }
    return averages;
}, r => $"{r.Count} averages, class average {r.Average():0.00}");

// ...or one query that asks SQL Server for all 50 averages at once.
await Measure("One query with a subquery", db =>
    db.Enrollments
        .Where(e => e.SectionId == 201 && e.AcademicYear!.IsCurrent)
        .Select(e => db.Marks.Where(m => m.EnrollmentId == e.Id && !m.IsAbsent).Average(m => m.MarksObtained))
        .ToListAsync(),
    r => $"{r.Count} averages, class average {r.Average():0.00}");

// 3. Single query or split query: Class 10's sections with every enrollment and student.
await Measure("Include, single query", async db =>
    (await db.Sections.Where(s => s.ClassId == 10)
        .Include(s => s.Enrollments).ThenInclude(e => e.Student).ToListAsync()).Sum(s => s.Enrollments.Count),
    r => $"{r} enrollments");
await Measure("Include, AsSplitQuery", async db =>
    (await db.Sections.Where(s => s.ClassId == 10)
        .Include(s => s.Enrollments).ThenInclude(e => e.Student).AsSplitQuery().ToListAsync()).Sum(s => s.Enrollments.Count),
    r => $"{r} enrollments");

// 4. A bulk change: graduate every Class 10 student of 2026-27. Both versions are rolled back.
await Measure("Load, change, SaveChanges", async db =>
{
    await using IDbContextTransaction transaction = await db.Database.BeginTransactionAsync();
    List<Student> classTen = await db.Students
        .Where(s => s.Enrollments.Any(e => e.AcademicYear!.IsCurrent && e.Section!.ClassId == 10)).ToListAsync();
    foreach (Student student in classTen)
    {
        student.Status = StudentStatus.Graduated;
    }
    int changed = await db.SaveChangesAsync();
    await transaction.RollbackAsync();
    return changed;
}, r => $"{r} students updated");

await Measure("ExecuteUpdateAsync", async db =>
{
    await using IDbContextTransaction transaction = await db.Database.BeginTransactionAsync();
    int changed = await db.Students
        .Where(s => s.Enrollments.Any(e => e.AcademicYear!.IsCurrent && e.Section!.ClassId == 10))
        .ExecuteUpdateAsync(setters => setters.SetProperty(s => s.Status, StudentStatus.Graduated));
    await transaction.RollbackAsync();
    return changed;
}, r => $"{r} students updated");

// 5. A compiled query skips EF Core's query-preparation step: 1,000 lookups each way.
Func<SchoolDbContext, string, Task<Student?>> findCompiled = EF.CompileAsyncQuery(
    (SchoolDbContext db, string admissionNumber) =>
        db.Students.AsNoTracking().SingleOrDefault(s => s.AdmissionNumber == admissionNumber));

await Measure("1,000 lookups, normal", async db =>
{
    int found = 0;
    for (int i = 1; i <= 1000; i++)
    {
        string number = $"ADM-2026-{i:00000}";
        found += await db.Students.AsNoTracking().SingleOrDefaultAsync(s => s.AdmissionNumber == number) is null ? 0 : 1;
    }
    return found;
}, r => $"{r} found");
await Measure("1,000 lookups, compiled", async db =>
{
    int found = 0;
    for (int i = 1; i <= 1000; i++)
    {
        found += await findCompiled(db, $"ADM-2026-{i:00000}") is null ? 0 : 1;
    }
    return found;
}, r => $"{r} found");

// 6. Paging: only one page of DTOs crosses the network.
await using (SchoolDbContext db = NewContext())
{
    PagedResult<StudentDto> page = await new EfStudentRepository(db).GetPageAsync(page: 3, pageSize: 5);
    Console.WriteLine($"Page {page.Page} of {page.TotalPages} ({page.TotalCount} students):");
    foreach (StudentDto student in page.Items)
    {
        Console.WriteLine($"  {student.FullName} ({student.AdmissionNumber})");
    }
}

Output
Tracked entities                     456 ms   1 queries  10012 students, tracked: 10012
AsNoTracking entities                 77 ms   1 queries  10012 students, tracked: 0
Projection to StudentDto              74 ms   1 queries  10012 DTOs
N+1: one query per student            38 ms  51 queries  50 averages, class average 60.02
One query with a subquery              2 ms   1 queries  50 averages, class average 60.02
Include, single query                100 ms   1 queries  1006 enrollments
Include, AsSplitQuery                 68 ms   2 queries  1006 enrollments
Load, change, SaveChanges            348 ms  25 queries  1006 students updated
ExecuteUpdateAsync                    16 ms   1 queries  1006 students updated
1,000 lookups, normal                456 ms 1000 queries  1000 found
1,000 lookups, compiled              363 ms 1000 queries  1000 found
Page 3 of 2003 (10012 students):
  Bhavya Ali (ADM-2026-00622)
  Bhavya Ali (ADM-2026-00682)
  Bhavya Ali (ADM-2026-00742)
  Bhavya Ali (ADM-2026-00802)
  Bhavya Ali (ADM-2026-00862)

The program ran three times on a laptop with LocalDB. The ranges below come from all three runs; your numbers will differ, but the gaps between the pairs will not.

Example: tracking versus no tracking

Loading all 10,012 students as tracked entities took 376 to 456 ms, and left 10,012 entries in the change tracker. With AsNoTracking(), the same query took 65 to 77 ms, with nothing tracked.

That is about six times faster for a one-word change. The SQL was identical; the whole difference was in memory, building snapshots that a read-only screen never uses.

Example: projections

Projecting straight into StudentDto took 59 to 74 ms: about the same as AsNoTracking here, because Students is a narrow table. The projection still wins on wide tables, and it never sends audit columns or soft-delete flags to a screen that does not show them.

Projections are never tracked, so they need no AsNoTracking(). The new GetPageAsync in EfStudentRepository uses one:

src/SchoolApp.Data/EfCore/EfStudentRepository.cs
using Microsoft.EntityFrameworkCore;
using SchoolApp.Core.Common;
using SchoolApp.Core.Dtos;
using SchoolApp.Core.Entities;
using SchoolApp.Core.Enums;

namespace SchoolApp.Data.EfCore;

// Lesson 59: the same operations as Lessons 57 and 58, with EF Core. You write LINQ;
// EF Core writes the SQL, adds the parameters, and creates the Student objects.
// Lesson 60: no more "!s.IsDeleted" here: the soft-delete query filter adds it to every query.
public class EfStudentRepository
{
    private readonly SchoolDbContext _db;

    public EfStudentRepository(SchoolDbContext db)
    {
        _db = db;
    }

    public Task<int> CountActiveAsync(CancellationToken cancellationToken = default)
    {
        return _db.Students.CountAsync(s => s.Status == StudentStatus.Active, cancellationToken);
    }

    public Task<Student?> GetByAdmissionNumberAsync(string admissionNumber, CancellationToken cancellationToken = default)
    {
        return _db.Students.SingleOrDefaultAsync(s => s.AdmissionNumber == admissionNumber, cancellationToken);
    }

    public Task<List<Student>> SearchByLastNameAsync(string lastName, CancellationToken cancellationToken = default)
    {
        return _db.Students
            .Where(s => s.LastName == lastName)
            .OrderBy(s => s.Id)
            .ToListAsync(cancellationToken);
    }

    // Lesson 61: one page of students for a list screen. The projection reads only the DTO's columns
    // (projections are never tracked), and Skip/Take become OFFSET/FETCH, so only one page is sent.
    public async Task<PagedResult<StudentDto>> GetPageAsync(int page, int pageSize, CancellationToken cancellationToken = default)
    {
        if (page < 1)
        {
            throw new ArgumentOutOfRangeException(nameof(page), "Page numbers start at 1.");
        }
        if (pageSize < 1 || pageSize > Paging.MaxPageSize)
        {
            throw new ArgumentOutOfRangeException(nameof(pageSize), $"The page size must be between 1 and {Paging.MaxPageSize}.");
        }

        int total = await _db.Students.CountAsync(cancellationToken);
        List<StudentDto> items = await _db.Students
            .OrderBy(s => s.LastName).ThenBy(s => s.FirstName).ThenBy(s => s.Id)  // a stable order for paging
            .Skip((page - 1) * pageSize)
            .Take(pageSize)
            .Select(s => new StudentDto(s.Id, s.AdmissionNumber, s.FirstName + " " + s.LastName,
                                        s.DateOfBirth, s.Gender.ToString(), s.Status.ToString()))
            .ToListAsync(cancellationToken);

        return new PagedResult<StudentDto>(items, page, pageSize, total);
    }

    // EF Core can still run your own SQL. In SqlQuery, each {value} becomes a parameter, not text,
    // so this is as safe from SQL injection as Lessons 57 and 58.
    public Task<List<ClassListEntryDto>> GetClassListAsync(int classId, string sectionName, CancellationToken cancellationToken = default)
    {
        return _db.Database
            .SqlQuery<ClassListEntryDto>($"EXEC dbo.usp_GetClassList @ClassId = {classId}, @SectionName = {sectionName}")
            .ToListAsync(cancellationToken);
    }
}

Example: fixing N+1

Section 10-C has 50 students. The N+1 version loaded the 50 enrollments, then asked for each student's average mark separately: 51 queries, 27 to 38 ms. The fixed version put the average inside the Select, so SQL Server calculated all 50 in one query: 2 to 11 ms.

Here, the database is on the same laptop, so each round trip is cheap. On a real server across a network, every one of those 50 extra trips takes longer, and the gap grows with it.

How to find N+1 in your own code: count the queries, as this program does, or watch the SQL log. A screen that sends dozens of nearly identical queries almost always has a query inside a loop.

Example: single or split query

Include normally sends one query with joins, and each parent row is repeated for every child row. Including two or more collections multiplies the rows. That is called a cartesian explosion.

AsSplitQuery() sends one query per collection instead. Loading Class 10's sections, enrollments, and students took 83 to 100 ms as one query and 68 to 99 ms split: no clear winner for one collection. Use split queries when you include several collections, and measure.

Split queries have one catch: they are separate queries, so data can change between them. For consistent results, run them in a transaction or accept the small risk.

Example: bulk updates with ExecuteUpdateAsync

Graduating all 1,006 Class 10 students the usual way meant loading them, changing each Status, and calling SaveChanges. That was 1 query to load and 24 batches of updates: 25 commands and 308 to 353 ms.

ExecuteUpdateAsync sent a single UPDATE ... WHERE statement: 1 command and 11 to 36 ms. Nothing was loaded into memory at all. ExecuteDeleteAsync does the same for deletes.

The speed has a price, because these methods go straight to the database. Entity rules, such as Student's constructor checks, are skipped. Tracked objects in memory are not updated.

Audit columns are not set either, unless you set them in the same call. Use these methods for simple, rule-free changes.

Example: compiled queries

EF Core caches the SQL for each query shape, but it still has to compare your LINQ with its cache on every call. EF.CompileAsyncQuery does that work once, up front.

1,000 lookups by admission number took 456 to 508 ms normally, and 363 to 381 ms compiled: about 20% faster. Worth it for a very hot query; not worth the extra code for ordinary ones.

Example: paging

A list screen should never load 10,012 rows to show 20. Skip and Take become OFFSET and FETCH in SQL, so only one page is sent back. GetPageAsync returns the PagedResult record from Lesson 48, with the total count for the page numbers.

Paging needs a stable order. Many bulk students share names, such as several called Bhavya Ali, so the order ends with Id. Without it, the same student could appear on two pages, or on none.

Common mistakes

Tracking queries for read-only screens. Snapshots cost memory and time. Fix: AsNoTracking(), or better, a projection.
Queries inside loops. 1 + N round trips. Fix: one query that returns everything the loop needs.
Loading entities to change one column on many rows. Every row travels twice. Fix: ExecuteUpdateAsync, when no entity rules apply.
Paging without a unique final sort column. Rows repeat or vanish between pages. Fix: end the OrderBy chain with the key.
Optimizing without measuring. Effort goes to the wrong place. Fix: count queries, time them, and read the SQL first.

In real projects

From 16 years of .NET projects

A school's attendance screen took 11 seconds for one section. Nobody had changed it in months; the school had simply grown.

The SQL log showed 1,201 queries for one page: one for the students, and then, for each of 40 students, one query per day of the month. Each query was fast. There were just far too many of them.

One projection with a grouped subquery replaced all of them, and the page loaded in 180 ms. Since then, "how many queries does this screen send?" is the first question I ask about any slow page.

Best practices

Use projections for read-only screens; AsNoTracking() when you need whole entities without edits.
Count and log queries while developing, and look for repeats.
Use ExecuteUpdateAsync and ExecuteDeleteAsync for simple bulk changes.
Page every list, with a stable order that ends in the key.
Measure before and after every optimization, and keep the numbers.

Recap

Change tracking cost about six times the time of AsNoTracking() for 10,012 students.
Projections create no entities and read only the columns you name.
N+1 is a query per item; one query with a subquery replaced 51.
Split queries help with several included collections; measure them.
ExecuteUpdateAsync changed 1,006 rows in one statement, skipping entity rules.
Compiled queries saved about 20% on a hot lookup; paging sends one page only.

Homework

Library module task: Write a shelf report of every book copy with a projection, and show that nothing is tracked. Then, in a transaction you roll back, set the fine on every overdue, unreturned book as on 2026-08-01 with one ExecuteUpdateAsync: 2 for each day late.

A correct result looks like:

Output
Shelf report: 4 copies in 1 query, tracked: 0
Fined 1 overdue issue(s) with 1 UPDATE; open fines now total 24.00

Show the solution

library-assignment/061-library-performance.cs
// Library homework, Lesson 61: a read-only shelf report with a projection, and late fines set for every
// overdue book with one ExecuteUpdateAsync (rolled back).
// Run with:  dotnet run library-assignment/061-library-performance.cs
#:project ../src/SchoolApp.Data/SchoolApp.Data.csproj
#:property PublishAot=false
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Storage;
using SchoolApp.Data;

int queries = 0;
DbContextOptions<LibraryDbContext> options = new DbContextOptionsBuilder<LibraryDbContext>()
    .UseSqlServer(SqlConnectionFactory.FromEnvironment().ConnectionString)
    .LogTo(_ => queries++, [DbLoggerCategory.Database.Command.Name], Microsoft.Extensions.Logging.LogLevel.Information)
    .Options;
await using LibraryDbContext db = new LibraryDbContext(options);

// 1. A report needs no tracking and no entities: project exactly the columns it prints.
var shelf = await db.BookCopies
    .OrderBy(c => c.Book!.Title).ThenBy(c => c.CopyNumber)
    .Select(c => new { c.Book!.Title, c.CopyNumber, c.Status })
    .ToListAsync();
Console.WriteLine($"Shelf report: {shelf.Count} copies in {queries} query, tracked: {db.ChangeTracker.Entries().Count()}");

// 2. Fines for every overdue, unreturned book on 2026-08-01: 2 per day late, one UPDATE statement.
DateOnly asOf = new DateOnly(2026, 8, 1);
await using IDbContextTransaction transaction = await db.Database.BeginTransactionAsync();
queries = 0;
int fined = await db.BookIssues
    .Where(i => i.ReturnedOn == null && i.DueOn < asOf)
    .ExecuteUpdateAsync(setters => setters.SetProperty(
        i => i.FineAmount,
        i => EF.Functions.DateDiffDay(i.DueOn, asOf) * 2m));
int updateCommands = queries;
decimal totalFines = await db.BookIssues.Where(i => i.ReturnedOn == null).SumAsync(i => i.FineAmount);
Console.WriteLine($"Fined {fined} overdue issue(s) with {updateCommands} UPDATE; open fines now total {totalFines}");
await transaction.RollbackAsync();

class Book
{
    public int Id { get; set; }
    public string Title { get; set; } = "";
}

class BookCopy
{
    public int Id { get; set; }
    public int BookId { get; set; }
    public short CopyNumber { get; set; }
    public string Status { get; set; } = "";
    public Book? Book { get; set; }
}

class BookIssue
{
    public int Id { get; set; }
    public int BookCopyId { get; set; }
    public DateOnly DueOn { get; set; }
    public DateOnly? ReturnedOn { get; set; }
    public decimal FineAmount { get; set; }
}

class LibraryDbContext : DbContext
{
    public LibraryDbContext(DbContextOptions<LibraryDbContext> options) : base(options)
    {
    }

    public DbSet<BookCopy> BookCopies => Set<BookCopy>();
    public DbSet<BookIssue> BookIssues => Set<BookIssue>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Book>().ToTable("Books");
        modelBuilder.Entity<BookCopy>(copy =>
        {
            copy.ToTable("BookCopies");
            copy.HasOne(c => c.Book).WithMany().HasForeignKey(c => c.BookId);
        });
        modelBuilder.Entity<BookIssue>(issue =>
        {
            issue.ToTable("BookIssues", table => table.HasTrigger("tr_BookIssues_SyncCopyStatus"));
            issue.Property(i => i.FineAmount).HasPrecision(8, 2);
        });
    }
}

EF.Functions.DateDiffDay becomes SQL Server's DATEDIFF(day, ...), so the fine is calculated inside the UPDATE statement for every matching row at once.

Quiz

Why was AsNoTracking about six times faster for 10,012 students?
1. It sends different SQL
2. It skips building snapshots for change tracking
3. It uses an index
4. It loads fewer rows
Show answer
Answer: B. It skips building snapshots for change tracking The SQL was identical; the time was spent in memory.
What is the N+1 problem?
1. A missing index
2. One query for a list, then one query per item
3. Loading one extra column
4. A query with N joins
Show answer
Answer: B. One query for a list, then one query per item 50 students meant 51 queries; one query with a subquery did the same job.
What does ExecuteUpdateAsync skip?
1. The WHERE clause
2. Loading entities, entity rules, and change tracking
3. Transactions
4. SQL Server triggers
Show answer
Answer: B. Loading entities, entity rules, and change tracking It sends one UPDATE straight to the database.
When does AsSplitQuery help most?
1. With no Include
2. When several collections are included
3. For single-row lookups
4. For ExecuteDelete
Show answer
Answer: B. When several collections are included It avoids multiplying parent rows by every child collection.
Why does GetPageAsync end its OrderBy chain with Id?
1. Id is faster to read
2. To make the order stable, so pages do not repeat or skip rows
3. SQL Server requires it
4. To sort by age
Show answer
Answer: B. To make the order stable, so pages do not repeat or skip rows Many bulk students share the same name.

Interview questions

Questions are grouped by experience level. Click a question to see a model answer. All questions: Data Access interview questions.

Fresher (0-2 yrs)

F What does AsNoTracking do, and when should you use it? concept

It tells EF Core not to keep snapshots of the loaded entities for change tracking. Use it for read-only queries such as lists, reports, and searches. Loading 10,012 students took about 65-77 ms with it and 376-456 ms without it, with identical SQL.

See the example · Permalink

F What is the N+1 problem? concept

Running one query for a list and then one more query for each item: 1 + N round trips. It often hides in a loop or behind lazy loading. Fix it with one query that returns everything, such as an Include or a projection with a subquery: 51 queries became 1 for a section's average marks.

See the example · Permalink

F How do you page results with EF Core? coding

Order the query, then use Skip((page - 1) * pageSize).Take(pageSize), which becomes OFFSET and FETCH in SQL. Return the total count for page numbers, and end the ordering with a unique column such as Id, so rows never repeat or disappear between pages.

See the example · Permalink

Mid (3-5 yrs)

M Why is a projection to a DTO often better than loading entities? concept

It reads only the columns the DTO needs, creates no entities, and is never tracked. It also keeps internal columns, such as audit fields, away from the screen or API. Use entities when you will change and save them; use projections for everything that only reads.

See the example · Permalink

M What are ExecuteUpdateAsync and ExecuteDeleteAsync, and what do they skip? concept

They send one UPDATE or DELETE statement for every row matching a LINQ query, without loading entities. Graduating 1,006 students took 1 command instead of 25. They skip the change tracker, entity rules, and anything your SaveChanges logic does, such as setting audit columns, and tracked objects in memory are not refreshed.

See the example · Permalink

M What is a cartesian explosion, and how does AsSplitQuery help? concept

Including several collections in one query repeats each parent row for every combination of child rows, so the result can grow enormously. AsSplitQuery() loads each collection with its own query. The trade-off is extra round trips and possible inconsistency if data changes between the queries.

See the example · Permalink

Senior (6-9 yrs)

S A page in the SMS is slow, and the team blames EF Core. How do you investigate? scenario

Measure first: log the SQL and count the queries for one page load. Many similar queries mean N+1; one huge query may mean too many includes or columns; a slow single query needs its execution plan and indexes. Then apply the matching fix and measure again with the same data.

How to answer: Give a method, not a list of tips: measure, classify the cause, fix, and verify. Mention tools such as the EF Core command log, query counters, and SQL Server execution plans.

See the example · Permalink

S When would you use a compiled query, and what does it actually save? concept

For very frequent queries with the same shape, such as a lookup by key on every request. It saves EF Core's work of matching the LINQ expression to its cached SQL, not the database time. On SchoolDb, 1,000 lookups were about 20% faster. For ordinary queries, the extra code is not worth it.

How to answer: State what is cached anyway, what compiling removes, and give a measured gain to keep it in proportion.

See the example · Permalink

Architect / Lead (10+ yrs)

A How would you set data-access performance rules for a team, so problems are caught before production? design

Agree on defaults: projections for reads, no lazy loading, paging on every list, bulk methods for simple mass changes. Log slow queries and query counts per request in every environment, add tests or alerts that fail when a request exceeds a query budget, and run load tests on realistic data volumes such as 10,000 students before release.

How to answer: Combine coding standards, automated detection, and production monitoring. Mention a realistic data set and query budgets per endpoint.

See the example · Permalink

Resources

Code for this lesson: git tag lesson-61-ef-core-performance (code repository coming soon)
Files: src/SchoolApp.Console/Program.cs, src/SchoolApp.Data/EfCore/EfStudentRepository.cs, library-assignment/061-library-performance.cs
Video: coming soon

What's next

SchoolApp now has three ways to reach SchoolDb, each with its own class. Next: one interface in front of all three, so the rest of the application never knows which one it is using. Lesson 62: Repository pattern

Want live sessions, code reviews, and interview preparation? Join the next batch at Sahasra Technologies.

Introduction​

Why this matters​

The concept​

Example: where the time goes​

Example: change tracking​

Example: the N+1 problem​

Apply it to the SMS​

Step 1: a small measuring tool​

Example: tracking versus no tracking​

Example: projections​

Example: fixing N+1​

Example: single or split query​

Example: bulk updates with ExecuteUpdateAsync​

Example: compiled queries​

Example: paging​

Common mistakes​

In real projects​

Best practices​

Recap​

Homework​

Quiz​

Interview questions​

Fresher (0-2 yrs)​

Mid (3-5 yrs)​

Senior (6-9 yrs)​

Architect / Lead (10+ yrs)​

Resources​

What's next​

Introduction

Why this matters

The concept

Example: where the time goes

Example: change tracking

Example: the N+1 problem

Apply it to the SMS

Step 1: a small measuring tool

Example: tracking versus no tracking

Example: projections

Example: fixing N+1

Example: single or split query

Example: bulk updates with ExecuteUpdateAsync

Example: compiled queries

Example: paging

Common mistakes

In real projects

Best practices

Recap

Homework

Quiz

Interview questions

Fresher (0-2 yrs)

Mid (3-5 yrs)

Senior (6-9 yrs)

Architect / Lead (10+ yrs)

Resources

What's next