IEnumerable vs IQueryable in C#

While fetching the results from the database, we normally use IEnumerable or IQueryable etc as the return types. and use them interchangeably, without knowing the affect of the use of the one or the other.

There is one major difference between the two, which can help us enhance the performance of the database centric applications to a great extent, when we are using these two. Let me explain what actually it is. We will be using the SQL Server profiler to analyze the difference between the two :

Following is the IEnumerable based query and its corresponding SQL query generated :

As you can see above, in case we use the IEnumerable, it will execute the basic query (or the query with Filter 1) on the database server and performs the rest of the operations on it (like Filter 2 here), in the code server or you can say the in-memory collections.

So it works as, fetches the records from the database which have ID < 7 , into the in-memory collection and then filter the rest of the data to return only the records that are starting with username as "P". So finally we get the result, but the initial request has fetched extra records into the local memory of the code, which did not matched the second criteria.

Now let's analyze the IQueryable :

As we can see, in this case, IQueryable will execute the basic query (or the query with Filter 1) along with the rest of the filters (like filter 2 in this case), on the database server and fetches the filtered records. So it actually combines both the filters and then execute the complete query on the database to get the results. This can be seen clearly from the SQL profiler generated query.

So IEnumerable is normally preferred in case where we need to perform operations on in-memory collections, rather then getting the list from the databases and then performing the filters or any other operations. Thus, helps in improving the traffic to the database.

Apart from this, there are other basic differences also between the two like the namsepaces are different, iqueryable is forward only type etc. , but I feel, this was the major difference between the two.