It is extremely important that the programmer knows the difference between IEnumerable and IQueryable.

When used in the Service layer, which communicates with the Data layer, the performance difference is extreme.

IEnumerable causes a query to bring over all of the query’s table rows and then filter them (your WHERE clause) on the web server by iterating over every row and applying the “WHERE”.

That’s work not intended for a web server and can mean the difference between a quick response and an HTTP 503 – Service Unavailable because the web server thread servicing the request is held up spinning through the data set.

That’s where IQueryable comes in. IQueryable passes the SQL query to the database server for execution and the rows returned are those that meet the WHERE clause of the query.

And that’s the way it should be: let the database server process the query; that’s what the database does.

This is extremely relevant when using Entity Framework (EF). You don’t want EF to bring back thousands of rows for the web server to spin through, just the relevant rows.

I have found that most people are not aware of this important difference. I know a few MCSD’s that were not aware of this.

IEnumerable has it’s place; it’s good for in-memory data.

So if you have a Service layer that communicates queries to the Data layer ensure your Data layer procedure definitions are written using IQueryable. Do that and enjoy the performance bump.