Bits of .NET

🔧 Every .Count() Is Another Trip to the Database

A LINQ query built against Entity Framework doesn’t run when you write it — it runs every single time you enumerate it. Call .Count(), then foreach over the same query, then .Any(), and you’ve silently sent three separate round-trips to the database for what looks like one query.

🐞 The Problem

var pendingOrders = db.Orders.Where(o => o.Status == "Pending");

int count = pendingOrders.Count();      // query #1 to the database
if (pendingOrders.Any())                // query #2
{
    foreach (var order in pendingOrders) // query #3
    {
        Process(order);
    }
}

🔍 Why This Happens

An IQueryable<T> is a description of a query, not a result. It
only becomes an actual database round-trip the moment something
enumerates it - Count(), Any(), foreach, ToList(), etc. each
trigger their OWN independent execution against the database,
even though they're all built from the exact same 'pendingOrders'
variable.

✅ The Fix: Materialize Once, Reuse the Result

• Call ToList() (or ToArray()) exactly once, at the point you’re done building the query, then work with the in-memory list from there.
• Every subsequent .Count(), .Any(), or foreach on a List<T> runs in memory – zero extra database round-trips.

📦 Correct Version

var pendingOrders = db.Orders.Where(o => o.Status == "Pending").ToList(); // ONE query

int count = pendingOrders.Count;   // in-memory, free
if (pendingOrders.Any())           // in-memory, free
{
    foreach (var order in pendingOrders) // in-memory, free
    {
        Process(order);
    }
}

⚠️ When You’d Actually Want Deferred Execution

• Building a query conditionally across several lines (adding .Where() clauses based on filters) and materializing only at the very end is a legitimate, common pattern — the trap is materializing implicitly multiple times, not deferred execution itself.
• Watch especially for a query passed into a method and used more than once inside it — that’s the easiest place for this to hide.