IQueryable VS IEnumerable in C#

As C# developers we use IEnumerable and IQueryable for data manipulation almost all the time in our application projects, and for me, sometimes is confusing when to use one or another. So I searched in internet and created the table bellow so if I forget I can quickly come to this post and review the differences so I can get the most of each and make sure I use it correctly.

IQueryable IEnumerable

IEnumerable IQueryable
Namespace. System.Collections System.Linq
Derives from. No base interface. Derives from IEnumerable.
Deferred Execution. Supported. Supported.
Lazy Loading. Not Supported. Suported.
Suitable for. LINQ to Object and LINQ to XML queries. LINQ to SQL queries.
Custom Query. Doesn’t supports. Supports using CreateQuery and Execute methods.
Other characteristics. It is a read only collection.
It iterates only in forward direction.
It works with collection in local memory.
It does not support adding, removing objects on collection.
It provides enumerator to iterate collection in forward direction.
It implements IEnumerable,so the results can be iterated using foreach.
It is best suited to query external data sources,(like remote database, service) collections.
It works with queryable data provider.
It creates query using an Expression Tree.
It is used to query a queryable data source.
When to use… Working with the read only collection.
Need to read the objects in forward direction only.
Not concerned about thread safety.
Want to iterate the collection’s objects using foreach.
Working with the queryable datasource.
Need to apply filter on data at the datasource.
Need to apply paging , composition.
Working with external data source.
Needs to load data in deferred way.
Need to use foreach to iterate collection.

IEnumerable Example:

DataContext ctx = new DataContext ();
IEnumerable<People> collection = ctx.People.Where(p => p.Name.StartsWith("j"));
collection = collection.Take<People>(20); 

Generated SQL statements of above query will be :

 SELECT [t0].[ID], [t0].[Name], [t0].[Salary] FROM [People] AS [t0]
WHERE [t0].[Name] LIKE @p0

Notice that in this query “top 20” is missing since IEnumerable place all the records in memory and filters on client side.

IQeryable Example:

 DataContext ctx = new DataContext ();
IQueryable<People> collection = ctx.People.Where(p => p.Name.StartsWith("j"));
collection = collection.Take<People>(20); 

Generated SQL statements of above query will be :

 
SELECT TOP 20 [t0].[ID], [t0].[Name], [t0].[Salary] FROM [People] AS [t0]
WHERE [t0].[Name] LIKE @p0

Notice that in this query “top 20” is exist, since IQueryable executes query in SQL server with all filters, and put in memory the result already filtered.

Official documentation for IQueryable and IEnumerable:

https://msdn.microsoft.com/en-us/library/system.linq.iqueryable(v=vs.100).aspx
https://msdn.microsoft.com/en-us/library/system.collections.ienumerable(v=vs.110).aspx