All About IEnumerable and IQueryable

• Author
• Recent Posts

Atul Sharma

An extrovert by nature, Atul has been sharing his opinions and views on various social network platforms. He always had been thinking about having his own platform and here it is. Creative and tech-savvy Atul has a good sense of humor and when he is not programming or writing blogs or traveling, you can find him cooking, Netflixing or sleeping.

Latest posts by Atul Sharma (see all)

• RISE FROM THE ASHES – Sanjib Nandi - November 1, 2021
• साथ में मेरे दोस्त खड़े थे - August 1, 2021
• BenchmarkDotNet: Advanced Features - June 20, 2021

Download the source code from github and experiment.

Introduction

When we talk about LINQ, we consider two collections IEnumerable and IQueryable. In this article, we will know all about IEnumerable and IQueryable except how they are evaluated. Possibly i will cover that in next article to strengthen our basic understanding.

Code Example

In this example, We are going to fetch the top 1 name, who works with Project “TAAGUNG”. Table structure and data is as follow –

For this we will be using same query with different return type 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, 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 2 steps 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.

Moreover, We can see above, 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 scenario. So Please notice the different way of writing the queries in both of the scenarios. i.e. everything is mentioned in the 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 on a collection
• They both support deferred execution.
• Whatever IEnumerable can do, IQueryable can also do but NOT the other way around.

Differences

What to use when?

IEnumerable

• If data is available in the same process i.e. In Memory collection e.g. Array, ArrayList etc.
• Linq to Object and Linq to XML.
• it is not suitable for paging like scenario.

IQueryable

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

References

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

I hope I could clear the basic concepts of these two. Source code is available here to experiment.