In this video
we will try to demonstrate
the difference between IEnumerable
and IQueryable.
Both these interfaces are for .NET collections.
Incase you are new to .NET collections
watch
the collections
videos where we have 5-6 videos
In one of the video we have already discussed IEnumerable Interface.
In this video
we will concentrate on
the difference between IEnumerable and IQueryable.
Putting down two important points
The first point is
both IEnumerable & IQueryable are interfaces to .NET Collections.
The second point is which is very important
IQueryable
inherits from IEnumerable.
Whatever things
IEnumerable can do
IQueryable can also do
but
there are somethings which IQueryable can do
but IEnumerable will not be able to do it.
There are lot of differences between both of these interfaces
lot of small differences
In this video will talk about that one big difference which will make a difference
rather than talking about ten differences.
Will talk about the one big difference between both of these interfaces
This will change your thought when to use IEnumerable and when to use IQueryable.
Here we have very simple
Entity Framework code
Incase you are new to Entity Framework see the video of Entity Framework Q&A on questpondvd.com
where we have explained right from scratch to professional level how to use Entity Framework.
At this moment we will concentrate on
pointing out the difference between IEnumerable and IQueryable rather going into Entity Framework.
This Entity Framework code
loads the employees database from here
into
this IEnumerable collection.
In this first line
by using EF
we are loading
the employee table.
In the next line
we are applying a filter here saying
filter Empid
which has value 2.
Put a debug point here and run this code in a debug mode
What kind of SQLs are getting fired on the SQL Sever.
When we apply a filter here
it will fire
SQL at the backend.
At the end of the day it fetch the data from SQL Server.
Will Run the profiler here
Incase you are new to porfiler
watch
the videos on SQL Server Performance Tuning where it explains what is SQL Profiler.
SQL Profiler helps us to understand
how the SQLs are getting fired on SQL Server.
Start the profiler and run it.
The profiler will start running at the background
Clear everything at this moment.
Go back to the code
We are on that code
where the filters are getting applied.
We have IEnumerable collection and on that IEnumerable collection
we are applying a filter
of Empid == 2.
Go back to the profiler
and stop profiling
and see what kind of SQL it has fired.
It is actually fetching all the data from the tbl EmpName table.
Our expectation was
it should say where Empid == 2.
All the data from SQL Server is going
to the Client
to the console application
and then the filter is getting applied on it.
When we use IEnumerable
and we apply a filter on it
the full data go from SQL Server to the client side
then the filter will get applied.
Go back to the code and change IEnumerable
to
IQueryable.
Go back to SQL Profiler
and clear everything.
Start the profiler and fire the filter
Go back to the profiler to see what kind of thing it is doing here.
When we used IQueryable
and fired
the filter on it
it has created SQL accordingly.
Only the necessary data will come from the SQL Server to the client side.
Here it is saying Where Empid = 2.
When we use IEnumerable
and when we apply filter on it
the complete data from SQL Server is sent to the Client side then the filter is applied in memory.
while in IQueryable
the filter gets applied
on the SQL Server or on the RDBMS
and only the necessary data is sent to the client side.
The biggest difference between IQueryable and IEnumerable is all about where the filter logic gets executed.
In case of IEnumerable the filter logic gets executed on the client side
In case of IQueryable the filter logic gets executed on the
Database side by using SQL.
As a practice
if we are working with in memory data collection, in memory data collection means that collection has no connection with RDBMS
then it is better to use IEnumerable.
If we are working with collections which are connected to database
then IQueryable is the better choice as it will reduce network traffic
and utilize the power of SQL.
In this video we were trying to understand
in what scenario we should use IEnumerable and in what scenario we should use IQueryable.
Next time when you are thinking about using IEnumerable and IQueryable
ask one question to yourself
is this collection connected to Database
or is this collection in memory?.