Differences between IEnumerable and IQueryable

Back to: LINQ Tutorial For Beginners and Professionals

Differences between IEnumerable and IQueryable in C#

In this article, I am going to discuss the Differences Between IEnumerable and IQueryable in C# with Examples. Please read our previous article before proceeding to this article where we discussed the basics of IEnumerable and IQueryable in C# with Examples.

Differences Between IEnumerable and IQueryable in C#

The IEnumerable and IQueryable in C# are used to hold a collection of data and also used to perform data manipulation operations such as filtering, Ordering, Grouping, etc. based on the business requirements. Here in this article, we will see the difference between IEnumerable and IQueryable in C# with Examples. For a better understanding, please have a look at the following image. As you can see, with IEnumerable<T>, it fetches the record from the database without applying the filter. But with IQueryable<T>, it fetches the record from the database by applying the filter. If this is not clear at the moment, then don’t worry, we will understand this with examples.

Example to Understand the Differences Between IEnumerable and IQueryable in C#

Here in this demo, we will create a Console Application that will retrieve the data from the SQL Server database using the Entity Framework Database First approach. We are going to fetch the following Student information from the Student table.

Please use the below SQL Script to create and populate the Student table with the required test data.

-- Create the required Student table
CREATE TABLE Student
(
     ID INT PRIMARY KEY,
     FirstName VARCHAR(50),
     LastName VARCHAR(50),
     Gender VARCHAR(50)
)
GO

-- Insert the required test data
INSERT INTO Student VALUES (101, 'Steve', 'Smith', 'Male')
INSERT INTO Student VALUES (102, 'Sara', 'Pound', 'Female')
INSERT INTO Student VALUES (103, 'Ben', 'Stokes', 'Male')
INSERT INTO Student VALUES (104, 'Jos', 'Butler', 'Male')
INSERT INTO Student VALUES (105, 'Pam', 'Semi', 'Female')
GO

Once you created the Student table with the required test data then create a new Console application. Once you create the Console Application then add the ADO.NET Entity Data Model using Database First Approach pointing to the above database.

Example to Understand the Use of IEnumerable in C#

Let us modify the Main method of the Program class as shown below. In the below example, we are fetching the top 2 students from the Students table where the gender is Male. But we have split the LINQ query into two statements. The first statement contains the where method and the second statement contains the Take method. And then using a for each loop we are displaying the top 2 student information. Further to see what SQL Statement was generated and executed on the database by Entity Framework, here, we are using DBContext.Database.Log = Console.Write; statement which will log the SQL Script on the Console window.

using System;
using System.Collections.Generic;
using System.Linq;

namespace LINQDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            using (StudentDBContext DBContext = new StudentDBContext())
            {
                //To See What SQL Generated By Entity Framework
                DBContext.Database.Log = Console.Write;

                //Fetch the Top 2 Records from the Students Database table where Gender = Male
                IEnumerable<Student> listStudents = DBContext.Students.Where(x => x.Gender == "Male");
                listStudents = listStudents.Take(2);

                Console.WriteLine("Top 2 Student Where Gender = Male");
                foreach (var std in listStudents)
                {
                    Console.WriteLine(std.FirstName + " " + std.LastName);
                }
            }
              
            Console.ReadKey();
        }
    }
}

Here we create the LINQ Query using IEnumerable. Now, with the above changes in place, run the application and see the output and you should get the following output.

As you can see in the above SQL Script, it will not use the TOP clause. So, here it will fetch all the Male Students’ data from SQL Server to in-memory, and then it will filter the data in memory.

Example to Understand the Use of IQueryable in C#

Let us modify the Main method of the Program class as shown below to use IQueryable. The following example exactly does the same thing as the previous example, but here we are storing the query in a variable of IQueryable<Student> type. And for this, we are using the AsQueryable() method. Here also we are logging the generated SQL Statement on the Console window.

using System;
using System.Linq;
namespace LINQDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            using (StudentDBContext DBContext = new StudentDBContext())
            {
                //To See What SQL Generated By Entity Framework
                DBContext.Database.Log = Console.Write;

                //Fetch the Top 2 Records from the Students Database table where Gender = Male
                IQueryable<Student> listStudents = DBContext.Students
                                   .AsQueryable()
                                   .Where(x => x.Gender == "Male");
                listStudents = listStudents.Take(2);

                Console.WriteLine("Top 2 Student Where Gender = Male");
                foreach (var std in listStudents)
                {
                    Console.WriteLine(std.FirstName + " " + std.LastName);
                }
            }
              
            Console.ReadKey();
        }
    }
}

Here we create the LINQ Query using IQueryable. With the above changes in place, now run the application and see the output, and you should get the following output.

As you can see in the above image, it includes the TOP (2) clause in the SQL Script and then fetches the data from the database. That means now the filtering is happening on the database side. With this kept in mind let us discuss the differences between IEnumerable and IQueryable in C#.

Differences Between IEnumerable and IQueryable in C#:

IEnumerable in C#:

1. IEnumerable is an interface that is available in the System.Collections namespace.
2. While querying the data from the database, the IEnumerable executes the “Select Statement” on the server side (i.e. on the database), loads data into memory on the client side, and then only applied the filters on the retrieved data if we split the query to multiple statements. If we form the query using a single statement, then everything is going to be executed in the database.
3. So, we need to use the IEnumerable when we need to query the data from in-memory collections like List, Array, and so on.
4. The IEnumerable is mostly used for LINQ to Object and LINQ to XML queries.
5. The IEnumerable collection is of type forward only. That means it can only move in forward, it can’t move backward and between the items.
6. IEnumerable supports deferred execution. In our coming article, we will discuss deferred execution in detail.
7. It doesn’t support custom queries.
8. The IEnumerable doesn’t support lazy loading. Hence, it is not suitable for paging-like scenarios.

IQueryable in C#:

1. The IQueryable is an interface that exists in the System.Linq Namespace.
2. While querying the data from a database, the IQueryable executes the “select query” with the applied filter on the server side i.e. on the database, and then retrieves data even though if the query is created using multiple statements.
3. So, we need to use the IQueryable when we want to query the data from out-memory such as remote database, service, etc.
4. IQueryable is mostly used for LINQ to SQL and LINQ to Entities queries.
5. The collection of type IQueryable can move only forward, it can’t move backward and between the items.
6. IQueryable supports deferred execution.
7. It also supports custom queries using CreateQuery and Executes methods.
8. IQueryable supports lazy loading and hence it is suitable for paging-like scenarios.

If you go to the definition of the where method, then you will see that it is implemented as an extension method of the IQueryable interface as shown below.

In the next article, I am going to discuss the LINQ Extension Methods in C# with Examples. Here, in this article, I try to explain the Differences Between IEnumerable and IQueryable in C#. I hope this article gives you a very good understanding of the Differences Between IEnumerable and IQueryable in C# with Examples.