In LINQ to query data from database and collections, we use IEnumerable and IQueryable for data manipulation. IEnumerable is inherited by Iqueryable, hence it has all the features of it and except this, it has its own features. Both have its own importance to query data and data manipulation. Let’s see both the features and take the advantage of both the features to boost your LINQ Query performance.
| IEnumerable | IEnumerable | IQueryable |
| Namespace | System.Collections Namespace | System.Linq Namespace |
| Derives from | No base interface | Derives from IEnumerable |
| Deferred Execution | Supported | Supported |
| Lazy Loading | Not Supported |
Supported |
| How does it work | While querying data from database, IEnumerable execute select query on server side, load data in-memory on client side and then filter data. Hence does more work and becomes slow. | While querying data from database, IQueryable execute select query on server side with all filters. Hence does less work and becomes fast. |
| Suitable for | LINQ to Object and LINQ to XML queries. | LINQ to SQL queries. |
| Custom Query | Doesn’t supports. | Supports using CreateQuery and Execute methods. |
| Extension mehtod parameter |
Extension methods supported in IEnumerable takes functional objects. | Extension methods supported in IEnumerable takes expression objects i.e. expression tree. |
| When to use | when querying data from in-memory collections like List, Array etc. | when querying data from out-memory (like remote database, service) collections. |
| Best Uses | In-memory traversal | Paging |
IEnumerable Example
MyDataContext dc = new MyDataContext ();
IEnumerable<Employee> list = dc.Employees.Where(p => p.Name.StartsWith(“S”));
list = list.Take<Employee>(10);
Generated SQL statements of above query will be:
SELECT [t0].[EmpID], [t0].[EmpName], [t0].[Salary] FROM [Employee] AS [t0]
WHERE [t0].[EmpName] LIKE @p0
IQueryable Example
MyDataContext dc = new MyDataContext ();
IQueryable<Employee> list = dc.Employees.Where(p => p.Name.StartsWith(“S”));
list = list.Take<Employee>(10);
Generated SQL statements of above query will be:
SELECT TOP 10 [t0].[EmpID], [t0].[EmpName], [t0].[Salary] FROM [Employee] AS [t0]
WHERE [t0].[EmpName] LIKE @p0
Hope it helps!
really nice explanation.