C# / .NET Compendium PLEN
.NET / C# / C# — language fundamentals

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 from IEnumerable<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 a DbSet<T> the variable type is cast to IEnumerable<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 the IQueryable<T> type right to the end of building the query, materialising only at the very end."

Buy me a coffee