DEV Community: Deepanshu Kumar

Entity Framework N+1 in a Report Loop: The Pre-Fetch Fix Was Going to Crash Large Tenants

Deepanshu Kumar — Mon, 03 Aug 2026 17:11:03 +0000

A report that had been slow since the day it shipped. Not slow like "takes a few seconds." Slow like fifteen minutes. Support tickets going back years, all saying the same thing: the expenditure summary report just doesn't load. Users would click generate, walk away, come back, and it still wasn't done.

I picked it up expecting a missing index or a bad query plan. What I found was a loop inside a loop, each iteration firing multiple database round-trips, each round-trip returning the entire tenant's data table so the application could filter it down to one row.

What the code was doing

The report listed every location in the tenant. For each location, it broke down work order costs by category: labor, parts, equipment. Simple enough conceptually. The implementation had an outer loop over locations, an inner loop over categories, and inside that inner loop, several data retrieval calls.

Here's a genericized version of the pattern:

foreach (var location in locations)
{
    var workOrders = _workOrderService.GetWorkOrders(locationId: location.Id);

    foreach (var category in workOrders.Select(w => w.Category).Distinct())
    {
        var labors    = _laborService.GetAllLabors(cultureId);     // full table
        var parts     = _partsService.GetAllParts(cultureId);      // full table
        var equipment = _equipmentService.GetAllEquipment(cultureId); // full table

        // filter down to this location + category
        var locationLabors = labors.Where(l => l.WorkOrderId == ... && l.Category == category);
        // ... same for parts, equipment
        // ... calculate totals
    }
}

The data retrieval methods returned IQueryable<T> backed by table-valued functions. Each call invoked the TVF and returned the full tenant table. The filtering happened afterward in memory.

For a small tenant this was fine. Slow, but fine. For a tenant with hundreds of locations and several WO categories each, the math became:

N work order queries (one per location)
N × C labor queries (one per location × category combination)
N × C parts queries
N × C equipment queries

A tenant with 200 locations averaging 4 categories each: roughly 2,600 database calls per report run. A larger tenant with 500 locations: over 6,000. Each call returning the full labor table, the full parts table, the full equipment table, hundreds of thousands of rows per call, discarded immediately after the filter ran.

The 15-minute report times made sense once I saw this. The mystery was why nobody had fixed it yet.

The obvious fix, and why it would have been worse

The first instinct was a global pre-fetch. Run each query once before the loop, hold the results in memory, filter from there:

// Pull everything once
var allLabors    = _laborService.GetAllLabors(cultureId).ToList();
var allParts     = _partsService.GetAllParts(cultureId).ToList();
var allEquipment = _equipmentService.GetAllEquipment(cultureId).ToList();

foreach (var location in locations)
{
    var workOrders = _workOrderService.GetWorkOrders(locationId: location.Id);

    foreach (var category in workOrders.Select(w => w.Category).Distinct())
    {
        var locationLabors = allLabors.Where(l => l.WorkOrderId == ... && l.Category == category);
        // etc.
    }
}

This works. Three database calls instead of thousands. The report that took fifteen minutes would finish in seconds.

I had the branch open, the change written, and was about to push it when I thought about the larger tenants.

The labor and parts tables hold the full history for the tenant: every work order ever completed, every part ever used, going back years. For a small tenant that's manageable. For a large one with tens of thousands of work orders per year, multiple sites, years of history, calling .ToList() with no predicate could pull millions of rows into the application server's memory in a single shot.

The TVF sources took only a culture ID parameter. No way to pass a filter into them. They returned the full tenant table and relied on the caller to narrow things down, so calling .ToList() with no predicate before it meant pulling everything regardless.

For the tenants with the worst report times, the ones that had been waiting years for this fix, a global pre-fetch might replace a 15-minute report with an out-of-memory crash. That's not a fix.

The approach that actually worked

The constraint was: we can't pass a predicate into the TVF, and we can't fetch everything at once either. But we also can't fetch per-loop. That was the original problem.

The answer was to split it into two phases.

Phase 1 — run the location loop normally, collecting work order IDs as you go:

var woIdList = new List<int>();

foreach (var location in locations)
{
    var workOrders = _workOrderService.GetWorkOrders(locationId: location.Id);
    woIdList.AddRange(workOrders.Select(w => w.Id));
}

woIdList = woIdList.Distinct().ToList();

This is still N database calls, one per location, no change there. But now we have a bounded list of exactly the work order IDs that appear in this report, scoped to the date range the user selected. Not the full tenant. Not all history. Just the IDs relevant to this specific report run.

Phase 2 — one pre-fetch per data type, filtered to that ID list:

var allLabors = _laborService.GetAllLabors(cultureId)
    .Where(l => woIdList.Contains(l.WorkOrderId))
    .ToList();

var allParts = _partsService.GetAllParts(cultureId)
    .Where(p => woIdList.Contains(p.WorkOrderId))
    .ToList();

var allEquipment = _equipmentService.GetAllEquipment(cultureId)
    .Where(e => woIdList.Contains(e.WorkOrderId))
    .ToList();

EF6 translates .Contains() on an integer list to a SQL IN clause. The database does the filtering. You get back only the rows for work orders that actually appear in the report.

Then the inner category loop runs against in-memory collections. Zero additional database calls.

Total queries: N (location work order fetches) + 3 (the scoped pre-fetches). For a tenant with 200 locations that's 203 queries instead of 2,600. For a tenant with 500 locations it's 503 instead of 6,000-plus.

The EF6 parameter limit

One more thing to watch: EF6 generates IN clauses as inline values, not parameterized. SQL Server has a 2,100-parameter limit on parameterized queries, but inline IN lists bypass that. The risk is query plan caching. Different ID lists produce different SQL strings, so the plan cache can't reuse them. For this use case that was acceptable. The report runs infrequently enough that plan cache pressure wasn't a concern.

If the ID list ever grew large enough to cause problems, the right move would be passing the IDs as a table-valued parameter and joining inside the query. That gives the plan cache something stable to reuse. For the typical report run the IN clause approach was clean and measurably fast.

What it looked like after

Before the fix: the main data-collection endpoint averaged around two minutes on the test environment, and the test environment was more powerful than production. In production, the same report ran five to fifteen minutes depending on the tenant.

After: four to eight seconds.

The fix had been obvious once the pattern was visible. The loop-inside-a-loop, the TVF sources that returned full tables, the in-memory filtering that discarded almost everything. All of it was right there in the code. The report had shipped like that and nobody had looked closely until the runtime climbed high enough to generate support tickets.

I don't know how many tenants had been quietly tolerating slow report times without filing tickets. The ones that filed them waited an average of eight months from first report to fix. The ones that didn't file tickets are harder to account for.

Until next time,
Deepanshu

Backend engineer writing about production bugs, distributed systems, and engineering patterns learned the hard way.

Portfolio · Medium · LinkedIn · GitHub

SQL Server "Login Does Not Exist" via AG Listener After Windows Auth Migration: The Server Name Is a Lie

Deepanshu Kumar — Thu, 23 Jul 2026 12:40:28 +0000

There's an address in our connection strings that looks like a server name but isn't one: ag1-listener.sd.local. We'd seen it for years. It never mattered. When we switched to SQL Auth, it mattered a lot.

For context: the app had always used Windows Authentication to connect to SQL Server. One flag, no credentials, nothing to manage. When services moved to Linux containers on EKS, that stopped working — Linux has no Kerberos domain context. We needed SQL Auth: explicit username and password. Store credentials in AWS Secrets Manager, fetch at runtime, substitute into the connection string.

Seemed mechanical. It worked locally. It worked across every integration environment. In production-like testing, every connection failed with the same error:

Login failed for user 'svc_app'.

First thing to check — whether the login actually exists on the node you're connected to:

SELECT name FROM sys.server_principals
WHERE name = 'svc_app';

Empty result set. The login wasn't there. But it existed in every other environment. That's when it got interesting.

The connection string that caused it

The app is multi-tenant. Each client has its own database. Connection strings are stored centrally. When a request arrives, the app reads the stored connection string, injects credentials fetched at runtime, and connects.

A stored connection string looks like this:

data source=ag1-listener.sd.local;
initial catalog=ClientDatabase;
user id={{UserId}};
password={{Password}}

The credentials come from Secrets Manager at runtime. The question is: which secret do you fetch?

In lower environments, the stored addresses were direct server names — sql-dev-01, sql-dev-02. One secret per server:

/dev/sql-dev-01_credentials  →  { "userId": "svc_app", "password": "..." }
/dev/sql-dev-02_credentials  →  { "userId": "svc_app", "password": "..." }

When the connection string said sql-dev-01, the provider fetched the matching secret. Clean.

In the production-like environment, the stored connection strings said ag1-listener.sd.local.

And ag1-listener.sd.local is not a server name.

What a listener actually is

An Availability Group listener is a virtual DNS name that always points to whoever is currently the primary SQL Server node in a cluster. Today it routes to sql-server-01. After a failover, it routes to sql-server-02. The app never has to change anything — the listener handles it transparently.

That's the design. The listener exists specifically so you don't know or care which physical server you're on.

SQL Auth breaks this.

With Windows Authentication, your identity was a domain account with rights on every SQL Server node. "Which server?" was irrelevant — the same Kerberos token worked everywhere.

With SQL Auth, credentials are server-level objects. A SQL Login is created on a specific SQL Server instance. If the login exists on sql-server-01 but not sql-server-02, it authenticates on one and fails on the other.

So when we tried to extend the per-server credential approach:

/prod/sql-server-01_credentials  →  works if listener routes here
/prod/sql-server-02_credentials  →  works if listener routes here

We needed to know which server the listener was pointing to before choosing which secret to fetch. But to find that out, you have to connect. To connect, you need credentials. To get credentials, you need to know which server the listener points to.

That's a circle. You can't break into it.

Why lower environments never showed this

Local: If you're running locally, your connection string provider likely short-circuits to Integrated Security=True against localhost. No SQL Auth, no listener, no Secrets Manager. Zero signal.

Integration environments: No Availability Groups. Single standalone SQL Server instances. Direct server names in the connection strings. Per-server credential lookup works exactly as designed. Still zero signal.

Production-like environment: Real Availability Groups. Real listeners in the connection strings. SQL Auth enabled. All three conditions together for the first time. The bug had always been there — it just had nowhere to show up.

The conversation with the DBA

When it broke, we spent time in the application code first. The provider looked correct. The secret naming looked right. It took the DBA explaining AG architecture for the root cause to land.

"The listener is intentionally abstract. After any failover it points to a different physical server. Any credential mapping that depends on knowing the server name will break the moment a failover happens."

So per-server credentials were the wrong model entirely, not just broken in this one case.

"The SQL login needs to exist on all nodes with the same username and password. Then it doesn't matter which node the listener routes to — the credential is always valid."

That reframed the whole problem. The listener answers where do I connect? The credential needs to answer how do I authenticate? — independently of where. Those two concerns have to be separated.

The fix

A SQL Login is a server-level object. It has to be created on each SQL Server instance individually — it doesn't replicate automatically across AG nodes.

Create the login on every node in the cluster:

-- Run on each node in the Availability Group
CREATE LOGIN [svc_app] WITH PASSWORD = 'your-password';

Then inside each database on that node, create the corresponding user and grant access:

USE [ClientDatabase];
CREATE USER [svc_app] FOR LOGIN [svc_app];
EXEC sp_addrolemember 'db_datareader', 'svc_app';
EXEC sp_addrolemember 'db_datawriter', 'svc_app';

Store the credentials once — one secret, fetched at runtime, applied to every connection:

{
  "userId":   "svc_app",
  "password": "..."
}

The connection string template stays the same. At runtime:

data source=ag1-listener.sd.local;
initial catalog=ClientDatabase;
user id=svc_app;
password=...;
MultiSubnetFailover=Yes

The listener routes to whichever node is currently primary. That node has the login. Authentication succeeds. If a failover happens and the listener routes to a different node, same result — the login is there too.

The scenario that still breaks it

One gap remains after this is working.

A new SQL Server cluster gets added to the environment. DB-ops provisions it, adds databases, starts assigning clients to it. But nobody ran CREATE LOGIN on the new nodes.

The login exists on every older node. Not on the new cluster. Clients on the new cluster fail with the exact same error. Everyone else is fine.

From the outside it looks like a data problem. Some clients broken, some not. Seemingly random. The diagnosis is one query on the suspected node:

SELECT name FROM sys.server_principals
WHERE name = 'svc_app';
-- Empty = login not created on this node

Fix: run CREATE LOGIN and CREATE USER on each new node before any clients land there. Simple operation. The risk is forgetting it exists.

What would have caught this earlier

One question during design: "What format are the connection strings stored in production?"

Answer: listener addresses.

That single fact collapses the per-server credential model immediately. You can't map credentials to a virtual DNS name — you need credentials that work on whatever physical server the name resolves to. That conversation should have happened in the design meeting. It happened in production testing instead.

The lower environments used standalone servers with direct names, so everything appeared to work. If production-like connection strings had been used in integration testing — or if the DBA had been in the design review — this would have been an hours-long problem. It wasn't.

Infrastructure assumptions tend to be invisible until the environment changes. Windows Auth worked everywhere because the domain handled it silently. SQL Auth makes every assumption explicit. The assumption that you can map credentials to a server name was always there — it just never mattered until the listener made "server name" mean something ambiguous.

Until next time,
Deepanshu

Backend engineer writing about production bugs, distributed systems, and engineering patterns learned the hard way.

Portfolio · Medium · LinkedIn · GitHub

Why the Same Bug Keeps Coming Back - And How to Stop It

Deepanshu Kumar — Tue, 21 Jul 2026 10:31:41 +0000

A few months ago, I fixed a bug on a Friday afternoon. Status filter on a dashboard widget returning empty results for some users. Found the problem, added one line, tests passed, shipped it. Felt good about it.

The following Tuesday someone filed another ticket. Different widget, same symptom. Assignee filter, empty results, same users affected. I opened the file, found the missing line in about thirty seconds, and just sat there for a moment.

I hadn't fixed the bug. I fixed one place where the bug lived. The same broken pattern was sitting in three other handlers, and I hadn't thought to check.

What actually happened

The app was a multi-tenant SaaS where users have permissions scoped to specific warehouses. Dashboard widgets query orders, but only the ones the user is allowed to see. Simple enough.

We had six of these query handlers. Someone had written the first one correctly, copy-pasted it five times as new widgets were added, and somewhere along the way one important line kept getting dropped.

// Priority widget handler — written correctly
public async Task<List<Priority>> Handle(GetPrioritiesQuery query)
{
    var permittedWarehouses = await _warehouseService.GetPermittedWarehouseIds(query.UserId);
    permittedWarehouses.Add(null); // include orders with no warehouse assigned

    return await _repository.GetPriorities(permittedWarehouses);
}

// Status widget handler — the one that broke
public async Task<List<Status>> Handle(GetStatusesQuery query)
{
    var permittedWarehouses = await _warehouseService.GetPermittedWarehouseIds(query.UserId);
    // that null append never made it here

    return await _repository.GetStatuses(permittedWarehouses);
}

The null append matters because some orders aren't assigned to any warehouse. Without it, those orders get filtered out entirely. For users whose entire order history happened to be unassigned, the widget showed nothing at all.

One line. Easy fix. But I only fixed it in the one handler that had broken visibly.

When I finally audited all six:

GetOrderStatuses     → ❌ missing null append
GetOrderPriorities   → ✅ has null append  
GetOrderAssignees    → ❌ missing null append
GetOrderLocations    → ✅ has null append
GetOrderCategories   → ❌ missing null append
GetOrderCosts        → ✅ has null append

Three broken, three fine. The broken ones just hadn't surfaced yet, either because fewer users hit those widgets or because the affected users had quietly assumed the data wasn't there and moved on without filing a ticket.

Why this keeps happening

The code didn't start inconsistent. Someone wrote the first handler carefully. The inconsistency came from copy-paste and the very reasonable assumption that "this is basically the same as that other handler." Nobody set out to leave three bugs behind.

The trap is how bugs announce themselves. They show up as individual tickets, individual screens, individual user complaints. The natural instinct is to find the broken thing and fix it. That instinct is right, but it stops one step too early.

In my case the pattern was "handlers that filter by warehouse permission don't consistently include unassigned orders." I fixed one handler. I should have searched for the pattern and fixed the category.

The one that never got reported

After I found the other three broken handlers, I thought about GetOrderCategories. It had been broken since the original commit. Same missing line, same bug. No ticket had ever been filed for it.

Two explanations. Either none of our users had an order history composed entirely of unassigned-warehouse orders filtered by category. Or some of them had, looked at the empty widget, assumed the data wasn't there, and closed the tab.

I don't know which. Probably both, at different customers, at different times. The second one bothers me more than the bug itself. A real user looked at a broken screen, formed the wrong mental model of their own data, and moved on without telling anyone.

Before you close the PR

The habit that changed how I work: before writing the fix, write down what's wrong in one sentence of plain English. Not which file, not which method. The rule that got violated.

"GetOrderStatuses doesn't append null to the warehouse list" is a file reference. "Warehouse-permission queries don't consistently include null to catch unassigned orders" is a pattern. The second version tells you what to search for everywhere else.

Then search:

grep -r "GetPermittedWarehouseIds" --include="*.cs" .

Read every result. For each one: should this also have the fix? If yes, it goes in the same PR. Not a follow-up ticket, not later. The same PR, while the context is fresh and the intent is clear.

The audit took me twenty minutes. The follow-up ticket that would have found GetOrderAssignees and GetOrderCategories would have taken two separate Tuesdays. That math is easy.

The bug that got reported was the one someone noticed. The ones that didn't get reported were waiting for the right user, the right data, or someone patient enough to sit with empty results and eventually wonder why.

Until next time,
Deepanshu

Backend engineer writing about production bugs, distributed systems, and engineering patterns learned the hard way.

Portfolio · Medium · LinkedIn · GitHub