Differences between IEnumerable and IQueryable

Back to: LINQ Tutorial For Beginners and Professionals

Differences Between IEnumerable and IQueryable in C#

In this article, I will 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. This article will show 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, IEnumerable<T> fetches the record from the database without applying the filter. But IQueryable<T> fetches the record from the database by applying the filter. 

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

In this demo, we will create a Console Application to 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 SQL script below 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

Create a new Console application once you created the Student table with the required test data. Once you create the Console Application, add the ADO.NET Entity Data Model using the 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, we are using DBContext.Database.Log = Console.Write; statement that 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. 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 does the same thing as the previous one, but here, we store the query in a variable of IQueryable<Student> type. For this, we are using the AsQueryable() method. We are also 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 in mind, let us discuss the differences between IEnumerable and IQueryable in C#.

IEnumerable vs. IQueryable in C#

IEnumerable and IQueryable are two interfaces in C# used to work with data collections. They have distinct purposes and characteristics, and understanding when to use each is important in various programming scenarios.

Purpose and Data Sources:

• IEnumerable: Primarily used for querying and manipulating in-memory collections, such as arrays, lists, and IEnumerable-compatible collections. Designed for querying data that is already in memory.
• IQueryable: Used for querying data from external data sources that may not be in memory, such as databases, web services, or remote data stores. Designed for querying data that resides outside of the application’s memory.

Execution Location:

• IEnumerable: Operations are executed in memory. All data is retrieved from the collection, and subsequent operations are performed in memory.
• IQueryable: Operations are typically translated into a query language (e.g., SQL for databases) and executed on the data source. This allows for server-side processing and optimization.

Lazy Evaluation:

• IEnumerable: Supports lazy evaluation. Operations are only executed when the collection is enumerated (e.g., in a for each loop).
• IQueryable: Also supports lazy evaluation. Queries are not executed until you enumerate the results, allowing for efficient resource use.

Query Translation:

• IEnumerable: LINQ methods are executed in memory on the entire data set. Filtering, sorting, and other operations are performed locally.
• IQueryable: LINQ methods are translated into a query language specific to the data source (e.g., SQL) and executed on the data source. This allows for efficient server-side operations.

Suitable Use Cases:

• IEnumerable: Suitable for working with small to moderate-sized in-memory collections where the entire data set can fit in memory. Typically used for in-memory data manipulation and querying.
• IQueryable: Suitable for working with large data sets or data sources external to the application, such as databases.
  It is ideal for offloading query execution to the data source, enabling efficient database querying.

Common Use Cases:

• IEnumerable: Used for querying and manipulating in-memory collections, filtering data, and applying transformations.
• IQueryable: Used with Entity Framework for querying databases using LINQ, where queries are translated into SQL statements.

Examples:

• IEnumerable: Querying and filtering a list of objects in memory. Working with arrays or lists.
• IQueryable: Querying a database using Entity Framework. Querying data from a web service or remote data source.

Performance Considerations:

• IEnumerable: It may not be as performant when dealing with large data sets or remote data sources because all data is brought into memory.
• IQueryable: More efficient for large data sets and data sources external to the application because queries are executed on the data source.

Understanding the differences between IEnumerable and IQueryable is crucial for writing efficient data access code in C#, particularly when dealing with large data sets or performance-critical applications.

So, choose IEnumerable<T> when working with in-memory collections where data is stored locally in the application’s memory. Use IQueryable<T> when querying data from external data sources or dealing with large data sets, as it allows for efficient server-side processing and query optimization. The appropriate choice depends on the nature of the data source and your performance requirements.

In the next article, I will discuss the LINQ Extension Methods in C# with Examples. In this article, I 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.