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
- System.Collection namespace
- No Base Interface
- Supports Deferred Execution
- No Lazy Loading
- No Custom Query support
- Suitable for LINQ to Object and LINQ to XML Queries
- 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:
![Table Employee]()
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:
![Table]()
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
- System.Linq namespace.
- Best to query data from Out-Memory.
- When querying data from a database IQueryable executes a SELECT Query on the server side with all filters.
- Suitable for LINQ To SQL Queries.
- Supports Deferred Execution.
- Supports custom query using Create Query and Execute methods.
- Supports Lazy Loading.
Now we will see it programmatically as in the following:
![see it program]()
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.
![see the difference]()
Image 6.