A few days ago, I was working on a project which has LINQ to SQL as the database layer. I got a requirement to get the first name of all employees whose designation starts with "soft".

I fired the below query and got the result in an IEnumerable variable.

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

But later on, I found it was taking too much time to get the count.

So to try something else, I uses an 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 that I got the result faster than the last time.

To find out what was causing this, I used SQL Profile to find out the SQL query fired by the code.

The first block of code fired the following query, i.e., the one which uses IEnumrable to store the 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

The second block of code fired the following query, i.e., which uses IQuerable to store the 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

The major difference between the queries is the first one doesn't contain the TOP clause to get the first record but the second one makes use of TOP to get the first record.

When I explored further I found following difference between them

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 the case of the IQueryable, the LINQ query gets used by IQueryProvider which must be interpreted or compiled in order to get the result. I.e., the 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<t> allows for a remote data source, like a database or web service.