Home / c# / Differences / IEnumerable / IQueryable

Differences between IQueryable and IEnumerable in C# with Example

By Krunal Shah
Introduction:

Here I will explain difference between IQueryable and IEnumerable list in c# with example or IEnumerable vs IQueryable list in c# with example or what is difference between IQueryable and IEnumerable list in c# with example. In c# we use IQueryable and IEnumerable lists to perform data manipulation.

Description:

In previous posts I explained multiple inheritance in c#, vb.net, difference between throw and throw ex in c#, vb.net, constructor in c#, vb.net with example, delegates in c#, vb.net with example, sealed class in c#, vb.net with example, using statement in c#, vb.net and many articles related to interview questions, asp.net, c#.net. Now I will explain difference between IQueryable and IEnumerable list in c# with example.
Generally, we use IEnumerable and IQueryable to hold collection of data and perform data manipulation operations like filtering, etc. based on our requirements. We will see difference between IEnumerable and IQueryable with examples.

IEnumerable

1. If we want to use IEnumerable in our application, we can get it by adding System.Collections namespace.


2. IEnumerable best suitable for in-memory operations because first it will execute “select query” on server and it will load all the data into client memory then only it will apply all the filter conditions.

Suppose if we have table called EmployeeDetails in our database from that we need to get only top 3 records where users gender equals to “Male” for this if we use IEnumerable first it will execute “select query” on database server, it loads all the records into memory and then it filters the data based on our requirement.



3. For remote operations IEnumerable is not suggestable and its better use IEnumerable to perform query operations on in-memory collections like List, Array, etc.

In case if our tables contain more than 1 or 2 lac records then IEnumerable will fetch all the records into our application memory then it will perform filter conditions due to this our application becomes very slow.

4. IEnumerable is beneficial when you want to load all the data from query into memory and apply further filtering. Its best suitable for LINQ to Objects and LINQ to XML operations.

IEnumerable Example

Following is the simple example for IEnumerable collection.


DataClasses1DataContext dbcon = new DataClasses1DataContext();
IEnumerable<EmployeeDetail> emplist = dbcon.EmployeeDetails.Where(e => e.Gender.Equals("Male"));
emplist = emplist.Take<EmployeeDetail>(3);

When we execute above code we will get sql query like as shown below


SELECT [t0].[EmpId], [t0].[EmpName], [t0].[Location], [t0].[Gender]
FROM [dbo].[EmployeeDetails] AS [t0]
WHERE [t0].[Gender] = @p0


Here if you observe above query “Top 3” filtering condition is missing because IEnumerable will apply filtering conditions once it loads all the data in client-side memory.

IQueryable

1. If we want to use IQueryable in our application, we can get it by adding System.Linq namespace.



2. IQueryable is best suitable for out-memory (remote database) operations because it will execute select query with all filter conditions on server.

Suppose if we have table called EmployeeDetails in our database from that we need to get only top 3 records where users gender equals to “Male” for this if we use IQueryable it will execute “select query along with filter conditions” on database server and it loads only required records based on our conditions.



3. IQueryable is best suitable for LINQ to SQL operations.

IQueryable Example

Following is the simple example for IQueryable collection.


DataClasses1DataContext dbcon = new DataClasses1DataContext();
IQueryable<EmployeeDetail> emplist = dbcon.EmployeeDetails.Where(e => e.Gender.Equals("Male"));
emplist = emplist.Take<EmployeeDetail>(3);

When we execute above code we will get sql query like as shown below


SELECT TOP (3) [t0].[EmpId], [t0].[EmpName], [t0].[Location], [t0].[Gender]
FROM [dbo].[EmployeeDetails] AS [t0]
WHERE [t0].[Gender] = @p0

Here if you observe above query “Top 3” filtering condition also included it means IQueryable will apply all the filtering conditions on SQL Server itself to get only matching records instead of loading all the data into memory.

I hope it helps you to understand difference between IEnumerable and IQueryable.