List<T> Vs. IEnumerable<T> Vs. IQueryable<T> in Data Retrieval

In this article, let’s see a very important difference between List<T> and IEnumerable<T> and IQueryable<T> in Data Retrieval.

Basically, you must already know that List<T> is a class and IEnumerable<T> and IQueryable<T> are interfaces. List<T> implements IEnumerable<T> along with some other interfaces, but it doesn’t implement IQueryable<T>. IQueryable<T> implements IEnumerable<T>.

Let’s consider the following code sample. Please note that here we are using EF and SQL Server Data Source (AdventureWorks sample database).

using (AdventureWorks2014 context = new AdventureWorks2014())

{

    List<Employee> lMales = context.Employees.Where(c => c.Gender == "M").ToList();

}

When you run this, a SELECT query (with a WHERE condition of course) will get executed on the underlined database.

Now if we take the IEnumerable<T> and IQueryable<T> version, it’s as follows.

using (AdventureWorks2014 context = new AdventureWorks2014())

{

    IEnumerable<Employee> eMales = context.Employees.Where(c => c.Gender == "M").AsEnumerable();

    IQueryable<Employee> qMales = context.Employees.Where(c => c.Gender == "M");

}

Here when you run this, still no query will get executed against the database and that’s because IEnumerable<T> and IQueryable<T> has deferred execution. You need to either iterate through or do a ToList().

Let’s consider the following scenario. Let’s add another Where on eMales which is of type IEnumerable<T> and take it into a List<T> and see how the underlined query looks like:

List<Employee> maleSingles = eMales.Where(c => c.MaritalStatus == "S").ToList();

SELECT 

    [Extent1].[BusinessEntityID] AS [BusinessEntityID], 

    [Extent1].[NationalIDNumber] AS [NationalIDNumber], 

    [Extent1].[LoginID] AS [LoginID], 

    [Extent1].[OrganizationLevel] AS [OrganizationLevel], 

    [Extent1].[JobTitle] AS [JobTitle], 

    [Extent1].[BirthDate] AS [BirthDate], 

    [Extent1].[MaritalStatus] AS [MaritalStatus], 

    [Extent1].[Gender] AS [Gender], 

    [Extent1].[HireDate] AS [HireDate], 

    [Extent1].[SalariedFlag] AS [SalariedFlag], 

    [Extent1].[VacationHours] AS [VacationHours], 

    [Extent1].[SickLeaveHours] AS [SickLeaveHours], 

    [Extent1].[CurrentFlag] AS [CurrentFlag], 

    [Extent1].[rowguid] AS [rowguid], 

    [Extent1].[ModifiedDate] AS [ModifiedDate]

FROM [HumanResources].[Employee] AS [Extent1]

WHERE N'M' = [Extent1].[Gender]

You can see the WHERE condition has only a Gender related condition but nothing on MaritalStatus. So what’s happening here is it will first select the males into the memory and then select singles from there. Not so efficient.

Now let’s add the Where on qMales which is of type IQueryable<T> and take it into a List<T> and see how the query looks.

List<Employee> maleSingles = qMales.Where(c => c.MaritalStatus == "S").ToList();

SELECT 

    [Extent1].[BusinessEntityID] AS [BusinessEntityID], 

    [Extent1].[NationalIDNumber] AS [NationalIDNumber], 

    [Extent1].[LoginID] AS [LoginID], 

    [Extent1].[OrganizationLevel] AS [OrganizationLevel], 

    [Extent1].[JobTitle] AS [JobTitle], 

    [Extent1].[BirthDate] AS [BirthDate], 

    [Extent1].[MaritalStatus] AS [MaritalStatus], 

    [Extent1].[Gender] AS [Gender], 

    [Extent1].[HireDate] AS [HireDate], 

    [Extent1].[SalariedFlag] AS [SalariedFlag], 

    [Extent1].[VacationHours] AS [VacationHours], 

    [Extent1].[SickLeaveHours] AS [SickLeaveHours], 

    [Extent1].[CurrentFlag] AS [CurrentFlag], 

    [Extent1].[rowguid] AS [rowguid], 

    [Extent1].[ModifiedDate] AS [ModifiedDate]

FROM [HumanResources].[Employee] AS [Extent1]

WHERE (N'M' = [Extent1].[Gender]) AND (N'S' = [Extent1].[MaritalStatus])

And here you can see that the WHERE condition contains both Gender and MaritalStatus.

And if we go further on IQueryable<T> for something like below:

List<Employee> maleSingleAccountants = qMales

    .Where(c => c.MaritalStatus == "S")

    .Select(e => new

    {

        NationalIDNumber = e.NationalIDNumber,

        JobTitle = e.JobTitle,

    })

    .Where(e => e.JobTitle == "Accountant").ToList();

And following is the underlined query.

SELECT 

    1 AS [C1], 

    [Extent1].[NationalIDNumber] AS [NationalIDNumber], 

    [Extent1].[JobTitle] AS [JobTitle]

FROM [HumanResources].[Employee] AS [Extent1]

WHERE (N'M' = [Extent1].[Gender]) AND (N'S' = [Extent1].[MaritalStatus]) AND (N'Accountant' = [Extent1].[JobTitle])

If we did this on eMales which is of type IEnumerable<T>, it will be selecting all males with their all properties (like last IEnumerable<T> example) from the database, and do the rest in memory. But here IQueryable<T> is smart enough to combine all the Wheres and only Select what is projected.

Isn’t that great? With the right use of these, you can make your code more efficient.

Happy Coding.