Difference Between IQueryable, IEnumerable, And IList In LINQ

This article explores the usages of IEnumerable, IList, and IQueryable, differences between them, and how to write a simple query with both interfaces in LINQ with code samples.

 

Let's start with the simple question - what is the use of both interfaces and where do they exist in a namespace.

• IEnumerable is useful when we want to iterate the collection of objects which deals with in-process memory.
• IQueryable is useful when we want to iterate a collection of objects which deals with ad-hoc queries against the data source or remote database, like SQL Server.
• IList is useful when we want to perform any operation like Add, Remove or Get item at specific index position in the collection.

We use IEnumerable and IQueryable for data manipulation while querying the data from a database or collections. IEnumerable and IQueryable, both are interfaces to a .NET collection. IQueryable exists in System.Linq Namespace while IEnumerable exists in System.Collections Namespace.

 

Before starting any example, we all should know that IQueryable is inherited from IEnumerable so whatever IEnumerable can do, we can achieve with IQueryable as well.

 

Let's have a look at both interfaces' base level.

 

IQueryable interface

=> As per the above snippet, it exists in System.Collections namespace.

=> IQueryable is best suitable for LINQ to SQL Conversion.

=> It supports custom query and lazzy loading.

=> IQueryable executes a query on a server with all filter conditions on server-side and gets the records which are matching with the filter condition.

 

Here, I will demonstrate the phenomenon with a code example as below. 

 

LINQ Query for using IQueryable.

public void IQueryableExample()
{
    IQueryable<EmployeeDetail> empDetail = dbContext.EmployeeDetails.Where(emp => emp.Employee_Name.StartsWith("j"));
    var result = empDetail.Take(2);
 }

SQL Query for the above LINQ query, 

SELECT TOP (2)
    [Extent1].[Eemployee_Number] AS [Eemployee_Number],
    [Extent1].[Employee_Name] AS [Employee_Name],
    [Extent1].[Department_Id] AS [Department_Id],
    [Extent1].[Salary] AS [Salary]
    FROM [dbo].[EmployeeDetails] AS [Extent1]
    WHERE [Extent1].[Employee_Name] LIKE 'j%'

In the above example, we see that IQueryable executed all the filters on the server-side and fetched the records that are matching all conditions and filters. In the above query, .Take(2) filter is applied on server-side and SQL Query takes the Top (2) records. So, the filter is applied on the server side.

IEnumerable interface

public void IEnumerableExample()
{
    IEnumerable<EmployeeDetail> empDetail = dbContext.EmployeeDetails.Where(emp => emp.Employee_Name.StartsWith("j"));
    var result = empDetail.Take(2);
}

 SQL Query for the above LINQ query

SELECT
    [Extent1].[Eemployee_Number] AS [Eemployee_Number],
    [Extent1].[Employee_Name] AS [Employee_Name],
    [Extent1].[Department_Id] AS [Department_Id],
    [Extent1].[Salary] AS [Salary]
    FROM [dbo].[EmployeeDetails] AS [Extent1]
    WHERE [Extent1].[Employee_Name] LIKE 'j%'

In the above example, we can see that IEnumerable filters did not execute on server side and it fetched all the records which are matching all conditions. In the above query, the .Take(2) filter was not applied on server side. The SQL query gets all records which match to the where condition. So, the filter is not applied on the server side but first, it fetches all the records from the server and applies on the client side.

 

IList <T> interface 

 

The IList interface is useful when you want to perform any operation like get item, add a new item, or remove an item at a specific index from the collection.

 

 

IList<T> is a generic collection which resides in System.Collections.Generic namespace and allows us to add a new item to the list and remove items or get items from a specific index position.

 

LINQ Query for IList<T> 

public void IListExample()
{
    IList<EmployeeDetail> empDetail = dbContext.EmployeeDetails
                                      .Where(emp => emp.Employee_Name.StartsWith("j"))
                                      .ToList();
    empDetail.Add(new EmployeeDetail
    {
       Eemployee_Number = 10012,
       Employee_Name = "Peter",
       Department_Id = 3,
       Salary = 45000
    });

    empDetail.RemoveAt(4);
}

In the above example, I have fetched the records from the database based on a condition. After that, I have added a new record to the same list and removed a record based on a specific index.