EF Core LINQ Queries

1. The N+1 Problem That Kills Prod

JUNIOR CODE:

var users = await _db.Users.ToListAsync();
foreach(var u in users) {
    Console.WriteLine(u.Orders.Count); // 1 SQL per user!
}

What happens: 1 query for users + 1000 queries for orders = 1001 roundtrips. DB CPU 100%. App timeout. You get PagerDuty at 3 AM.

STAFF FIX:

var users = await _db.Users.Include(u => u.Orders).ToListAsync(); // 1 SQL with JOIN

Rule: If you loop and access navigation property, you need Include.

2. AsNoTracking - The 90% Perf Win

// SLOW: Tracks 10k objects. 100MB RAM wasted for read-only page.
var products = await _db.Products.ToListAsync();

// FAST: No tracking. 5x less memory. 2x faster.
var products = await _db.Products.AsNoTracking().ToListAsync();

Rule: If you don't call SaveChanges() after, use AsNoTracking(). Reports, lists, GET APIs = always.

3. First vs Single vs FirstOrDefault

1. The Interview Theory: IQueryable vs IEnumerable

IQueryable: Expression tree. Builds SQL. Runs on DB. _db.Users.Where(u => u.Age > 18) → WHERE Age > 18.

IEnumerable: In-memory. Runs in C#. users.Where(u => u.Age > 18) → Loops in RAM.

Bug: Call .ToList() too early. _db.Users.ToList().Where(...) = downloads whole table. Your app dies.

2. The #1 Interview Question: "Fix N+1"

Your Answer: "N+1 happens when I lazy-load navigation props in a loop. Each access = 1 DB query. Fixes: 1. Eager loading: .Include(u => u.Orders).ThenInclude(o => o.Items). 1 SQL with JOINs. 2. Explicit loading: _db.Entry(user).Collection(u => u.Orders).LoadAsync(). 3. Projection: .Select(u => new { u.Name, OrderCount = u.Orders.Count }). Best perf, no entities tracked. I check with SQL Profiler or EnableSensitiveDataLogging()."

3. LINQ That Doesn't Translate - Silent Killer

// BAD: Client evaluation. Downloads all users, filters in C#.
var users = await _db.Users
   .Where(u => MyCSharpMethod(u.Name)) // Can't translate
   .ToListAsync();

// GOOD: Translate to SQL
var users = await _db.Users
   .Where(u => u.Name.StartsWith("A")) // Translates to LIKE 'A%'
   .ToListAsync();

Staff Rule: EF Core 3+ throws if can't translate. EF Core 2 silently ran in memory. Always check warnings.

4. Perf Tricks Seniors Use

5. ThenInclude vs Manual Join

// EASY: 1 query, but can be slow with many JOINs
var users = await _db.Users
   .Include(u => u.Orders)
       .ThenInclude(o => o.Items)
   .ToListAsync();

// HARD: 2 queries, faster for large data
var users = await _db.Users.ToListAsync();
var userIds = users.Select(u => u.Id);
await _db.Orders.Where(o => userIds.Contains(o.UserId)).LoadAsync();

Theory: Include = JOIN. Too many JOINs = Cartesian explosion. 100 users * 100 orders * 100 items = 1M rows. AsSplitQuery or manual load fixes it.