(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.)
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.
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
