IEnumerable<T>,IQueryable<T>, ICollection<T> & IList<T>: What is the difference and Where to use what

Lot has been said on this topic and today I am going to put my two cents on this one.

.NET Framework is full of such interfaces and often I have seen people using these interfaces without paying attention to what they do and where to use what .

So let’s get started with IQueryable<T> and IEnumerable<T> and later on we will throw in IList<T> and ICOllection<T> as well.

IQueryable<T> vs IEnumerable<T>

First of all IQueryable<T> and IEnumerable<T> are just generic versions of IEnumerable and IQueryable.The latter were present in .NET 1.1 and former got introduced in .NET 2.0 when generics came into picture.

To clearly outline the difference between the two let me just show one code sample of using IQueryable and IEnumerable and how the queries get executed on the db for both of them. For the purpose of this demo I have created one simple application which connects to a table in DB using Linq to SQL.

Also I am using SQL Profiler to see what queries are getting executed on db when you access IEnumerable and IQueryable.

Consider the code snippet shown below:

IEnumerable<Employee> profileEnumerable =
dc.Employees.AsEnumerable().Where(x => x.MiddleName== "Riggs");

Console.WriteLine("Enumerable");
foreach (var employeeProfile in profileEnumerable)
{
     Console.WriteLine(employeeProfile.FirstName);
}

Here if you notice I am converting Linq.Table<Employee> type to IEnumerable<Employee> using AsEnumerable() and then running filter condition using Where.

Using Sql Profiler we see that following query is executed in database when foreach loop is executed for the first time.

SELECT [t0].[EmployeeId], [t0].[FirstName], [t0].[EmployeeAddress],
[t0].[Manager], [t0].[DepartmentId], [t0].[MiddleName],
[t0].[LastName], [t0].[Age], [t0].[YearInService]
FROM [dbo].[Employee] AS [t0]

What this indicates is that first all the employee data is fetched from db and further filtering happens in memory.

Now lets see what happens when we use IQueryable.Below is the code snippet.Notice in the code that this time I am converting Linq.Table<Employee> type to IQueryable<Employee> using AsQueryable.

IQueryable<Employee> profileQueryable = dc.Employees.AsQueryable()
.Where(x => x.MiddleName == "Riggs");
Console.WriteLine("Queryable");
foreach (var employeeProfile in profileQueryable)
{
  Console.WriteLine(employeeProfile.FirstName);
}

Checking this in Sql profiler following query is executed in database.

exec sp_executesql N'SELECT [t0].[EmployeeId], [t0].[FirstName], [t0].[EmployeeAddress],
 [t0].[Manager], [t0].[DepartmentId], [t0].[MiddleName], [t0].[LastName], [t0].[Age], [t0].[YearInService]
FROM [dbo].[Employee] AS [t0]
WHERE [t0].[MiddleName] = @p0',N'@p0 varchar(8000)',@p0='Riggs'

Well its a dynamic sql but more importantly if you notice it has a where clause with condition which we specified in the code.So whenever you use IQueryable the whole query along with filter condition gets executed on the data source (which in this case is Sql Server) and only the final results are returned.

Under the hood…

So the question is how and why does IQueryable and IEnumerable behave like this.Answer is subtle but see if you can spot the difference between the method signature of Where extension method for IEnumerable and IQueryable below.

IEnumerable<T>

IQueryable<T>

Well what I am referring to is the first parameter to where in the above definitions.Where  or any other extension method for IEnumerable takes Func delegate(Func<Employee,bool> above) where as IQueryable extension methods take Expression (Expression<Func<Employee,bool>> above).

When compiled, Func  gets converted into code i.e. it would be converted into a normal method whereas Expression will be converted into an expression tree of objects (but will not be executed).Implication of this fact is that func call will be executed immediately but expression tree would be provided to  a query provider and will be converted into a data source query which will be executed in the data source.This is called deferred execution.In our case data source is SQL Server but it can be anything.Thats how LinqToSql or Linq to Anything else works.

IList<T> and ICollection<T>

ICollection<T> is a generic interface from which all generic collection based classes & interfaces are derived e.g. IList<T>,Stack<T>,Queue<T> etc.It inherits IEnumerable<T> and IEnumerable.ICollection<T> provides collection based functionality like adding ,removing and clearing the collection.

IList<T> inherits from ICollection<T> and add functionality for indexed based access (like Insert,InsertAt,RemoveAt etc) which we usually use in all the list based classes e.g. Collection<T>,List<T> etc.

Where to use what

• IEnumerable<T> : IEnumerable is best where you want to query in memory collections.It is suitable for LinqToObject and LinqToXml queries.
• IQueryable<T>: Best for querying data from external data sources especially which have some kind of query languages defined like databases (Sql queries )or sharepoint(CAML query).In such cases expression trees can be converted to data source specific queries which can be executed on remote data sources along with all the filters.For this reason in such situations performance of IQueryable is better than IEnumerable.
• ICollection<T> : Best when you only need collection based functionality like iteration ,adding ,removing etc and don’t need querying functionality
• IList<T> : Best when along with iteration you need indexed based access as well.

Also IEnumerable and IQueryable always hit the data source whenever you access them.That is if you are accessing SQL database, on each access of IEnumerable and IQueryable database will be accessed .Use this when data can change and you want fresh data always to be queried from data source.But incase you don’t want to go to database everytime make sure you convert the IEnumerable or IQueryable to a IList as shown below and do further operation on IList.

IList<EmployeeProfile> profileEnumerable =
 dc.EmployeeProfiles.AsEnumerable().
Where(x => x.EmployeeJobSector == "In Consulting")
.ToList();

Hope this post helped in clearing some confusion associated with these interfaces.For more insights into related C# fundamentals you can refer these two courses – one on C# collections and another one on LINQ fundamentals.