SQL Server Interview Question #287

What is the difference between IQueryable and IEnumerable in an EF Core query?

ASP.NET Core, EF Core & SQL Server Real-World Scenarios Senior Advanced

Detailed Explanation

IQueryable represents a query expression that a provider such as EF Core can translate and execute remotely. Query operators added before materialization can become part of the SQL statement.

After data is materialized into an in-memory collection, IEnumerable operations run in .NET memory. Calling ToList too early can therefore retrieve far more rows than required.

A common performance principle is to apply translatable filtering, ordering, projection, and pagination before materialization.

Code Example

var query = db.Products
    .Where(p => p.IsActive)
    .OrderByDescending(p => p.Rating)
    .Select(p => new { p.Id, p.Name, p.Price });

var results = await query.Take(20).ToListAsync();