While working with data base sometime it's a very
confusable situation ie: 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 difference between these
two and as per your requirements you can use these.
IEnumerable:
1.
System.Collection namespace
2.
No Base Interface
3.
Support Deferred Execution
4.
No Lazy Loading
5.
No Custom Query support
6.
Suitable for LINQ to Object & LINQ to
XML Queries
7.
While Query data from database Ienumerable
execute SELECT query on server side – Load data In-Memory on client side and do Filter.
Now we will see how IEnumerable works in real life:
I have a Table Employee in my Data Base:
Image 1.
Now I created a new application and Right Click on
Solution -> Add New Item -> LINQ To SQL Classes -> Employee.dbml
Now write your Query to select records from Employee
Table like below:
Image 2.
EmployeeDataContext dc = new EmployeeDataContext();
IEnumerable<Employee> list = dc.Employees.Where(p
=> p.Name.StartsWith("S"));
list = list.Take<Employee>(10);
Now Debug and
see:
Image 3.
SQL Query
correspondent to above statement is:
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 filter will apply
on client side after getting all records form Data Base.
IQueryable:
1.
System.Linq namespace
2.
Best to query data from Out-Memory
3.
While Query data from Data Base IQueryable
execute SELECT Query on server side with all Filter.
4.
Suitable for LINQ To SQL Queries.
5.
Support Deferred Execution.
6.
Support custom query using Create Query
& Execute methods.
7.
Support Lazy Loading
Now we will see it programmatically:
Image 4.
EmployeeDataContext dc = new EmployeeDataContext();
IQueryable<Employee> list =
dc.Employees.Where(p => p.Name.StartsWith("S"));
list = list.Take<Employee>(10);
Now debug
Query:
Image 5.
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 you can
use it as per your business need.
Image 6.