IEnumerable vs IQueryable


While fetching the results from the datatbase, 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 :

IEnumerable

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 :

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. Happy Coding…!!!

Advertisements

About Jasminder

.Net developer and blogger by profession, keen to learn new technologies, love nature, music and cricket.
This entry was posted in C#, Linq and tagged . Bookmark the permalink.

I have a suggestion..

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s