IEnumerable Vs IQueryable

When working with a database, sometimes the situation may become confusing. Some questions may develop such as what we should use to fetch data from SQL Server, should I go with IEnumerable or should I go with IQueryable?

So here I am trying to show the difference between these two and you can use them depending on your requirements.

IEnumerable

1. System.Collection namespace
2. No Base Interface
3. Supports Deferred Execution
4. No Lazy Loading
5. No Custom Query support
6. Suitable for LINQ to Object and LINQ to XML Queries
7. When querying data from a database IEnumerable executes a SELECT query on the server side. It loads the data in-memory on the client side and does a filter.

Now we will see how IEnumerable works in real life.

I have a Table Employee in my database as in the following:


Figure 1: Employee table

Now I created a new application and right-click on the solution then select Add New Item -> LINQ To SQL Classes -> Employee.dbml.

Now write your query to select records from the Employee Table as in the following:


Figure 2: Selecting records from Employee table

EmployeeDataContext dc = new EmployeeDataContext();
IEnumerable<Employee> list = dc.Employees.Where(p => p.Name.StartsWith("S"));
list = list.Take<Employee>(10);

Now debug and you will see the following:


Figure 3: After debugging

The following is the SQL Query corresponding to the preceding statement:

SELECT [t0].[ID], [t0].[Name], [t0].[Email], [t0].[Country]
FROM [dbo].[Employee] AS [t0]
WHERE [t0].[Name] LIKE @p0

There is no Select TOP statement. So the filter will be applied on the client side after getting all the records from the database.

IQueryable

1. System.Linq namespace.
2. Best to query data from Out-Memory.
3. When querying data from a database IQueryable executes a SELECT Query on the server side with all filters.
4. Suitable for LINQ To SQL Queries.
5. Supports Deferred Execution.
6. Supports custom query using Create Query and Execute methods.
7. Supports Lazy Loading.

Now we will see it programmatically as in the following:


Figure 4: Code Snippet

EmployeeDataContext dc = new EmployeeDataContext();
IQueryable<Employee> list = dc.Employees.Where(p => p.Name.StartsWith("S"));
list = list.Take<Employee>(10

Now debug the query:


Figure 5: After debugging

SELECT TOP (10) [t0].[ID], [t0].[Name], [t0].[Email], [t0].[Country]
FROM [dbo].[Employee] AS [t0]
WHERE [t0].[Name] LIKE @p0

So now you can see the difference between these 2 and use it depending on your business needs.


Image 6.