Backend

Collections Study Portal

Lists, dictionaries, LINQ, enumerables
Flash Cards Practice
Backend / Collections / IQueryable

IQueryable

3 min read
Mid-level2 min read
Rapid overview

IQueryable

TL;DR

IQueryable<T> looks like IEnumerable<T> but builds an expression tree instead of executing immediately, letting providers like EF Core translate the whole composed pipeline into a single SQL statement. Returning it from repositories pushes filtering, sorting, and projection to the database — the difference between SELECT TOP 100 ... WHERE Symbol = 'AAPL' and dragging millions of rows into memory before filtering them with LINQ-to-Objects.

How it works

IQueryable — Expression Trees for Remote Queries

"Use IQueryable when queries should be translated and executed remotely (databases, APIs), not in memory."

❌ Bad example:

public IEnumerable<Trade> GetTradesForSymbol(string symbol)
{
    return _dbContext.Trades.ToList() // loads ALL trades into memory
                            .Where(t => t.Symbol == symbol); // filters in memory
}

// Caller
var trades = repository.GetTradesForSymbol("AAPL"); // loads millions of rows

Using IEnumerable forces full materialization before filtering—catastrophic for large datasets.

âś… Good example:

public IQueryable<Trade> GetTradesForSymbol(string symbol)
{
    return _dbContext.Trades.Where(t => t.Symbol == symbol); // builds expression tree
}

// Caller
var trades = repository.GetTradesForSymbol("AAPL")
                       .OrderByDescending(t => t.Timestamp)
                       .Take(100); // SQL: SELECT TOP 100 ... WHERE Symbol = 'AAPL' ORDER BY ...

👉 IQueryable builds expression trees; EF Core translates to SQL and executes on database.

🔥 Composing queries before execution:

public IQueryable<Order> GetOrders()
{
    return _dbContext.Orders;
}

// Caller composes query further
var recentLargeOrders = orderService.GetOrders()
    .Where(o => o.Amount > 10000)
    .Where(o => o.CreatedAt > DateTime.UtcNow.AddDays(-7))
    .OrderByDescending(o => o.Amount); // still no execution

var results = recentLargeOrders.ToList(); // NOW executes as single SQL query

👉 Query composition is deferred until materialization (.ToList(), .First(), foreach).

🔥 Avoiding N+1 queries:

// ❌ Bad: N+1 problem
public IEnumerable<Order> GetOrdersWithCustomers()
{
    var orders = _dbContext.Orders.ToList(); // 1 query
    foreach (var order in orders)
    {
        var customer = order.Customer; // N queries (lazy loading)
    }
    return orders;
}

// âś… Good: single query with join
public IQueryable<Order> GetOrdersWithCustomers()
{
    return _dbContext.Orders.Include(o => o.Customer); // SQL JOIN
}

👉 IQueryable enables Include() for eager loading, avoiding N+1 queries.

đź’ˇ In trading systems:

  • Use IQueryable for database repositories to push filtering/sorting to SQL.
  • Enable dynamic query composition for flexible reporting without loading everything.
  • Avoid materializing with .ToList() prematurely—keep query deferred until final shape is known.

Quick recall Q&A

Q: What's the difference between IEnumerable and IQueryable? A: IEnumerable executes in-memory (LINQ-to-Objects), IQueryable builds expression trees for remote execution (LINQ-to-SQL, LINQ-to-Entities). Use IQueryable for databases.
Q: When should I return IQueryable vs IEnumerable? A: Return IQueryable from repository methods so callers can compose queries before execution. Return IEnumerable when data is already in memory or query composition isn't needed.
Q: Can IQueryable be enumerated multiple times? A: Yes, but each enumeration re-executes the query against the database. Cache results with .ToList() if multiple enumerations are needed.
Q: What happens if I use unsupported operations in IQueryable? A: Providers throw runtime exceptions if they can't translate operations to SQL (e.g., calling custom C# methods). Use .AsEnumerable() to switch to in-memory for unsupported logic.
Q: How do I switch from IQueryable to IEnumerable? A: Call .AsEnumerable(). This forces remaining operations to execute in-memory via LINQ-to-Objects. Useful for operations EF can't translate.
Q: What's the performance cost of IQueryable? A: Building expression trees has overhead, but it's negligible compared to database I/O. The benefit is pushing work to the database, reducing memory and network costs.
Q: Can IQueryable be used outside EF Core? A: Yes. Any provider implementing IQueryProvider can expose IQueryable (e.g., LINQ-to-MongoDB, OData). Custom providers require implementing expression visitors.
Q: How does IQueryable relate to SQL injection? A: Parameterized queries are generated by providers like EF Core, preventing SQL injection. Never concatenate strings into IQueryable predicates—use variables instead.
Q: Should I expose IQueryable from repositories? A: Controversial. Pros: flexible querying. Cons: leaks data access concerns to callers. Consider returning IQueryable for read-only queries, but encapsulate writes.
Q: What's deferred execution in IQueryable? A: Query construction doesn't execute; only enumeration (.ToList(), .First(), foreach) triggers execution. This enables composing filters/projections cheaply before hitting the database.

See also