I was working in a .Net shop helping a medical insurance company rewrite their “legacy” system from the database all the way to the front end web site.

The database was SQL Servier. The project manager was set on Entity Framework, IIS, and Web API.

IEnumerable and IQueryable are both interfaces in C# that expose an enumerator and allow for iteration over a collection of objects.

It is extremely important that the programmer know the difference between IEnumerable and IQueryable and the context in which each should be used.

IEnumerable should be used for collections that are already in memory. IQueryable should be used when querying external data sources, like a database in a Data Access Layer (DAL).

When querying a database (like SQL Server) in a method defined to return data, using IEnumerable causes the query to bring over all of the query’s table rows and then filter them (your WHERE clause) on the platform making the call (like a 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 to the calling method 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 the job of the database and is what the database is designed to do – query information.

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.

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.