14 Years of Enterprise ASP.NET, Part 2: LINQ, EF Core & SQL Server Without the Footguns

Part 2 of 4 — 14 Years of Enterprise ASP.NET. The data layer is where clean code most often collides with a slow, surprising database. Three lessons that took me years to internalize: LINQ is a contract about where code runs, EF Core is a power tool that punishes misuse, and SQL Server is still smarter than your foreach. Real before/after, the diagnostic commands, and the Mattrx numbers.

---

Recap

This is Part 2 of a four-part series distilling 14+ years of enterprise ASP.NET. Part 1 covered Clean Code, OOP, and SOLID. Now we go down a layer to the place where most production latency actually lives.

Running example throughout: Mattrx — multi-tenant marketing-analytics SaaS, .NET 9 / ASP.NET Core, Azure SQL, ~3,200 req/sec peak. Tables that matter here: Campaigns (~4M rows), Events (~180M), CampaignEvents (~1.2B).

---

Lesson 4 — LINQ: More Than Query Syntax

The single most expensive LINQ misunderstanding is not knowing where your query runs. IQueryable<T> builds an expression tree the provider translates to SQL — it runs in the database. The moment you call .ToList(), .AsEnumerable(), or iterate, execution happens; anything after that runs in your process, in C#, over whatever you already pulled.

IQueryable is "not yet, and maybe in SQL." IEnumerable is "now, in memory." Cross that boundary too early and you drag a million rows into the app to filter five of them. Most "EF is slow" complaints are really "we materialized before we filtered."

Before

// BEFORE — .ToList() pulls EVERYTHING, then filters in C#. Reads 4M rows for 12 results.
var campaigns = await db.Campaigns.ToListAsync(ct);            // materializes ALL campaigns
var active = campaigns
    .Where(c => c.TenantId == tenantId && c.Status == "Active") // now runs in memory
    .OrderByDescending(c => c.CreatedAt)
    .Take(12)
    .ToList();

After

Keep it IQueryable until the filter, projection, and paging are all expressed — so SQL Server does the work and returns 12 rows.

// AFTER — the whole query translates to SQL; the DB returns exactly 12 projected rows
var active = await db.Campaigns
    .Where(c => c.TenantId == tenantId && c.Status == "Active")  // WHERE in SQL
    .OrderByDescending(c => c.CreatedAt)                          // ORDER BY in SQL
    .Take(12)                                                     // TOP 12 in SQL
    .Select(c => new CampaignListItem(c.Id, c.Name, c.Status))   // SELECT only 3 columns
    .ToListAsync(ct);

Two rules that flow from this: filter and page before materializing, and project to a DTO so you pull three columns, not thirty. The Select also stops EF from tracking full entities you don't need.

Mattrx metric: one list endpoint that did .ToListAsync() then filtered in memory was reading ~4M rows per call; expressing the filter in IQueryable dropped it to an index seek returning 12 rows — endpoint p95 2,100 ms → 40 ms, and the query's logical reads fell by ~5 orders of magnitude.

---

Lesson 5 — Entity Framework: Use It Correctly

EF Core is excellent until you use it like a magic ORM that hides the database. Used correctly it's a precise SQL generator with change tracking. The four habits that fixed 90% of our EF pain: no-tracking for reads, project don't load, kill N+1 with explicit loading, and use set-based ExecuteUpdate/ExecuteDelete for bulk writes.

The N+1 trap (the classic)

// BEFORE — N+1: one query for campaigns, then ONE MORE per campaign for its events
var campaigns = await db.Campaigns.Where(c => c.TenantId == t).ToListAsync(ct);
foreach (var c in campaigns)
    c.EventCount = await db.Events.CountAsync(e => e.CampaignId == c.Id, ct); // +N queries
// 200 campaigns = 201 round trips. Looks innocent, murders latency.

// AFTER — one query, aggregated in the database
var rows = await db.Campaigns
    .Where(c => c.TenantId == t)
    .Select(c => new { c.Id, c.Name, EventCount = c.Events.Count() })  // GROUP BY in SQL
    .ToListAsync(ct);                                                  // ONE round trip

Reads: no-tracking + projection

// BEFORE — tracked entities (change tracker overhead) + every column loaded
var posts = await db.Posts.Where(p => p.Status == Published).ToListAsync(ct);

// AFTER — no tracking, only the columns the screen needs
var posts = await db.Posts.AsNoTracking()
    .Where(p => p.Status == Published)
    .Select(p => new PostCard(p.Id, p.Title, p.Slug, p.PublishedAt))
    .ToListAsync(ct);

Bulk writes: stop loading-to-update

// BEFORE — load 50k rows into memory just to flip a flag, then SaveChanges one-by-one-ish
var stale = await db.Sessions.Where(s => s.ExpiresAt < now).ToListAsync(ct);
foreach (var s in stale) s.IsExpired = true;
await db.SaveChangesAsync(ct);            // 50k tracked entities, huge memory + slow

// AFTER — one set-based UPDATE statement, no entities loaded (EF Core 7+)
await db.Sessions.Where(s => s.ExpiresAt < now)
    .ExecuteUpdateAsync(u => u.SetProperty(s => s.IsExpired, true), ct);  // single SQL UPDATE

# diagnostic: see the SQL EF actually generates — the fastest way to catch N+1 / over-fetch
# enable EF command logging, or:
dotnet ef dbcontext optimize     # compiled models; and log with LogTo(Console.WriteLine)

Mattrx metric: the EF cleanup (no-tracking reads, projections, killing N+1, ExecuteUpdate for bulk) was the biggest single driver of dropping DB CPU at peak from 78% to 22% and let us downgrade the Azure SQL tier, saving ~$280/month — without changing a single index.

---

Lesson 6 — SQL Server Still Matters

ORMs let you ignore SQL right up until the day you can't. After 14 years the lesson is blunt: the database is a set-processing engine that will out-perform any loop you write in C#, and you must be able to read an execution plan. The two skills that pay forever are indexing for your actual queries and knowing when work belongs in SQL, not the app.

Before — work dragged into the app

// BEFORE — pull rows to the app and aggregate in C#. The DB can do this 100x faster.
var events = await db.Events.Where(e => e.CampaignId == id).ToListAsync(ct);
var byDay = events.GroupBy(e => e.OccurredAt.Date)
                  .Select(g => new { Day = g.Key, Count = g.Count() });   // in-memory over 180M-row table

After — let the engine do the set work

-- AFTER — aggregate where the data lives; return a handful of rows
SELECT CAST(OccurredAt AS date) AS Day, COUNT(*) AS Count
FROM Events
WHERE CampaignId = @id
GROUP BY CAST(OccurredAt AS date)
ORDER BY Day;

Indexing for the query you actually run

-- the covering index that turned a scan into a seek for the query above
CREATE NONCLUSTERED INDEX IX_Events_Campaign_Date
ON Events (CampaignId, OccurredAt)
INCLUDE (EventType);     -- INCLUDE the columns the query SELECTs to avoid key lookups

-- diagnostic: ALWAYS measure, never guess
SET STATISTICS IO ON;     -- logical reads before vs after the index
-- + read the actual execution plan: seek vs scan, and watch for key lookups / spills

THE MENTAL MODEL

App-side aggregation:  180M rows ──► network ──► C# GroupBy ──► result
                       (move everything, compute in the slow place)

DB-side aggregation:   180M rows ──► index seek + GROUP BY ──► 30 rows ──► network
                       (compute where the data is, move the answer)

Mattrx metric: moving event aggregation into SQL with the covering index took one dashboard query from scanning ~180M rows (logical reads in the millions) to an index seek returning 30 rows — p95 on that endpoint 1,800 ms → 55 ms, and it stopped being the top consumer of DB CPU during month-end.

---

The thread through all three

LINQ   →  decide WHERE code runs (SQL vs memory) — filter before you materialize
EF     →  it's a SQL generator, not magic — project, don't track, batch, watch the SQL
SQL    →  the engine beats your loop — index for real queries, read the plan, measure

Together: the data layer returns exactly the rows you need, computed where the data is.

The teams that struggle with "the database is slow" almost never have a slow database — they have an app that asks the database the wrong way. Learn the boundary (LINQ), respect the tool (EF), and never stop being able to read a plan (SQL).

---

Continue the series

This is Part 2 of 4 in 14 Years of Enterprise ASP.NET.

→ Next: Part 3 — Performance, Microservices & Design Patterns That Earn Their Keep — once the data layer is fast, the next ceilings are in the app tier and the architecture. This is what to do (and what to avoid) at scale.

The full series:

1. Part 1: Clean Code, OOP & SOLID
2. Part 2 (you are here): LINQ, EF Core & SQL Server
3. Part 3: Performance, Microservices & Design Patterns
4. Part 4: Azure, Observability & AI

---

Further reading

• Data Access in .NET — EF Core, LINQ, and Dapper — the full decision guide behind Lessons 4–5.
• EF Core in Production: The N+1 Trap and How to Spot It — Lesson 5's classic bug, diagnosed step by step.
• Indexing Strategies for Postgres at the 100M-Row Mark — the indexing reasoning from Lesson 6 (Postgres, but the plan-reading skill is identical).

---

Staring at a query you think should be fast? Email randhir.jassal@gmail.com with the LINQ and the SET STATISTICS IO output, and I'll tell you whether it's a LINQ boundary, an EF habit, or a missing index.