Skip to main content

Anti-Pattern: N+1 Queries

🔴 The Problem​

The N+1 Query Problem is the most common performance killer in ORM-based systems (Entity Framework). It happens when the application executes 1 query to retrieve a list of objects, and then executes an N additional queries for each retrieved object to load related data within a loop.

Why it's Bad​

  • Database Spam: The database is "carpet-bombed" by hundreds of tiny requests.
  • Latency: If the database latency is 2ms, fetching 500 items takes 1 second just in network roundtrips, ignoring query time.

🚱 Real WEP Example​

Identified in HomeController.SolveFeeTitle:

// Query 1: Retrieve 5 Languages
var languages = db.CultureForLanguages.ToList(); 

foreach (var lang in languages) 
{
    // Query N: Executed 5 times! 
    // If "languages" were 100, we would do 101 total queries.
    var fees = db.OrderFeeLines.Where(f => f.Lang == lang.Id).ToList(); 
    
    // ... logic ...
}

💡 The Solution​

Retrieve all necessary data in a single or few optimized queries before the loop.

1. Eager Loading (Include)​

If the relationship is defined in the ORM:

var feesByLanguage = db.OrderFeeLines
    .Include(f => f.CultureForLanguage) // LOAD TOGETHER
    .ToList();

[!CAUTION] Cartesian Explosion: Be careful when using multiple .Include() on collection properties. This can cause the SQL result set to grow exponentially (Rows = N * M * P). In such cases, prefer Performance Stabilization Pillar 2: Prefetching.

2. Explicit Join (Projections)​

If you only need specific fields:

var data = (from l in db.CultureForLanguages
            join f in db.OrderFeeLines on l.Id equals f.Lang
            select new { Lang = l.Name, Fee = f.Amount })
           .ToList();

3. Load & Group (Memory)​

If complex logic requires loading separate lists:

// Single Query: Retrieve everything at once
var allFees = db.OrderFeeLines.Where(...).ToList();

// Group in Memory (0 DB calls)
var feesByLang = allFees.GroupBy(f => f.Lang);

foreach (var lang in languages) 
{
     var fees = feesByLang[lang.Id]; // Fast dictionary lookup
}

⚠️ 2026 Critical Findings​

  • Case Study: OMGT Data Export - Row-based Template Resolution
    • Issue: Exporting 1000 orders triggers thousands of lazy-loading hits for Customer, OrderHeader, and Payments inside the generator loop.
    • Remediation: Eager loading via dynamic .Include() based on template parsing.