c# - Returning IEnumerable vs. IQueryable

What is the difference between returning IQueryable vs. IEnumerable?

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

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

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

Answer

Yes, both will give you deferred execution.

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

For the IEnumerable 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 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 custs = ...;
// Later on...
var goldCustomers = custs.Where(c => c.IsGold);

This is quite an important difference, and working on IQueryable 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 will cause all of your rows to be loaded in memory.