Returning IEnumerable<T> vs IQueryable<T>

Question

what is the difference between returning iqueryable vs ienumerable.

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? When should one be preferred over the other?

Answer 1

Both will give you deferred exectuion, yes.

As for which is preferred over the other, it depends on what your underlying datasource is.

Returning an IEnumerable will automatically force the runtime to use LINQ to Objects to query your collection.

Returning an IQueryable (which implements IEnumerable, by the way) provides the extra functionality to translate your query into something that might perform better on the underlying source (LINQ to SQL, LINQ to XML, etc.).

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 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.