IQueryable Vs. IEnumerable in terms of LINQ to SQL queries

Few days ago I was working on my project which is having Linq to Sql as database layer. I got requirement to get the first of all employee who’s designation starts with “soft”.

I get fired below query and got the result in IEnumerable varible

IEnumerable<employee> emp = 
         dc.Employees.Where(x => x.Desc.StartsWith("soft"));
emp = emp.Take(1);

But later on I found there it’s taking too much time to get the count.

So to try something else I use IQueryable to store the result and to get the count.

IQueryable<employee> emplist = 
         dc.Employees.Where(x => x.Desc.StartsWith("soft"));
emplist = emplist.Take(1);

After using IQueryable I found I get result faster than previous one.

So to find out this I used SQL Profile to find out the SQL Query fired by the code.

First block of code  fire following query i.e which use IEnumrable to store output of the LINQ query

SELECT [t0].[Id], [t0].[Name], [t0].[Address], [t0].[Desc] AS [Desc]
FROM [dbo].[Employee] AS [t0]
WHERE [t0].[Desc] LIKE @p0

Second block of code  fire following query i.e which use IQuerable to store output of the LINQ query

SELECT TOP (1) [t0].[Id], [t0].[Name], [t0].[Address], [t0].[Desc] AS [Desc]
FROM [dbo].[Employee] AS [t0]
WHERE [t0].[Desc] LIKE @p0

Major difference between both query is First one doesn’t contain the TOP clause to get the first record but second one make use of the TOP to get the first record.

When I explore further I fond following difference between both ?

The major difference is that IEnumerable will enumerate all elements, while IQueryable will enumerate elements, or even do other things, based on a query. In case of IQueryable Linq Query get used by IQueryProvider  which must interpret or compiled in order to get the result.
i.e Extension methods defined for IQueryable take Expression objects instead of Func objects (which is what IEnumerable uses)., meaning the delegate it receives is an expression tree instead of a method to invoke.

IEnumerable is great for working with in-memory collections, but IQueryable allows for a remote data source, like a database or web service.

Pranay Rana

Pranay is MVB on DZone and working as a Senior Software engineer. Doing Web development using Asp.Net ,C#, MS sql server, JQuery/Javascript that he had experience of 4.3 years now. For himdef. of programming is : Programming is something that you do once and that get used by multiple for many years You can visit him on his blog - http://pranayamr.blogspot.com/ Follow on twitter : @pranayamr