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 Server. The project manager was set on Entity Framework, IIS, and Web API.

Looking at their middleware code, I noticed that they were using IEnumerable when querying the database for a lists of information. This is bad practice because using IEnumerable in this context results in large amounts of data being returned from the database which, in turn, clogs the network and introduces a new workload on the web server because it has to iterate over that potentially large data set.

Let me explain.

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 rows of the tables in the query and then filter them (the 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. After all 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, one worked at the shop that I mentioned at the top.

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.