IEnumerable
VS IQueryable
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 fetures and take the advantage
of both the fetures to boost your LINQ Query performance.
IEnumerable
1.
IEnumerable exists in
System.Collections Namespace.
2.
IEnumerable can move
forward only over a collection, it can’t move backward and between the items.
3.
IEnumerable is best to
query data from in-memory collections like List, Array etc.
4.
While query data from
database, IEnumerable execute select query on server side, load data in-memory
on client side and then filter data.
5.
IEnumerable is
suitable for LINQ to Object and LINQ to XML queries.
6.
IEnumerable supports
deferred execution.
7.
IEnumerable doesn’t
supports custom query.
8.
IEnumerable doesn’t
support lazy loading. Hence not suitable for paging like scenarios.
9.
Extension methods
supports by IEnumerable takes functional objects.
IEnumerable Example
1.
MyDataContext dc = new MyDataContext ();
2.
IEnumerable<Employee> list = dc.Employees.Where(p => p.Name.StartsWith("S"));
3.
list = list.Take<Employee>(10);
Generated SQL statements of above query will be :
1.
SELECT [t0].[EmpID], [t0].[EmpName], [t0].[Salary] FROM [Employee]
AS [t0]
2.
WHERE [t0].[EmpName] LIKE @p0
Notice that in this query "top
10" is missing since IEnumerable filters records on client side
IQueryable
1.
IQueryable exists in
System.Linq Namespace.
2.
IQueryable can move
forward only over a collection, it can’t move backward and between the items.
3.
IQueryable is best to
query data from out-memory (like remote database, service) collections.
4.
While query data from
database, IQueryable execute select query on server side with all filters.
5.
IQueryable is suitable
for LINQ to SQL queries.
6.
IQueryable supports
deferred execution.
7.
IQueryable supports
custom query using CreateQuery and Execute methods.
8.
IQueryable support
lazy loading. Hence it is suitable for paging like scenarios.
9.
Extension methods
supports by IEnumerable takes expression objects means expression tree.
IQueryable Example
1.
MyDataContext dc = new MyDataContext ();
2.
IQueryable<Employee> list = dc.Employees.Where(p => p.Name.StartsWith("S"));
3.
list = list.Take<Employee>(10);
Generated SQL statements of above query will be :
1.
SELECT TOP 10 [t0].[EmpID], [t0].[EmpName], [t0].[Salary] FROM [Employee]
AS [t0]
2.
WHERE [t0].[EmpName] LIKE @p0
Notice that in this query "top
10" is exist since IQueryable executes query in SQL server with
all filters.
