Returning IEnumerable<T> vs. IQueryable<T>

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.

The top answer is good but it doesn't mention expression trees which explain "how" the two interfaces differ. Basically, there are two identical sets of LINQ extensions. Where(), Sum(), Count(), FirstOrDefault(), etc all have two versions: one that accepts functions and one that accepts expressions.

• The IEnumerable version signature is: Where(Func<Customer, bool> predicate)

• The IQueryable version signature is: Where(Expression<Func<Customer, bool>> predicate)

You've probably been using both of those without realizing it because both are called using identical syntax:

e.g. Where(x => x.City == "<City>") works on both IEnumerable and IQueryable

• When using Where() on an IEnumerable collection, the compiler passes a compiled function to Where()

• When using Where() on an IQueryable collection, the compiler passes an expression tree to Where(). An expression tree is like the reflection system but for code. The compiler converts your code into a data structure that describes what your code does in a format that's easily digestible.

Why bother with this expression tree thing? I just want Where() to filter my data. The main reason is that both the EF and Linq2SQL ORMs can convert expression trees directly into SQL where your code will execute much faster.

Oh, that sounds like a free performance boost, should I use AsQueryable() all over the place in that case? No, IQueryable is only useful if the underlying data provider can do something with it. Converting something like a regular List to IQueryable will not give you any benefit.

Yes, both use deferred execution. Let's illustrate the difference using the SQL Server profiler....

When we run the following code:

MarketDevEntities db = new MarketDevEntities();

IEnumerable<WebLog> first = db.WebLogs;
var second = first.Where(c => c.DurationSeconds > 10);
var third = second.Where(c => c.WebLogID > 100);
var result = third.Where(c => c.EmailAddress.Length > 11);

Console.Write(result.First().UserName);

In SQL Server profiler we find a command equal to:

"SELECT * FROM [dbo].[WebLog]"

It approximately takes 90 seconds to run that block of code against a WebLog table which has 1 million records.

So, all table records are loaded into memory as objects, and then with each .Where() it will be another filter in memory against these objects.

When we use IQueryable instead of IEnumerable in the above example (second line):

In SQL Server profiler we find a command equal to:

"SELECT TOP 1 * FROM [dbo].[WebLog] WHERE [DurationSeconds] > 10 AND [WebLogID] > 100 AND LEN([EmailAddress]) > 11"

It approximately takes four seconds to run this block of code using IQueryable.

IQueryable has a property called Expression which stores a tree expression which starts being created when we used the result in our example (which is called deferred execution), and at the end this expression will be converted to an SQL query to run on the database engine.