IQueryable VS IEnumerable … a must know!!!

There are many numerous articles written upon the same but still I would like to re-iterate in this post the most common stated example to state the difference between the IQueryable and IEnumerable.
Difference:
IEnumerable: The collection implementing the IEnumerable interface is designed in a manner to do in-memory operations, where by the data is loaded altogether in one go to the memory and then filtered within the same.
When the following code is ran in conjunction with ADO.NET entity model the following resultant SQL query is fired to the server as below:

 using (AlphaDbEntities dc  = new AlphaDbEntities())
 {
       IEnumerable<Employee> enumrableList = from e in dc.Employees
                                             select e;

       List<Employee> empList1 = enumrableList.Take(10).ToList();
 }

Resulting TSQL:

SELECT 
[Extent1].[employeeId] AS [employeeId], 
[Extent1].[firstName] AS [firstName], 
[Extent1].[lastName] AS [lastName], 
[Extent1].[designation] AS [designation], 
[Extent1].[contactNumber] AS [contactNumber], 
[Extent1].[dateOfJoining] AS [dateOfJoining], 
[Extent1].[rating] AS [rating]
FROM [dbo].[Employees] AS [Extent1]

IQueryable: The collection implementing the IQueryable interface is designed in a manner to alter the LINQ query to a suitable/optimal query being fired on the external data source to filter out the data on the external data source itself and thus bringing in only the data required after filtration to the process in-memory.
This methodology is most suitable while working with large data sets and where paging mechanism also need to be implemented, and working with chunks of data would be sufficient for the application, rather than in memory analytics where entire data set is required within the memory for aggregations.
When the following code is ran in conjunction with ADO.NET entity model the following resultant SQL query is fired to the server as below:

 using (AlphaDbEntities dc  = new AlphaDbEntities())
 {
        IQueryable<Employee> queryableList = from e in dc.Employees
                                             select e;

        List<Employee> empList2 = queryableList.Take(10).ToList();
 }

Resulting TSQL:

SELECT TOP (10) 
.[employeeId] AS [employeeId], 
.[firstName] AS [firstName], 
.[lastName] AS [lastName], 
.[designation] AS [designation], 
.[contactNumber] AS [contactNumber], 
.[dateOfJoining] AS [dateOfJoining], 
.[rating] AS [rating]
FROM [dbo].[Employees] AS 

From the above I can conclude that IQueryable being smarter than IEnumerable is altering the query fired to the database and filtering the data on the database itself only (using the TOP 10 operator in TSQL), rather than compared to IEnumerable bringing in the entire set of data back to the application.

References:
http://msdn.microsoft.com/en-IN/library/system.collections.ienumerable.aspx
http://msdn.microsoft.com/en-IN/library/system.linq.iqueryable.aspx

Leave a Reply

Your email address will not be published. Required fields are marked *

*