Friday, February 1, 2013

IQueryable and IEnumerable



There is already a post regarding the differences of “IQueryable” and “IEnumerable” published.
Please consider below sample code to understand the difference and usuage of IQueryable and IEnumerable.
//finding the user based on user id (1), using iqueryable.
                user=db.Users.AsQueryable().Where(u => u.UserID == 1).SingleOrDefault();

//finding the user based on user id (1), using ienumerable.
                user = db.Users.AsEnumerable().SingleOrDefault(u =>
                    {
                       return u.UserID == 1;
                    });
Using “IQueryable “, it is the role of the dataprovider (sql or oracle or any db source) to apply the filters or ordering data etc…
“IQueryable” inturn implements “IEnumerable”, to iterate or fetch the data after running the iqueryable expression and store in local memory.
So “IEnumerable” is not playing a role of applying the filters, but helps to read the data executing the query expression. Unless, enumerator of “IQueryable” is invoked the expression will not be executed.
In case of “IEnumerable” with out “IQueryable”, any filters which are applied like (where, count etc…) or any other predicates of linq are applied in the local memory.

Observe below intellitrace screen shots to understand better.

IQueryable – debug intellitrace
(sql Where clause is applied while executing the sql query (sql query build from linq expression using sql provider).




IEnumerable – debug intellitrace
Normal sql with out where clause, but the filtering is applied in memory after fetching the records.