All 18 LINQ questions
More in LINQ
What is 'deferred execution' in LINQ, and how does it differ from calling .ToList() or .ToArray() immediately? How do GroupBy() and its resulting IGrouping<TKey, TElement> work? What is the difference between First(), FirstOrDefault(), Single(), and SingleOrDefault()? What is LINQ, and how does it simplify querying collections compared to writing manual loops? How does GroupBy translate and what performance pitfalls exist? Why can LINQ cause the N+1 query problem and how do you fix it? How does LINQ to Objects differ fundamentally from LINQ to Entities (Entity Framework), particularly regarding how the query is actually executed? How would you write a custom LINQ extension method that integrates seamlessly with the standard method-chaining style, like a 'DistinctBy' before it existed natively? How does LINQ's Aggregate() method work, and how would you use it to implement a custom reduction not covered by Sum()/Average()/Count()? How would you write an efficient LINQ query combining Join() with GroupBy() to replicate a SQL-style LEFT OUTER JOIN with aggregation?
Advertisement
C# › LINQ

What is the difference between IEnumerable<T>, IQueryable<T>, and List<T>, and how does choosing the wrong one hurt performance against a database?

Intermediate

Answer

IEnumerable<T> represents a sequence you iterate in memory -- when used against Entity Framework, any filtering applied to it happens AFTER the full dataset has already been pulled into memory, which is wasteful for large tables. IQueryable<T> instead builds an expression tree that Entity Framework translates into SQL, so filtering, sorting, and aggregation happen at the database level, only returning the rows that actually match. List<T> is a fully materialized, in-memory collection supporting fast indexed access and mutation, but by definition has already loaded everything it contains -- the practical rule is to keep queries as IQueryable<T> for as long as possible, only materializing to a List<T> once you're ready to actually use the final, filtered results.
// Inefficient: pulls ALL rows into memory, THEN filters
IEnumerable<User> users = dbContext.Users;
var adults = users.Where(u => u.Age >= 18); // filtering happens in memory, after loading everything

// Efficient: filter is translated to SQL and applied by the database
IQueryable<User> query = dbContext.Users.Where(u => u.Age >= 18);
var adults = query.ToList(); // only matching rows are ever retrieved
Real-world example A slow admin dashboard querying a million-row orders table via IEnumerable<T> (accidentally loading every row before filtering) is fixed by switching the query to IQueryable<T>, letting the database itself apply the WHERE clause and return only the handful of relevant rows.

Common follow-ups: How would you detect in code review that an IEnumerable-typed query is about to cause a full table scan?;What's the risk of calling .ToList() too early in a LINQ chain against Entity Framework?

SQL Queries;Diagnostics & Performance
All 18 LINQ questions