Returning IEnumerable vs. IQueryable

Question

What is the difference between returning IQueryable<T> vs. IEnumerable<T>?

IQueryable<Customer> custs = from c in db.Customers
where c.City == "<City>"
select c;

IEnumerable<Customer> custs = from c in db.Customers
where c.City == "<City>"
select c;

Will both be deferred execution and when should one be preferred over the other?

Answer 1

In general terms I would recommend the following:

• Return IQueryable<T> if you want to enable the developer using your method to refine the query you return before executing.

• Return IEnumerable if you want to transport a set of Objects to enumerate over.

Imagine an IQueryable as that what it is - a "query" for data (which you can refine if you want to). An IEnumerable is a set of objects (which has already been received or was created) over which you can enumerate.

Answer 2

Yes, both will give you deferred execution.

The difference is that IQueryable<T> is the interface that allows LINQ-to-SQL (LINQ.-to-anything really) to work. So if you further refine your query on an IQueryable<T>, that query will be executed in the database, if possible.

For the IEnumerable<T> case, it will be LINQ-to-object, meaning that all objects matching the original query will have to be loaded into memory from the database.

In code:

IQueryable<Customer> custs = ...;
// Later on...
var goldCustomers = custs.Where(c => c.IsGold);

That code will execute SQL to only select gold customers. The following code, on the other hand, will execute the original query in the database, then filtering out the non-gold customers in the memory:

IEnumerable<Customer> custs = ...;
// Later on...
var goldCustomers = custs.Where(c => c.IsGold);

This is quite an important difference, and working on IQueryable<T> can in many cases save you from returning too many rows from the database. Another prime example is doing paging: If you use Take and Skip on IQueryable, you will only get the number of rows requested; doing that on an IEnumerable<T> will cause all of your rows to be loaded in memory.

Answer 3

I recently ran into an issue with IEnumerable v. IQueryable. The algorithm being used first performed an IQueryable query to obtain a set of results. These were then passed to a foreach loop, with the items instantiated as an Entity Framework (EF) class. This EF class was then used in the from clause of a Linq to Entity query, causing the result to be IEnumerable.

I'm fairly new to EF and Linq for Entities, so it took a while to figure out what the bottleneck was. Using MiniProfiling, I found the query and then converted all of the individual operations to a single IQueryable Linq for Entities query. The IEnumerable took 15 seconds and the IQueryable took 0.5 seconds to execute. There were three tables involved and, after reading this, I believe that the IEnumerable query was actually forming a three table cross-product and filtering the results.

Try to use IQueryables as a rule-of-thumb and profile your work to make your changes measurable.

Answer 4

these are some differences between IQueryable<T> and IEnumerable<T>

Answer 5

While using LINQ to Entities, it is important to understand when to use IEnumerable and IQueryable. If we use IEnumerable, the query will be executed immediately. If we use IQueryable, the query execution will be deferred until the application requests the enumeration. Now let's see what should be considered while deciding whether to use IQueryable or IEnumerable. Using IQueryable gives you a chance to create a complex LINQ query using multiple statements without executing the query at the database level. The query gets executed only when the final LINQ query gets enumerated.

Answer 6

In addition to first 2 really good answers (by driis & by Jacob) :

IEnumerable interface is in the System.Collections namespace.

The IEnumerable object represents a set of data in memory and can move on this data only forward. The query represented by the IEnumerable object is executed immediately and completely, so the application receives data quickly.

When the query is executed, IEnumerable loads all the data, and if we need to filter it, the filtering itself is done on the client side.

IQueryable interface is located in the System.Linq namespace.

The IQueryable object provides remote access to the database and allows you to navigate through the data either in a direct order from beginning to end, or in the reverse order. In the process of creating a query, the returned object is IQueryable, the query is optimized. As a result, less memory is consumed during its execution, less network bandwidth, but at the same time it can be processed slightly more slowly than a query that returns an IEnumerable object.

What to choose?

If you need the entire set of returned data, then it's better to use IEnumerable, which provides the maximum speed.

If you DO NOT need the entire set of returned data, but only some filtered data, then it's better to use IQueryable.