IEnumerable vs IQueryable: Who's Lazy and Who's Dangerous?

Ever wondered why your LINQ query runs fine in-memory but blows up against the database? Or why adding a simple .ToList() can sometimes save your bacon? If you work with Entity Framework or LINQ-to-Objects, understanding the difference between IEnumerable and IQueryable isn’t just academic—it can prevent subtle bugs and performance nightmares.

At first glance, both interfaces seem to support LINQ, iteration, and deferred execution. But here’s the catch: IEnumerable is lazy in the sense that it processes data in-memory, one item at a time, only when requested. IQueryable, on the other hand, is potentially dangerous because it builds an expression tree that can be translated into SQL—and not always in the way you expect.

Consider this code snippet:

var query = dbContext.Users
    .Where(u => u.IsActive)
    .AsEnumerable()
    .Where(u => IsPremiumUser(u));

Here, the first Where gets translated to SQL, efficiently filtering on the server. But the second Where is a C# method call that runs in-memory. The moment you call .AsEnumerable(), you shift from IQueryable to IEnumerable, pulling all matching rows into memory. That might be fine for 100 users, but catastrophic for 10,000.

Contrast that with:

var query = dbContext.Users
    .Where(u => u.IsActive && u.Tier == "Premium");

This remains fully in IQueryable, and Entity Framework will translate the whole thing into a tight SQL query.

The golden rule? Stay in IQueryable as long as possible when querying databases. Use .ToList(), .AsEnumerable(), or similar only when you’re ready to work in-memory. Otherwise, you risk unintentional N+1 queries or worse—loading your entire database into RAM.

Respect the lazy. Beware the dangerous.

Have your own LINQ horror story or optimization trick? Share it in the comments below!