IEnumerable<T> vs IQueryable<T> - the key difference in LINQ
This is one of the most frequently asked questions in .NET interviews - the foundation for understanding how LINQ to Entities (EF Core) works at all.
The basic definition - IQueryable<T> inherits from IEnumerable<T>
public interface IEnumerable<T> : IEnumerable
{
IEnumerator<T> GetEnumerator();
}
public interface IQueryable<T> : IEnumerable<T>, IQueryable
{
Type ElementType { get; }
Expression Expression { get; }
IQueryProvider Provider { get; }
}
IQueryable<T> is an IEnumerable<T> (it inherits from it) plus additional information needed to build a query and translate it into another language (most often SQL).
Where the filtering runs - the key difference
// IEnumerable - filtering IN MEMORY, in C#
IEnumerable<Order> orders = context.Orders.ToList(); // ⚠️ ToList() has already fetched EVERYTHING from the database!
var filtered = orders.Where(o => o.Status == "Pending"); // the filtering happens NOW, in .NET memory
// IQueryable - filtrowanie NA BAZIE DANYCH, w SQL
IQueryable<Order> query = context.Orders; // jeszcze BRAK zapytania SQL
var filtered2 = query.Where(o => o.Status == "Pending"); // STILL no SQL - only extending the expression
var result = filtered2.ToList(); // DOPIERO TERAZ generowany i wykonywany SQL: WHERE Status = 'Pending'
This is the fundamental practical consequence: IEnumerable fetches all the data and then filters in C#; IQueryable translates the filter into SQL and filters directly in the database, sending only the resulting rows over the network.
Demonstrating the performance problem - a classic trick question
// ❌ BARDZO NIEEFEKTYWNE
public List<Order> GetPendingOrders(AppDbContext context)
{
IEnumerable<Order> orders = context.Orders; // niejawna konwersja na IEnumerable!
return orders.Where(o => o.Status == "Pending").ToList();
}
// What actually happens: EF Core sees the IEnumerable type, so it CANNOT build SQL with the filter
// -> fetches ALL the orders from the entire table (SELECT * FROM Orders, with NO WHERE)
// -> the "Status == Pending" filtering happens ONLY in C# memory, after fetching EVERYTHING
// ✅ EFEKTYWNE
public List<Order> GetPendingOrders(AppDbContext context)
{
IQueryable<Order> orders = context.Orders; // pozostaje IQueryable
return orders.Where(o => o.Status == "Pending").ToList();
}
// SQL: SELECT * FROM Orders WHERE Status = 'Pending' - filtr wykonany NA BAZIE,
// ONLY the matching rows travel over the network
This is a real, common bug in production code - if anywhere along the way you change the variable type to IEnumerable<T> (or call a method that takes IEnumerable<T> instead of IQueryable<T>), you lose the ability to translate further operations into SQL - everything from that point on runs in memory, after fetching (potentially) the whole table.
How this happens "under the hood" - Expression vs Func
// IEnumerable.Where przyjmuje SKOMPILOWANY delegat
public static IEnumerable<T> Where<T>(this IEnumerable<T> source, Func<T, bool> predicate)
{
foreach (var item in source)
if (predicate(item)) yield return item; // executes actual C# code on EVERY element
}
// IQueryable.Where takes an EXPRESSION TREE - a representation of STRUCTURE, not code
public static IQueryable<T> Where<T>(this IQueryable<T> source, Expression<Func<T, bool>> predicate)
{
// does NOT execute predicate! Instead it ADDS it to the Expression tree,
// which the Provider (e.g. EF Core) will analyse and translate into SQL
}
o => o.Status == "Pending" as a Func<Order, bool> is ready, compiled code - you can only execute it, you can't "look inside" and see what it does. The same code as an Expression<Func<Order, bool>> is a data structure describing: "a comparison of the object's Status property with the literal "Pending"" - EF Core reads this structure and generates the corresponding fragment of SQL.
A consequence - IQueryable doesn't accept arbitrary C# code
// ❌ Throws an exception at runtime - EF Core can't translate ANY C# method into SQL
var orders = context.Orders.Where(o => IsValidCustomLogic(o)).ToList();
// "The LINQ expression could not be translated"
private bool IsValidCustomLogic(Order o) => /* any complex C# logic */;
// ✅ Works - simple expressions that EF Core CAN translate into SQL
var orders = context.Orders.Where(o => o.TotalAmount > 100 && o.Status == "Pending").ToList();
This is a limitation that doesn't exist with IEnumerable - there any C# code, whatever it is, simply runs, because it is an ordinary, compiled delegate and not a structure to be analysed.
Deferred execution - a shared feature, but a different "moment" of execution
// IEnumerable - defers the EXECUTION OF C# CODE, but the iteration itself still happens in memory
IEnumerable<int> query = numbers.Where(n => n > 5); // NOTHING has run yet
foreach (var n in query) { } // NOW it actually iterates and filters - but STILL in .NET memory
// IQueryable - defers the EXECUTION OF THE QUERY (and its CONSTRUCTION as SQL)
IQueryable<Order> query2 = context.Orders.Where(o => o.Status == "Pending"); // brak SQL
var list = query2.ToList(); // NOW the SQL is generated AND sent to the database
When the "switch" from IQueryable to IEnumerable happens - materialisation
IQueryable<Order> query = context.Orders.Where(o => o.Status == "Pending"); // still IQueryable, no SQL
List<Order> materialized = query.ToList(); // .ToList() EXECUTES the query - the result is a LIST, concrete data in memory
// From THIS POINT any further Where/OrderBy already run in memory (on a List<T>, i.e. IEnumerable)
var furtherFiltered = materialized.Where(o => o.TotalAmount > 500); // this NO LONGER touches the database - a filter in C#
This is the key moment of "materialisation" - ToList(), ToArray(), First(), Count() execute the SQL query and return concrete data in memory - everything you do after that moment is just ordinary IEnumerable operations in C#, not SQL.
A practical rule - build the WHOLE query as IQueryable, materialise at the end
// ✅ Good practice - ALL the conditions added BEFORE you call ToListAsync()
public async Task<List<OrderDto>> SearchOrdersAsync(string status, decimal? minAmount, int page)
{
IQueryable<Order> query = _context.Orders; // zaczynasz jako IQueryable
if (!string.IsNullOrEmpty(status))
query = query.Where(o => o.Status == status); // still IQueryable - adds to the SQL
if (minAmount.HasValue)
query = query.Where(o => o.TotalAmount >= minAmount); // still IQueryable
query = query.Skip((page - 1) * 20).Take(20); // pagination ALSO in SQL (OFFSET/FETCH), not in memory!
return await query.Select(o => new OrderDto(o.Id, o.CustomerName, o.TotalAmount)) // projekcja w SQL
.ToListAsync(); // DOPIERO TERAZ - jedno, kompletne zapytanie SQL z WSZYSTKIMI warunkami
}
This is exactly the pagination + filtering pattern that should always stay an IQueryable right to the end - if you materialise prematurely (ToList() too early), pagination (Skip/Take) will run in memory after fetching all the rows from the database, which for a large table is catastrophic for performance.
Comparison table
IEnumerable<T> |
IQueryable<T> |
|
|---|---|---|
Where the logic runs (Where, Select) |
In memory, in C# (compiled code) | Translated into SQL, executed in the database |
Parameter type in Where/Select |
Func<T, bool> (a delegate) |
Expression<Func<T, bool>> (an expression tree) |
| Data source | In-memory collections (List<T>, arrays) OR already fetched data |
Sources with a LINQ provider (EF Core, LINQ to SQL) |
| Performance with large data sets | Poor - you have to fetch everything into memory first | Good - the filter/pagination run in the database, only the resulting data is sent |
| Arbitrary C# code in a predicate | ✅ Always works | ❌ Only expressions translatable to SQL |
| Relationship | The base interface | Inherits from IEnumerable<T>, adds Expression/Provider |
Summary - a concise interview answer
"
IQueryable<T>inherits fromIEnumerable<T>, but represents a query as an expression tree (Expression), which is only translated by the provider at materialisation (ToList(),ToListAsync()) - in the case of EF Core into SQL - and executed directly on the database.IEnumerable<T>operates on data already held in memory, executing compiled C# code (Func<T,bool>), with no translation at all. The practical consequence: if in a LINQ chain over aDbSet<T>the variable type is cast toIEnumerable<T>at some point, all further operations (Where, pagination) run in memory AFTER fetching the data from the database, instead of being translated to SQL - this is a common, costly performance bug, so good practice is to keep theIQueryable<T>type right to the end of building the query, materialising only at the very end."