While working with LINQ, you must have wondered what the difference between the IQueryable and IEnumerable is.

The primary difference is that the extension methods defined for IQueryable take Expression objects instead of Functional objects, meaning the delegate it receives is an expression tree instead of a method to invoke.

IQueryable is inherited from IEnumerable.

public interface IQueryable : IEnumerable, IQueryable, IEnumerable
{

}

IEnumerable is great for working with in-memory collections, but IQueryable provides all the functionality provided by it’s base class and also allows for a remote data source, like a database or web service.

IQueryable provides deferred execution of queries.

Here is a small example to demonstrate that.
I have added 2 gridview controls to display the results fetched from the database.
Used adventureworks database to query the data.

C# Designer:

<asp:Panel ID="pnlList" runat="server" GroupingText="IQueryable vs IEnumerable">
        <table>
        <tr>
            <td valign="top">Value1: <asp:GridView ID="dbList1" runat="server"/></td>
            <td valign="top">Value2: <asp:GridView ID="dbList2" runat="server"/></td>
        </tr>
        </table>
    </asp:Panel>

C# Code:

using (AdWorksDataContext awdc = new AdWorksDataContext(@"server=.\sqlexpress;database=AdventureWorks;Integrated Security=True"))
            {
                IEnumerable<Employee> query1 = from emp in awdc.Employees
                                                      select emp;

                IQueryable<EmployeeAddress> query2 = from empAddress in awdc.EmployeeAddresses
                                                      select empAddress;

                dbList1.DataSource = query1.Take(10);
                dbList1.DataBind();

                dbList2.DataSource = query2.Take(10);
                dbList2.DataBind();
            }

To generate the necessary classes from the database, I added a “LINQ to SQL classes” template to the project and added the necessary tables to it.

Two queries are built to fetch the data from tables “Employee” and “EmployeeAddress”.

The first query “query1” fetches the data from the “Employees” table and applies the extension method “Take”.

The second query “query2” fetches the data from the “EmployeeAddress” table and applies the extension method “Take”.

While looking at the underlying SQL’s generated for the above LINQ queries, here is what I found.

SQL Code:

For the LINQ statement using IEnumerable,

IEnumerable<Employee> query1 = from emp in awdc.Employees                                                 select emp;

dbList1.DataSource = query1.Take(10);
dbList1.DataBind();

The following SQL is generated, which fetches the whole employee table into the memory and then applies the extension method “Take”.

SELECT [t0].[EmployeeID], [t0].[NationalIDNumber], [t0].[ContactID], [t0].[LoginID], [t0].[ManagerID], [t0].[Title], [t0].[BirthDate], [t0].[MaritalStatus], [t0].[Gender], [t0].[HireDate], [t0].[SalariedFlag], [t0].[VacationHours], [t0].[SickLeaveHours], [t0].[CurrentFlag], [t0].[rowguid], [t0].[ModifiedDate]
FROM [HumanResources].[Employee] AS [t0]

For the LINQ statement using IQueryable,

IQueryable<EmployeeAddress> query2 = from empAddress in awdc.EmployeeAddresses
                                                      select empAddress;

dbList2.DataSource = query2.Take(10);
dbList2.DataBind();

The following SQL is generated, which fetchs only the top 10 records from the table “EmployeeAddress”

SELECT TOP (10) [t0].[EmployeeID], [t0].[AddressID], [t0].[rowguid], [t0].[ModifiedDate]
FROM [HumanResources].[EmployeeAddress] AS [t0]

Conclusion:

From the above example it is clear that IQueryable has quite some advantages over IEnumerable while using LINQ to SQL.