Differences between IEnumerable and IQueryable

Differences between IEnumerable and IQueryable in C#

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

The IEnumerable and IQueryable 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 with examples.

Differences between IEnumerable and IQueryable in C#

Example:

Here in this demo, we will create a console application which will retrieve the data from the SQL Server database using Entity Framework database first approach.

We are going to fetch the following Student information from the Student table.

Differences between IEnumerable and IQueryable in C#

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 Database approach.

Example: Using IEnumerable

Let us modify the Program class as shown below.

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

namespace LINQDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            StudentDBContext dBContext = new StudentDBContext();
            IEnumerable<Student> listStudents = dBContext.Students.Where(x => x.Gender == "Male");
            listStudents = listStudents.Take(2);

            foreach(var std in listStudents)
            {
                Console.WriteLine(std.FirstName + " " + std.LastName);
            }

            Console.ReadKey();
        } 
    }
}

Here we create the LINQ Query using IEnumerable. Please use SQL Profiler to log the SQL Script. Now run the application and you will see the following SQL Script is generated and executed.

SELECT 
    [Extent1].[ID] AS [ID], 
    [Extent1].[FirstName] AS [FirstName], 
    [Extent1].[LastName] AS [LastName], 
    [Extent1].[Gender] AS [Gender]
    FROM [dbo].[Student] AS [Extent1]
    WHERE 'Male' = [Extent1].[Gender]

As shown in the above SQL Script, it will not use the TOP clause. So here it will fetch the data from SQL Server to in-memory and then it will filter the data.

Example: Using IQueryable

Let us modify the Program class as shown below to use IQueryable.

using System;
using System.Linq;

namespace LINQDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            StudentDBContext dBContext = new StudentDBContext();
            IQueryable<Student> listStudents = dBContext.Students
                                .AsQueryable()
                                .Where(x => x.Gender == "Male");
            listStudents = listStudents.Take(2);

            foreach(var std in listStudents)
            {
                Console.WriteLine(std.FirstName + " " + std.LastName);
            }

            Console.ReadKey();
        } 
    }
}

Once you run the application, it will create the following SQL Script.

SELECT TOP (2) 
    [Extent1].[ID] AS [ID], 
    [Extent1].[FirstName] AS [FirstName], 
    [Extent1].[LastName] AS [LastName], 
    [Extent1].[Gender] AS [Gender]
    FROM [dbo].[Student] AS [Extent1]
    WHERE 'Male' = [Extent1].[Gender]

As you can see it includes the TOP clause in the SQL Script and then fetches the data from the database. With this keep in mind let us discuss the differences between IEnumerable and IQueryable.

Differences between IEnumerable and IQueryable in C#:

IEnumerable:
  1. IEnumerable is an interface which 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.
  3. So you need to use the IEnumerable when you 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.
  7. It doesn’t support custom query.
  8. The IEnumerable doesn’t support lazy loading. Hence, it is not suitable for paging like scenarios.
IQueryable:
  1. The IQueryable is an interface which 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 server-side i.e. on the database and then retrieve data.
  3. So you need to use the IQueryable when you 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 query using CreateQuery and Execute methods.
  8. IQueryable support lazy loading and hence it is suitable for paging like scenarios.

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

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

Leave a Reply

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