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

(Couple of years back I wrote this post about IEnumerable<T> Vs. IQueryable<T> and I suggest you reading that as well as I will not be explaining what I written there over in this post.)

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

Basically I am sure you already know 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 I am 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 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 that on there WHERE condition has only Gender related condition but nothing on MaritalStatus. So here what’s happening 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 like.

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 more 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 the 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. I am sure with right use of these, you can make your code more efficient.

So hope this helps.

Happy Coding.

Regards,

Jaliya