Few days ago I am 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.