DEV Community

kirandeepjassal-crypto
kirandeepjassal-crypto

Posted on • Originally published at prepstack.co.in

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

#csharp #database #dotnet #performance

Originally published at prepstack.co.in

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. Running example: Mattrx — multi-tenant marketing-analytics SaaS, .NET 9 / ASP.NET Core, Azure SQL, ~3,200 req/sec peak. Tables that matter: Campaigns (~4M), Events (~180M), CampaignEvents (~1.2B).

Lesson 4 — LINQ: it's about WHERE code runs

The single most expensive LINQ misunderstanding is not knowing where your query runs. IQueryable is "not yet, and maybe in SQL." IEnumerable is "now, in memory." The moment you call .ToList(), execution happens; everything after runs in C# over what you already pulled.

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

// 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 3 columns
    .ToListAsync(ct);
Enter fullscreen mode Exit fullscreen mode

Filter and page before materializing, and project to a DTO. That one endpoint went from reading ~4M rows to an index seek returning 12 — p95 2,100 ms → 40 ms. Most "EF is slow" is really "we materialized before we filtered."

Lesson 5 — EF Core is a SQL generator, not magic

Four habits fixed 90% of our EF pain.

Kill N+1 — aggregate in the database, not in a loop:

// BEFORE — 200 campaigns = 201 round trips
foreach (var c in campaigns)
    c.EventCount = await db.Events.CountAsync(e => e.CampaignId == c.Id, ct);

// AFTER — one query, aggregated in SQL
var rows = await db.Campaigns.Where(c => c.TenantId == t)
    .Select(c => new { c.Id, c.Name, EventCount = c.Events.Count() })
    .ToListAsync(ct);
Enter fullscreen mode Exit fullscreen mode

No-tracking + projection for reads, and set-based bulk writes instead of loading rows to change them:

// BEFORE — load 50k rows into memory just to flip a flag
var stale = await db.Sessions.Where(s => s.ExpiresAt < now).ToListAsync(ct);
foreach (var s in stale) s.IsExpired = true;
await db.SaveChangesAsync(ct);

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

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

Lesson 6 — SQL Server is still smarter than your foreach

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.

// BEFORE — pull 180M rows to the app and GroupBy in C#
var events = await db.Events.Where(e => e.CampaignId == id).ToListAsync(ct);
var byDay = events.GroupBy(e => e.OccurredAt.Date)...;
Enter fullscreen mode Exit fullscreen mode 
-- 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 = @idGROUP BY CAST(OccurredAt AS date) ORDER BY Day;

-- the covering index that turned a scan into a seek
CREATE NONCLUSTERED INDEX IX_Events_Campaign_Date
ON Events (CampaignId, OccurredAt) INCLUDE (EventType);
Enter fullscreen mode Exit fullscreen mode

Moving that aggregation into SQL with the covering index took a dashboard query from scanning ~180M rows to an index seek returning 30 — p95 1,800 ms → 55 ms. Compute where the data is; move the answer, not the rows. And always measure — SET STATISTICS IO ON and read the actual plan (seek vs scan, watch for key lookups).

The thread through all three

LINQ decides where code runs — filter before you materialize. EF is a SQL generator — project, don't track, batch, watch the SQL it emits. SQL Server beats your loop — index for real queries and read the plan. The teams that struggle with "the database is slow" almost never have a slow database; they have an app that asks it the wrong way.

This is Part 2 of 4. Full post with every before/after and the diagnostics, plus Parts 1, 3, and 4, on PrepStack.

Top comments (0)