Difference between IEnumerable and IQueryable

IEnumerable vs IQueryable

The main difference between IEnumerable and IQueryable in C# is that IQueryable queries out-of-memory data stores, while IEnumerable queries in-memory data. Moreover, IQueryable is part of .NET's System.LINQ namespace, while IEnumerable is in System.Collections namespace.

IQueryable contains methods for constructing expression trees. IQueryable inherits IEnumerable, so IQueryable does everything that IEnumerable does. IQueryable extends the IEnumerable with logic for querying data.

LINQ and Entity Framework Example

This example illustrate shows the difference between IEnumerable and IQueryable when working with databases using LINQ and EntityFramework:

IQueryable<Customer> customers = _context.Customers.Where(c => c.IsActive);
IEnumerable<Customer> customers = _context.Customers.AsEnumerable().Where(c => c.IsActive);

For the IQueryable approach the translated SQL is:

SELECT * FROM [Customers] AS [c] WHERE [c].[IsActive] == 1 

On the other hand, the IEnumerable approach selects all records and loads them to memory. Then, it searches in-memory:

SELECT * FROM [Customers] AS [c]

What is IQueryable?

IQueryable<T> is a C# interface that lets you query different data sources. The type T specifies the type of the data source that you're querying. Under the hood, IQueryable uses expression trees that translate LINQ queries into the query language for the data provided.

Since IQueryable<T> extends the IEnumerable<T> interface, we can enumerate the result.

Interpreted Queries

Interpreted queries in C# are operations that translate expression trees at runtime. For example, EntityFramework translates IQueryable expression trees to SQL queries.

The benefit of using interpreted queries is that we can write the same code, but have different translations. For example, we can write the same C# code for the SQL server and for the PostgreSQL. We use the IQueryable interface, but interpreter figures out the underlying queries.

Local Queries

Local queries in C# are operations that execute in-memory. You can use local queries to operate on any collection that implements the IEnumerable interface.

All the code that we execute using local queries is part of the Intermediate Language(IL) code. That's different from interpreted queries that can be translated differently during runtime.

How to cast IQueryable to IEnumerable?

To cast IQueryable to IEnumerable use the Enumerable.AsEnumerable method. Enumerable.AsEnumerable method forces the compiler to evaluate queries on the object locally.

When you cast IQueryable to IEnumerable you have more flexibility with operations that you can perform on the collection. For example, some grouping operations are restricted in SQL when we use IQueryable, but work locally using IEnumerable.

The main disadvantage of casting IQueryable to IEnumerable is slower performance. Since IEnumerable works on local objects, we first need to load all the objects to memory. IQueryable is better in that sense because we can pick just what we need.

How to cast IEnumerable to IQueryable?

To cast IQueryable to IEnumerable, use the AsQueryable method.

The AsQueryable method wraps the IEnumerable with IQueryable interface. That lets you build the expression tree without executing on the collection. Once you enumerate it, you get the same results.