Bits of .NET

🤖 The Prompt That Counts Queries the Way Your Database Would

An N+1 query bug happens when code loads a list of records with one query, then loops over that list making a separate query per item to fetch related data – a pattern that runs fine with five test rows and falls over with five thousand production ones. It’s genuinely hard to spot by reading code alone, because each individual line looks completely reasonable in isolation; the problem only exists in the relationship between a loop and a lazy-loading call inside it, which is exactly the kind of cross-line pattern an AI model with the right prompt can scan for quickly across a whole file.

🔎 The Problem

You are reviewing code that uses an ORM (Entity Framework, Hibernate,
Django ORM, ActiveRecord, or similar - identify which one from context)
for N+1 query patterns. For each file I paste, look specifically for:

1. A loop (for, foreach, .map(), list comprehension) that accesses a
   navigation property, related object, or lazy-loaded collection
   INSIDE the loop body, where that access triggers a separate
   database round trip per iteration.
2. Any place a "fix" for this - like .Include()/.ThenInclude() in EF,
   select_related()/prefetch_related() in Django, or eager loading in
   ActiveRecord - is clearly missing given how the result is later used.

For each finding, show: the loop in question, which line triggers the
extra queries, roughly how many total queries it produces for N items,
and the specific eager-loading call that fixes it for this ORM. Code:

[paste your code here]

✅ Why This Prompt Works

• Asking the model to identify the ORM first keeps the suggested fix concrete and correct – the eager-loading syntax for Entity Framework, Django, and ActiveRecord are all different, and a generic answer isn’t actionable.
• Requesting a rough query count for N items reframes the finding in terms that matter to a reviewer – ‘1 query becomes 501 queries for 500 rows’ is far more persuasive than an abstract description of the pattern.
• Focusing specifically on loop-plus-lazy-access keeps the review targeted, since this is a narrow, well-defined pattern an AI model can scan for reliably, unlike vaguer ‘find performance problems’ requests.

⚠️ Getting the Most Out of It

• Include the model/entity class definitions alongside the query code when you can, since knowing which properties are lazy-loaded navigation properties versus already-loaded scalar fields is what separates a real finding from a false positive.
• Double check any flagged loop against your actual data volumes – a loop over a collection that’s always small in practice is a much lower priority fix than one iterating over a table that grows without bound.