All About IEnumerable and IQueryable

Code Example

In this example, we are going to fetch the top name of the person who works with the project "TAAGUNG". The table structure and data are as below.

 

Table Structure and Data

For this, we will be using the same query with different return types as shown below. Certainly, they return the same result.

static void QueryWithIEnumerable(StudyEntities studyEntities)
 {
     IEnumerable<PROJECT_TEAM> projects =
         studyEntities.PROJECT_TEAM.Where(e => e.PRJ_ID.Equals("TAAGUNG"));
     Console.WriteLine(projects.ToString());
     projects = projects.Take(1);
     Console.WriteLine(projects.ToString());
     foreach (var project in projects)
     {
         Console.WriteLine(project.FRST_NM + " " + project.LAST_NM);
     }
 }
 static void QueryWithIQueryable(StudyEntities studyEntities)
 {

     IQueryable<PROJECT_TEAM> projects =
         studyEntities.PROJECT_TEAM.Where(e => e.PRJ_ID.Equals("TAAGUNG"));
     projects = projects.Take(1);
     Console.WriteLine(projects.ToString());

     foreach (var project in projects)
     {
         Console.WriteLine(project.FRST_NM + " " + project.LAST_NM);
     }
 }

How does it work?

For IQueryable, the generated query is this. Please note the TOP(1) in the first line.

SELECT TOP (1)
    [Extent1].[PRJ_ID] AS [PRJ_ID],
    [Extent1].[FRST_NM] AS [FRST_NM],
    [Extent1].[LAST_NM] AS [LAST_NM]
    FROM (SELECT
    [PROJECT_TEAM].[PRJ_ID] AS [PRJ_ID],
    [PROJECT_TEAM].[FRST_NM] AS [FRST_NM],
    [PROJECT_TEAM].[LAST_NM] AS [LAST_NM]
    FROM [dbo].[PROJECT_TEAM] AS [PROJECT_TEAM]) AS [Extent1]
    WHERE 'TAAGUNG' = [Extent1].[PRJ_ID]

While IEnumerable generates the following SQL query and then performs in-memory filtering of data, i.e., getting the required data is a two-step process.

SELECT
    [Extent1].[PRJ_ID] AS [PRJ_ID],
    [Extent1].[FRST_NM] AS [FRST_NM],
    [Extent1].[LAST_NM] AS [LAST_NM]
    FROM (SELECT
    [PROJECT_TEAM].[PRJ_ID] AS [PRJ_ID],
    [PROJECT_TEAM].[FRST_NM] AS [FRST_NM],
    [PROJECT_TEAM].[LAST_NM] AS [LAST_NM]
    FROM [dbo].[PROJECT_TEAM] AS [PROJECT_TEAM]) AS [Extent1]
    WHERE 'TAAGUNG' = [Extent1].[PRJ_ID]

Based on this, we can easily depict the processing of these two in the following 2 images.

 

Figure 1 - IQUERYABLE

 

 

Figure 2 - IEnumerable

 

Moreover, we can see above that the query is written in two statements. If our LINQ is written in one statement like this,

IEnumerable<PROJECT_TEAM> projects = studyEntities.PROJECT_TEAM
.Where(e => e.PRJ_ID.Equals("TAAGUNG")).Take(1);

IEnumerable<PROJECT_TEAM> projects = studyEntities.PROJECT_TEAM
.Where(e => e.PRJ_ID.Equals("TAAGUNG")).Take(1);

Then, it will generate this same query in both of the scenarios. So, please notice the different ways of writing the queries in both of the scenarios, i.e., everything is mentioned in a single query.

SELECT TOP (1)
    [Extent1].[PRJ_ID] AS [PRJ_ID],
    [Extent1].[FRST_NM] AS [FRST_NM],
    [Extent1].[LAST_NM] AS [LAST_NM]
    FROM (SELECT
    [PROJECT_TEAM].[PRJ_ID] AS [PRJ_ID],
    [PROJECT_TEAM].[FRST_NM] AS [FRST_NM],
    [PROJECT_TEAM].[LAST_NM] AS [LAST_NM]
    FROM [dbo].[PROJECT_TEAM] AS [PROJECT_TEAM]) AS [Extent1]
    WHERE 'TAAGUNG' = [Extent1].[PRJ_ID]

Relationship

When we see the definition (F12) of IQueryable, we see it implements IEnumerable.

public interface IQueryable<out T> : IEnumerable<T>, IEnumerable, IQueryable
   {
   }

public interface IQueryable : IEnumerable
   {
   }

With this, IQueryable gets these 3 additional read-only properties.

public interface IQueryable : IEnumerable
    {
        //
        // Summary:
        //     Gets the expression tree that is associated with the instance of System.Linq.IQueryable.
        //
        // Returns:
        //     The System.Linq.Expressions.Expression that is associated with this instance
        //     of System.Linq.IQueryable.
        Expression Expression { get; }
        //
        // Summary:
        //     Gets the type of the element(s) that are returned when the expression tree associated
        //     with this instance of System.Linq.IQueryable is executed.
        //
        // Returns:
        //     A System.Type that represents the type of the element(s) that are returned when
        //     the expression tree associated with this object is executed.
        Type ElementType { get; }
        //
        // Summary:
        //     Gets the query provider that is associated with this data source.
        //
        // Returns:
        //     The System.Linq.IQueryProvider that is associated with this data source.
        IQueryProvider Provider { get; }
    }

Similarities

• They both support forward-only iteration only on a collection
• They both support deferred execution.
• Whatever IEnumerable can do, IQueryable can also do but NOT the other way around.

Differences

Feature	IEnumerable	IQueryable
Namespace	System.Collection	System.Linq
Lazy Loading	No Support	Supported
Custom Query	No Support	Supported

 

What to use when?

IEnumerable

• If data is available in the same process; i.e., in memory collection, Array, ArrayList etc.
• LINQ to Object and LINQ to XML.
• It is not suitable for paging scenarios.

IQueryable

• When querying data from out of process; i.e., SQL, Oracle or LDAP etc.
• Good for LINQ to SQL.
• By the virtue of the support of lazy loading, it is best for the query in paging scenarios.
• Last and most important, it can replace IEnumerable anywhere.

References

https://blogs.msdn.microsoft.com/charlie/2007/12/10/linq-and-deferred-execution/