Saturday, 18 October 2014

IEnumerable vs IQueryable

Many  developers gets confused between IEnumerable and IQueryable. When it comes to writing code, both looks very similar. However there are many difference between them which needs to be taken care of while writing code. Both have some intended usability scenarios for which they are made.

The first important point to remember is “IQueryable” interface inherits from “IEnumerable”, so whatever “IEnumerable” can do, “IQueryable” can also do.

There are many differences but let us discuss about the one big difference which makes the biggest difference. “IQueryable” interface is useful when your collection is loaded using LINQ or Entity framework and you want to apply filter on the collection.
Consider the below simple code which uses “IEnumerable” with entity framework. It’s using a “where” filter to get records whose “EmpId” is “2”.

IEnumerable<Employee> emp = ent.Employees;

IEnumerable<Employee> temp = emp.Where(x => x.Empid == 2).ToList<Employee>();

This where filter is executed on the client side where the “IEnumerable” code is. In other words, all the data is fetched from the database and then at the client it scans and gets the record with “EmpId” is “2”.

But now see the below code we have changed “IEnumerable” to “IQueryable”.

IQueryable<Employee> emp = ent.Employees;

IEnumerable<Employee> temp = emp.Where(x => x.Empid == 2).ToList<Employee>();

In this case, the filter is applied on the database using the “SQL” query. So the client sends a request and on the server side, a select query is fired on the database and only necessary data is returned.

So the difference between “IQueryable” and “IEnumerable” is about where the filter logic is executed. One executes on the client side and the other executes on the database.
So if you are working with only in-memory data collection “IEnumerable” is a good choice but if you want to query data collection which is connected with database, “IQueryable” is a better choice as it reduces network traffic and uses the power of SQL language.

Below lists the differences between them based on their properties :
IEnumerable IQueryable
NamespaceSystem.Collections NamespaceSystem.Linq Namespace
Derives fromNo base interfaceDerives from IEnumerable
Deferred ExecutionSupportedSupported
Lazy LoadingNot SupportedSupported
How does it workWhile 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 forLINQ to Object and LINQ to XML queries.LINQ to SQL queries.
Custom QueryDoesn’t supports.Supports using CreateQuery and
Execute methods.
Extension mehtod
Extension methods supported in IEnumerable takes functional objects.Extension methods supported in
IEnumerable takes expression objects
i.e. expression tree.
When to usewhen querying data from in-memory collections like List, Array etc.when querying data from out-memory
(like remote database, service)
Best UsesIn-memory traversalPaging


Post a Comment


ADO .Net (2) Ajax (1) Angular Js (17) Angular2 (24) ASP .Net (14) Azure (1) Breeze.js (1) C# (49) CloudComputing (1) CMS (1) CSS (2) Design_Pattern (3) DI (3) Dotnet (21) Entity Framework (3) ExpressJS (4) Html (3) IIS (1) Javascript (6) Jquery (9) Lamda (3) Linq (11) Mongodb (1) MVC (48) NodeJS (7) RDLC (1) Report (1) Sql Server (29) SSIS (3) SSRS (2) UI (1) WCF (12) Web Api (10) Web Service (1) XMl (1)