IEnumerable vs IQueryable in C#

Overview

IEnumerable<T> and IQueryable<T> are two important LINQ-related interfaces in C#. They can look similar because both allow a sequence of data to be queried with LINQ, but they are designed for different execution models.

IEnumerable<T> represents a sequence that can be enumerated in .NET. It is most commonly used for in-memory collections such as arrays, lists, sets, dictionaries, and custom iterator methods. LINQ methods used with IEnumerable<T> are provided by the Enumerable class and operate using normal .NET delegates such as Func<T, bool>.

IQueryable<T> represents a query that can be translated and executed by a query provider. It is most commonly used with remote or external data sources such as Entity Framework Core, LINQ providers, databases, OData providers, and other systems that can translate an expression tree into a native query. LINQ methods used with IQueryable<T> are provided by the Queryable class and usually build expression trees such as Expression<Func<T, bool>>.

The practical difference is not just syntax. It affects where the filtering happens, when the query executes, how much data is loaded, whether the database can optimize the query, whether a custom C# method can be used, how exceptions appear, and whether code is safe across application layers.

For interviews, this topic is important because it tests whether a developer understands LINQ beyond basic syntax. A strong C# developer should know when a query runs in memory, when it can be translated to SQL, when ToList() changes behavior, why returning IQueryable<T> from repositories can be risky, and how to avoid common performance bugs such as client-side filtering and repeated query execution.

Core Concepts

Basic Definitions

IEnumerable<T> is an interface for reading a sequence of values one item at a time.

public interface IEnumerable<out T> : IEnumerable
{
    IEnumerator<T> GetEnumerator();
}

It supports enumeration through foreach:

IEnumerable<int> numbers = new List<int> { 1, 2, 3, 4, 5 };

foreach (int number in numbers)
{
    Console.WriteLine(number);
}

IQueryable<T> is an interface for building a query that can be interpreted by a provider.

public interface IQueryable<out T> : IEnumerable<T>, IQueryable
{
}

Even though IQueryable<T> inherits from IEnumerable<T>, it adds query-specific information through the non-generic IQueryable interface:

public interface IQueryable : IEnumerable
{
    Type ElementType { get; }
    Expression Expression { get; }
    IQueryProvider Provider { get; }
}

The important properties are:

In simple terms:

IEnumerable<T> = enumerate .NET objects
IQueryable<T> = describe a query for a provider to execute

The Main Difference

The most important difference is where the query logic runs.

IEnumerable<T> Execution Model

IEnumerable<T> works by asking a sequence for an enumerator, then repeatedly calling MoveNext() until the sequence ends.

IEnumerable<string> names = new List<string>
{
    "Alice",
    "Bob",
    "Charlie"
};

foreach (string name in names)
{
    Console.WriteLine(name);
}

LINQ over IEnumerable<T> usually runs in the application process:

List<User> users = GetUsersFromMemory();

IEnumerable<User> activeUsers = users
    .Where(user => user.IsActive)
    .OrderBy(user => user.LastName);

The Where and OrderBy methods here are Enumerable.Where and Enumerable.OrderBy. They operate on .NET objects. The predicate is compiled code:

Func<User, bool> predicate = user => user.IsActive;

The filtering happens in memory when the sequence is enumerated.

IQueryable<T> Execution Model

IQueryable<T> does not normally execute the query logic directly in .NET. Instead, it builds an expression tree that describes the query.

Example with Entity Framework Core:

IQueryable<User> query = dbContext.Users
    .Where(user => user.IsActive)
    .OrderBy(user => user.LastName)
    .Select(user => new UserSummaryDto
    {
        Id = user.Id,
        FullName = user.FirstName + " " + user.LastName,
        Email = user.Email
    });

This query is not executed immediately. It is a description of work to perform. With EF Core, the provider can translate it into SQL when the query is executed.

For example, the provider may produce SQL conceptually similar to:

SELECT Id, FirstName, LastName, Email
FROM Users
WHERE IsActive = 1
ORDER BY LastName;

The key point is that the filtering, ordering, and projection can happen in the database instead of loading all users into memory first.

Enumerable vs Queryable Extension Methods

C# LINQ uses extension methods. The selected method depends on the compile-time type of the source.

For IEnumerable<T>:

IEnumerable<User> users = GetUsers();

var query = users.Where(user => user.IsActive);

This uses Enumerable.Where:

Enumerable.Where(users, user => user.IsActive);

The predicate is a Func<User, bool>.

For IQueryable<T>:

IQueryable<User> users = dbContext.Users;

var query = users.Where(user => user.IsActive);

This uses Queryable.Where:

Queryable.Where(users, user => user.IsActive);

The predicate is an Expression<Func<User, bool>>.

That difference is extremely important:

Func<T, bool> executes code.
Expression<Func<T, bool>> describes code.

A delegate can be invoked directly by .NET. An expression tree can be inspected, translated, optimized, or rejected by a provider.

Expression Trees

An expression tree represents code as data.

This lambda:

user => user.Age >= 18

Can become an expression tree that describes:

Parameter: user
Member access: user.Age
Constant: 18
Operator: >=

A query provider such as EF Core can inspect that expression tree and translate it into SQL:

WHERE Age >= 18

This is why IQueryable<T> can be powerful. The provider receives a structured representation of the query instead of an already compiled .NET function.

However, this also creates limitations. The provider can only translate expressions it understands. A normal C# method may not be translatable.

public static bool IsImportantCustomer(Customer customer)
{
    return customer.TotalSpend > 10_000 && customer.IsActive;
}

var query = dbContext.Customers
    .Where(customer => IsImportantCustomer(customer));

This may fail because the provider does not know how to translate IsImportantCustomer into SQL.

A provider-friendly version is:

var query = dbContext.Customers
    .Where(customer => customer.TotalSpend > 10_000 && customer.IsActive);

Deferred Execution

Both IEnumerable<T> and IQueryable<T> commonly use deferred execution.

Deferred execution means the query is not executed when it is defined. It is executed when the result is enumerated or when a terminal operation is called.

var query = dbContext.Users
    .Where(user => user.IsActive);

// Query has not executed yet.

List<User> users = query.ToList();

// Query executes here.

Common execution triggers include:

• foreach
• ToList()
• ToArray()
• Count()
• Any()
• First()
• Single()
• Max()
• Min()
• Sum()
• Average()

With IEnumerable<T>, execution usually means iterating in memory.

With IQueryable<T>, execution usually means the provider executes the query, often by sending SQL to the database.

Example: Filtering Before and After Materialization

This is one of the most common interview examples.

Good version:

var users = await dbContext.Users
    .Where(user => user.IsActive)
    .OrderBy(user => user.LastName)
    .Take(50)
    .ToListAsync();

This keeps the query as IQueryable<T> until the end. EF Core can translate the filtering, ordering, and paging into SQL. The database returns only the required records.

Bad version:

var allUsers = await dbContext.Users.ToListAsync();

var users = allUsers
    .Where(user => user.IsActive)
    .OrderBy(user => user.LastName)
    .Take(50)
    .ToList();

This loads all users from the database first, then filters in memory. That can cause major performance and memory problems.

The practical rule is:

For database queries, apply Where, OrderBy, Select, Skip, and Take before ToList().

Example: IEnumerable<T> Against an EF Core DbSet

Because IQueryable<T> inherits from IEnumerable<T>, it is possible to accidentally force LINQ to use Enumerable instead of Queryable.

IEnumerable<User> users = dbContext.Users;

var activeUsers = users.Where(user => user.IsActive);

This looks harmless, but the compile-time type is now IEnumerable<User>, so the Where method is Enumerable.Where, not Queryable.Where.

Depending on the exact code path, this can lead to a query being executed and filtering happening in memory instead of being translated as part of the database query.

Prefer this when composing EF Core queries:

IQueryable<User> users = dbContext.Users;

var activeUsers = users.Where(user => user.IsActive);

Or simply keep the chain directly on DbSet<T>:

var activeUsers = dbContext.Users
    .Where(user => user.IsActive)
    .ToList();

AsEnumerable()

AsEnumerable() changes how subsequent LINQ operators are resolved.

var query = dbContext.Users
    .Where(user => user.IsActive)
    .AsEnumerable()
    .Where(user => IsValidInApplicationCode(user));

The first Where is still provider-based and can be translated to SQL. After AsEnumerable(), later operators use Enumerable and run in memory.

Important details:

• AsEnumerable() does not normally execute the query immediately by itself.
• It changes the compile-time type from IQueryable<T> to IEnumerable<T>.
• Subsequent LINQ operations happen in application memory when the sequence is enumerated.
• It is useful when you intentionally want to switch from provider translation to in-memory logic.
• It is dangerous when used accidentally before filtering, sorting, or paging.

Example where AsEnumerable() is acceptable:

var results = dbContext.Orders
    .Where(order => order.Status == OrderStatus.Completed)
    .Select(order => new
    {
        order.Id,
        order.Total,
        order.CreatedAt
    })
    .AsEnumerable()
    .Select(order => new OrderReportRow
    {
        Id = order.Id,
        FormattedTotal = FormatCurrency(order.Total),
        CreatedDate = order.CreatedAt.ToString("yyyy-MM-dd")
    })
    .ToList();

In this example, database-friendly filtering and projection happen first. Formatting happens in memory afterward.

AsQueryable()

AsQueryable() converts a sequence to IQueryable<T> from the perspective of the type system.

List<User> users = GetUsersFromMemory();

IQueryable<User> query = users.AsQueryable();

This does not magically turn an in-memory list into a database query. If the source is already in memory, query execution still happens in memory.

Common mistake:

var users = GetUsersFromApi().AsQueryable();

This only wraps the in-memory sequence. It does not make the API queryable, does not push filtering to the API server, and does not create SQL.

AsQueryable() is useful in some generic APIs, dynamic query builders, or tests, but it should not be treated as a performance optimization.

ToList(), ToArray(), and Materialization

Materialization means executing a query and storing the results in a concrete collection.

List<User> users = await dbContext.Users
    .Where(user => user.IsActive)
    .ToListAsync();

After materialization, further LINQ operations run in memory:

var names = users
    .Where(user => user.LastName.StartsWith("S"))
    .Select(user => user.FullName)
    .ToList();

Materialization is not bad. It is necessary when:

• You need a stable snapshot of results.
• You want to avoid multiple database calls.
• You need to close the database context before further processing.
• You need to use application-only logic that cannot be translated.
• You need to pass data to another layer as a concrete collection.

Materialization becomes a problem when it happens too early:

var users = await dbContext.Users
    .ToListAsync(); // Too early if the table is large.

return users
    .Where(user => user.IsActive)
    .Take(20)
    .ToList();

Better:

return await dbContext.Users
    .Where(user => user.IsActive)
    .Take(20)
    .ToListAsync();

Server-Side vs Client-Side Evaluation

With IQueryable<T>, the provider decides what can be executed by the external source.

For EF Core, the ideal case is server-side execution:

var users = await dbContext.Users
    .Where(user => user.Email.EndsWith("@example.com"))
    .ToListAsync();

The database can handle this filtering.

Client-side evaluation means data is loaded into the application and then processed in memory:

var users = dbContext.Users
    .AsEnumerable()
    .Where(user => CustomEmailCheck(user.Email))
    .ToList();

Client-side evaluation can be acceptable when the dataset is already small, but it is dangerous for large tables.

Common interview warning:

Do not accidentally move filtering, sorting, paging, or joining from the database to the application.

IQueryable<T> and Entity Framework Core

DbSet<T> in EF Core implements IQueryable<T>. That allows LINQ queries to be translated into SQL.

public async Task<List<ProductDto>> GetProductsAsync(decimal minimumPrice)
{
    return await dbContext.Products
        .Where(product => product.Price >= minimumPrice)
        .OrderBy(product => product.Name)
        .Select(product => new ProductDto
        {
            Id = product.Id,
            Name = product.Name,
            Price = product.Price
        })
        .ToListAsync();
}

Good habits with EF Core queries:

• Keep the query as IQueryable<T> while composing database operations.
• Use Select to return only the columns needed.
• Apply Where before materialization.
• Apply Skip and Take before materialization.
• Use ToListAsync, FirstOrDefaultAsync, AnyAsync, and CountAsync for async database execution.
• Avoid calling custom C# methods inside provider-translated filters.
• Avoid returning tracked entity queries directly to UI layers.

Projection Matters

A common performance mistake is loading full entities when only a few fields are needed.

Less efficient:

var users = await dbContext.Users
    .Where(user => user.IsActive)
    .ToListAsync();

var result = users.Select(user => new UserListItemDto
{
    Id = user.Id,
    Name = user.FirstName + " " + user.LastName
}).ToList();

More efficient:

var result = await dbContext.Users
    .Where(user => user.IsActive)
    .Select(user => new UserListItemDto
    {
        Id = user.Id,
        Name = user.FirstName + " " + user.LastName
    })
    .ToListAsync();

The second version allows the database provider to select only the needed columns.

Paging Must Happen Before Materialization

Bad version:

var allProducts = await dbContext.Products.ToListAsync();

var page = allProducts
    .OrderBy(product => product.Name)
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize)
    .ToList();

This loads all products first.

Good version:

var page = await dbContext.Products
    .OrderBy(product => product.Name)
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize)
    .ToListAsync();

This lets the database return only the requested page.

Multiple Enumeration

IEnumerable<T> can represent a lazy sequence. Every enumeration may repeat the work.

IEnumerable<User> users = GetUsersFromDatabaseLikeSource();

int count = users.Count();

foreach (var user in users)
{
    Console.WriteLine(user.Name);
}

This may enumerate the sequence twice. If the sequence triggers database access, file reads, API calls, expensive calculations, or logging, the work may happen twice.

Safer version:

List<User> users = GetUsersFromDatabaseLikeSource().ToList();

int count = users.Count;

foreach (var user in users)
{
    Console.WriteLine(user.Name);
}

For IQueryable<T>, multiple enumeration may send multiple database queries:

IQueryable<Order> query = dbContext.Orders
    .Where(order => order.Status == OrderStatus.Pending);

int count = await query.CountAsync();

List<Order> orders = await query.ToListAsync();

This sends two database queries. Sometimes that is intentional. Sometimes it is wasteful.

DbContext Lifetime and IQueryable<T>

IQueryable<T> depends on its provider. With EF Core, that provider depends on a live DbContext.

Problematic example:

public IQueryable<User> GetActiveUsers()
{
    using var dbContext = new AppDbContext();

    return dbContext.Users.Where(user => user.IsActive);
}

The query is returned without being executed. But the DbContext is disposed before the caller enumerates the query. This can fail at runtime.

Better:

public async Task<List<UserDto>> GetActiveUsersAsync()
{
    await using var dbContext = new AppDbContext();

    return await dbContext.Users
        .Where(user => user.IsActive)
        .Select(user => new UserDto
        {
            Id = user.Id,
            Name = user.FirstName + " " + user.LastName
        })
        .ToListAsync();
}

The query is executed while the context is still alive.

Returning IQueryable<T> from Repositories

Returning IQueryable<T> from a repository is controversial.

Example:

public IQueryable<User> GetUsers()
{
    return dbContext.Users;
}

This gives callers maximum flexibility:

var users = await repository.GetUsers()
    .Where(user => user.IsActive)
    .OrderBy(user => user.LastName)
    .ToListAsync();

But it also leaks data access details outside the repository. Callers can now decide query shape, tracking behavior, includes, filtering, paging, and execution timing.

Risks include:

• Repository abstraction becomes thin and less meaningful.
• Data access logic spreads across the application.
• Callers may build inefficient queries.
• Queries may execute outside the intended DbContext lifetime.
• Security filters may be bypassed if not enforced carefully.
• Unit tests using in-memory AsQueryable() may not catch provider translation problems.

A more controlled approach is to expose specific query methods:

public async Task<List<UserListItemDto>> GetActiveUsersAsync(int pageNumber, int pageSize)
{
    return await dbContext.Users
        .Where(user => user.IsActive)
        .OrderBy(user => user.LastName)
        .Skip((pageNumber - 1) * pageSize)
        .Take(pageSize)
        .Select(user => new UserListItemDto
        {
            Id = user.Id,
            Name = user.FirstName + " " + user.LastName,
            Email = user.Email
        })
        .ToListAsync();
}

Another approach is the Specification pattern, where query rules are represented explicitly and applied inside the data access layer.

Query Composition

IQueryable<T> is useful for composing optional filters before execution.

IQueryable<Product> query = dbContext.Products;

if (!string.IsNullOrWhiteSpace(searchTerm))
{
    query = query.Where(product => product.Name.Contains(searchTerm));
}

if (categoryId is not null)
{
    query = query.Where(product => product.CategoryId == categoryId);
}

if (minimumPrice is not null)
{
    query = query.Where(product => product.Price >= minimumPrice);
}

var products = await query
    .OrderBy(product => product.Name)
    .Select(product => new ProductDto
    {
        Id = product.Id,
        Name = product.Name,
        Price = product.Price
    })
    .ToListAsync();

This is a strong use case for IQueryable<T> because the query remains composable and still executes once.

Dynamic Filtering

IQueryable<T> is often used in APIs that support dynamic filtering, sorting, and paging.

public async Task<List<CustomerDto>> SearchCustomersAsync(CustomerSearchRequest request)
{
    IQueryable<Customer> query = dbContext.Customers;

    if (!string.IsNullOrWhiteSpace(request.Keyword))
    {
        query = query.Where(customer =>
            customer.Name.Contains(request.Keyword) ||
            customer.Email.Contains(request.Keyword));
    }

    query = request.SortBy switch
    {
        "email" => query.OrderBy(customer => customer.Email),
        "createdAt" => query.OrderByDescending(customer => customer.CreatedAt),
        _ => query.OrderBy(customer => customer.Name)
    };

    return await query
        .Skip((request.Page - 1) * request.PageSize)
        .Take(request.PageSize)
        .Select(customer => new CustomerDto
        {
            Id = customer.Id,
            Name = customer.Name,
            Email = customer.Email
        })
        .ToListAsync();
}

This keeps dynamic query construction server-side.

IQueryable<T> Translation Limitations

Not every C# expression can be translated by every provider.

Usually safe:

var query = dbContext.Users
    .Where(user => user.Age >= 18 && user.IsActive)
    .Select(user => new
    {
        user.Id,
        user.Email
    });

Potentially unsafe:

var query = dbContext.Users
    .Where(user => NormalizeEmail(user.Email) == normalizedInput);

The provider may not know how to translate NormalizeEmail.

A safer approach is to normalize the input before the query and use provider-translatable operations:

string normalizedInput = input.Trim().ToUpperInvariant();

var query = dbContext.Users
    .Where(user => user.NormalizedEmail == normalizedInput);

Common operations that may cause issues depending on provider and version:

• Custom methods inside Where
• Complex object construction before filtering
• Local collection operations that cannot be translated
• Unsupported string, date, or math methods
• DateTime operations that the database provider does not support
• Comparing complex objects instead of scalar fields

IEnumerable<T> Is Not Always Materialized

A common misconception is that IEnumerable<T> always means a collection already exists in memory.

That is not always true.

public IEnumerable<int> GenerateNumbers()
{
    for (int i = 1; i <= 5; i++)
    {
        Console.WriteLine($"Generating {i}");
        yield return i;
    }
}

This method returns an IEnumerable<int>, but the numbers are generated lazily.

IEnumerable<int> numbers = GenerateNumbers();

foreach (int number in numbers)
{
    Console.WriteLine(number);
}

The output is produced as the sequence is enumerated.

This matters because IEnumerable<T> tells you that something can be enumerated. It does not guarantee that it is already stored in memory.

IQueryable<T> Is Not Always a Database Query

Another misconception is that IQueryable<T> always means SQL or a database.

That is also not true.

List<int> numbers = new() { 1, 2, 3, 4, 5 };

IQueryable<int> query = numbers.AsQueryable();

This query is still backed by an in-memory list. It uses an in-memory query provider.

The correct understanding is:

IQueryable<T> means provider-based query composition, not necessarily database execution.

API Design: What Should Methods Accept?

For method parameters, choose the least powerful abstraction that satisfies the method.

Use IEnumerable<T> when the method only needs to iterate:

public decimal CalculateTotal(IEnumerable<OrderLine> lines)
{
    return lines.Sum(line => line.Quantity * line.UnitPrice);
}

Use IReadOnlyCollection<T> when the method needs enumeration and count:

public void ValidateItems(IReadOnlyCollection<OrderLine> lines)
{
    if (lines.Count == 0)
    {
        throw new InvalidOperationException("At least one line is required.");
    }
}

Use IReadOnlyList<T> when the method needs indexing:

public OrderLine GetFirstLine(IReadOnlyList<OrderLine> lines)
{
    return lines[0];
}

Use IQueryable<T> only when the method is intentionally composing a provider query:

public static IQueryable<User> ApplyActiveFilter(IQueryable<User> query)
{
    return query.Where(user => user.IsActive);
}

Do not accept IQueryable<T> just because it looks flexible. It couples the method to provider-based query semantics.

API Design: What Should Methods Return?

Return IEnumerable<T> when the caller only needs to enumerate a sequence and should not depend on a specific collection type.

public IEnumerable<string> GetSupportedCultures()
{
    yield return "en-US";
    yield return "es-ES";
    yield return "vi-VN";
}

Return List<T> or IReadOnlyList<T> when the result is materialized and stable.

public async Task<IReadOnlyList<UserDto>> GetUsersAsync()
{
    return await dbContext.Users
        .Select(user => new UserDto
        {
            Id = user.Id,
            Name = user.FirstName + " " + user.LastName
        })
        .ToListAsync();
}

Return IQueryable<T> only when you deliberately want the caller to keep composing the provider query.

public IQueryable<Product> QueryProducts()
{
    return dbContext.Products.AsNoTracking();
}

This should be a conscious design decision, not the default.

Common Real-World Scenarios

Use IEnumerable<T> for:

• In-memory collections.
• Simple service methods that process a sequence.
• Iterator methods using yield return.
• Domain logic that should not depend on EF Core or database providers.
• Returning data that has already been materialized.
• Passing results to UI code, reporting code, or business rules.

Use IQueryable<T> for:

• Building database queries before execution.
• Adding optional filters, sorting, paging, and projection.
• EF Core query composition inside repository/query services.
• Dynamic search screens.
• Provider-based query translation.
• Data access code that must keep execution server-side.

Common Mistake: Calling ToList() Too Early

Bad:

public async Task<List<OrderDto>> GetOrdersAsync(OrderSearchRequest request)
{
    var orders = await dbContext.Orders.ToListAsync();

    if (request.CustomerId is not null)
    {
        orders = orders
            .Where(order => order.CustomerId == request.CustomerId)
            .ToList();
    }

    return orders.Select(order => new OrderDto
    {
        Id = order.Id,
        Total = order.Total
    }).ToList();
}

Better:

public async Task<List<OrderDto>> GetOrdersAsync(OrderSearchRequest request)
{
    IQueryable<Order> query = dbContext.Orders;

    if (request.CustomerId is not null)
    {
        query = query.Where(order => order.CustomerId == request.CustomerId);
    }

    return await query
        .Select(order => new OrderDto
        {
            Id = order.Id,
            Total = order.Total
        })
        .ToListAsync();
}

Common Mistake: Returning IQueryable<T> to the Controller

Problematic:

public IQueryable<User> GetUsers()
{
    return dbContext.Users;
}

[HttpGet]
public async Task<IActionResult> GetUsers()
{
    var users = await userRepository.GetUsers()
        .Where(user => user.IsActive)
        .ToListAsync();

    return Ok(users);
}

This allows controller code to shape the database query directly.

Better:

public async Task<List<UserDto>> GetActiveUsersAsync()
{
    return await dbContext.Users
        .Where(user => user.IsActive)
        .Select(user => new UserDto
        {
            Id = user.Id,
            Email = user.Email
        })
        .ToListAsync();
}

[HttpGet]
public async Task<IActionResult> GetUsers()
{
    var users = await userService.GetActiveUsersAsync();

    return Ok(users);
}

The service or repository controls the query shape.

Common Mistake: Unit Tests with List.AsQueryable()

A unit test may pass with List<T>.AsQueryable() but fail with EF Core.

var users = new List<User>
{
    new() { Email = "[email protected]" }
}.AsQueryable();

var result = users
    .Where(user => NormalizeEmail(user.Email) == "[email protected]")
    .ToList();

This works in memory because .NET can call NormalizeEmail. But the same query may fail against a real database provider because the method cannot be translated.

For query behavior, integration tests against a realistic provider are often more valuable than only using in-memory AsQueryable().

Common Mistake: Hidden Database Queries

This method returns an IEnumerable<Order>:

public IEnumerable<Order> GetPendingOrders()
{
    return dbContext.Orders.Where(order => order.Status == OrderStatus.Pending);
}

Even though the return type is IEnumerable<Order>, the underlying object may still be an EF Core query. The database query may not execute until the caller enumerates it.

This can create hidden behavior:

var orders = orderRepository.GetPendingOrders();

// Database query may execute here.
foreach (var order in orders)
{
    Console.WriteLine(order.Id);
}

A clearer approach is to execute inside the data access method:

public async Task<List<Order>> GetPendingOrdersAsync()
{
    return await dbContext.Orders
        .Where(order => order.Status == OrderStatus.Pending)
        .ToListAsync();
}

Tracking and No-Tracking Queries

With EF Core, IQueryable<T> can also carry tracking behavior.

For read-only queries, use AsNoTracking() when entity tracking is not needed:

var users = await dbContext.Users
    .AsNoTracking()
    .Where(user => user.IsActive)
    .Select(user => new UserDto
    {
        Id = user.Id,
        Name = user.FirstName + " " + user.LastName
    })
    .ToListAsync();

This can reduce overhead for read-only scenarios.

However, do not blindly use AsNoTracking() when you plan to modify the entity and save changes.

Async Queries

IQueryable<T> from EF Core supports async query execution through methods such as:

• ToListAsync()
• FirstOrDefaultAsync()
• SingleOrDefaultAsync()
• AnyAsync()
• CountAsync()

Example:

bool exists = await dbContext.Users
    .AnyAsync(user => user.Email == email);

An in-memory IEnumerable<T> uses normal synchronous LINQ:

bool exists = users.Any(user => user.Email == email);

Do not confuse async query execution with in-memory enumeration. ToListAsync() is useful when the provider performs asynchronous I/O, such as database access.

IEnumerable<T> vs IAsyncEnumerable<T>

IEnumerable<T> is synchronous. It is suitable for normal in-memory enumeration.

IAsyncEnumerable<T> supports asynchronous streaming:

public async IAsyncEnumerable<string> ReadLinesAsync(string path)
{
    using var reader = File.OpenText(path);

    while (await reader.ReadLineAsync() is { } line)
    {
        yield return line;
    }
}

This is a separate concept from IQueryable<T>. A query can be provider-based and async-executed, but IQueryable<T> itself is not the same as IAsyncEnumerable<T>.

Choosing Between IEnumerable<T> and IQueryable<T>

A practical decision guide:

Best Practices

Keep database queries as IQueryable<T> until all provider-translatable filters, sorting, paging, and projection are applied.

var result = await dbContext.Products
    .Where(product => product.IsActive)
    .OrderBy(product => product.Name)
    .Skip(offset)
    .Take(limit)
    .Select(product => new ProductDto
    {
        Id = product.Id,
        Name = product.Name
    })
    .ToListAsync();

Materialize intentionally with ToList(), ToArray(), or async EF Core methods.

Avoid exposing IQueryable<T> outside the layer that owns query rules unless you intentionally want composability.

Avoid calling AsEnumerable() before filtering, ordering, paging, or selecting needed columns.

Do not use AsQueryable() as a fake performance improvement for in-memory collections.

Use projection to DTOs for API responses instead of returning full entities.

Watch for multiple enumeration of IEnumerable<T> and repeated execution of IQueryable<T>.

Use integration tests for important EF Core queries because in-memory LINQ does not behave exactly like provider-translated SQL.

Use IReadOnlyList<T> or IReadOnlyCollection<T> when a method needs collection semantics beyond simple enumeration.

Summary Mental Model

Use this mental model in interviews:

IEnumerable<T>
- Pulls objects one by one.
- Uses delegates.
- Runs LINQ in .NET memory.
- Best for objects already in the application.

IQueryable<T>
- Builds an expression tree.
- Uses a query provider.
- Can translate LINQ to another query language such as SQL.
- Best before executing database/provider queries.