IEnumerable<T> Vs. IQueryable<T>

IEnumerable<T> Vs. IQueryable<T>

Introduction

Probably most of you have seen the following interfaces when you write code with .NET Framework.

Have you ever wondered what these really are. Let’s see the beauty of them and let’s have a dig into IEnumerable<T> and IQueryable<T>.

First let’s see how MSDN describes these four interfaces.

IEnumerable

Exposes an enumerator, which supports a simple iteration over a non-generic collection.

public interface IEnumerable

IEnumerable<T>

Exposes the enumerator, which supports a simple iteration over a collection of a specified type.

public interface IEnumerable<out T> : IEnumerable

IQueryable

Provides functionality to evaluate queries against a specific data source wherein the type of the data is not specified.

public interface IQueryable : IEnumerable
{
 Type ElementType { get; }
 Expression Expression { get; }
 IQueryProvider Provider { get; }
}

IQueryable<T>

Provides functionality to evaluate queries against a specific data source wherein the type of the data is known.

public interface IQueryable<out T> : IEnumerable<T>,
 IQueryable, IEnumerable
{
 Type ElementType { get; }
 Expression Expression { get; }
 IQueryProvider Provider { get; }
}

So it seems mainly what is common about all four of these is that they are iterators. Which means we can traverse through the items. If we know the type of the collection that we are going to iterate (a generic type), we can use IEnumerable<T> or IQueryable<T>, and if we don’t know the type (a non-generic type) we can use IEnumerable or IQueryable.

As you can see IQueryable and IQueryable<T> contains three additional properties which are not present in IEnumerable and IEnumerable<T>.

Assuming that we know the type (which means we can use either IEnumerable<T> or IQueryable<T>), let’s compare IEnumerable<T> and IQueryable<T>.

Case Study: IEnumerable<T> Vs. IQueryable<T>

For better understanding let's go with an example here. We have a very simple database which is called “DemoDB” and it contains a single table “Employee”. “Employee” table consists of three columns which are “EmployeeId”, ”FirstName” and “LastName”. We have the following values in it.


sample data

Now I have added an ADO.NET Entity Data Model to my application and selected Generate from database and selected my “DemoDB”. Entity framework will create all the necessary classes to query my database.

I have following two helper methods to query data with the condition Employees with their Last Name “Smith”, one for IEnumerable<T> and the other for IQueryable<T>. I am calling them from my Main method.

static void CallWithIEnumerable(DemoDBEntities oDemoDBEntities)
{
 IEnumerable<Employee> employeeList = 
 oDemoDBEntities.Employees.Where(e => e.LastName == "Smith");
 
 foreach (Employee employee in employeeList)
 {
 Console.WriteLine(employee.FirstName);
 }
}
 
static void CallWithIQueryable(DemoDBEntities oDemoDBEntities)
{
 IQueryable<Employee> employeeList = 
 oDemoDBEntities.Employees.Where(e => e.LastName == "Smith");
 
 foreach (Employee employee in employeeList)
 {
 Console.WriteLine(employee.FirstName);
 }
}

Now if we examine the SQL queries generated for above two scenarios we are getting the following.

--IEnumerable
SELECT 
[Extent1].[EmployeeId] AS [EmployeeId], 
[Extent1].[FirstName] AS [FirstName], 
[Extent1].[LastName] AS [LastName]
FROM [dbo].[Employee] AS [Extent1]
WHERE 'Smith' = [Extent1].[LastName]
 
--IQueryable
SELECT 
[Extent1].[EmployeeId] AS [EmployeeId], 
[Extent1].[FirstName] AS [FirstName], 
[Extent1].[LastName] AS [LastName]
FROM [dbo].[Employee] AS [Extent1]
WHERE 'Smith' = [Extent1].[LastName]

They are the same. Now let’s modify our helper methods to get TOP 1 record.

static void CallWithIEnumerable(DemoDBEntities oDemoDBEntities)
{
 IEnumerable<Employee> employeeList = 
 oDemoDBEntities.Employees.Where(e => e.LastName == "Smith");
 employeeList = employeeList.Take(1);
 
 foreach (Employee employee in employeeList)
 {
 Console.WriteLine(employee.FirstName);
 }
}
 
static void CallWithIQueryable(DemoDBEntities oDemoDBEntities)
{
 IQueryable<Employee> employeeList = 
 oDemoDBEntities.Employees.Where(e => e.LastName == "Smith");
 employeeList = employeeList.Take(1);
 
 foreach (Employee employee in employeeList)
 {
 Console.WriteLine(employee.FirstName);
 }
}

And if we examine the queries now we are getting the following:

--IEnumerable with top
SELECT 
[Extent1].[EmployeeId] AS [EmployeeId], 
[Extent1].[FirstName] AS [FirstName], 
[Extent1].[LastName] AS [LastName]
FROM [dbo].[Employee] AS [Extent1]
WHERE 'Smith' = [Extent1].[LastName]
 
--IQueryable with top
SELECT TOP (1) 
[Extent1].[EmployeeId] AS [EmployeeId], 
[Extent1].[FirstName] AS [FirstName], 
[Extent1].[LastName] AS [LastName]
FROM [dbo].[Employee] AS [Extent1]
WHERE 'Smith' = [Extent1].[LastName]

For IEnumerable<T>, it was the same query as the previous, but for the IQueryable<T>, the query has changed amazingly to get TOP 1 directly.

So now we know something additional is happening with IQueryable<T> and let’s find out what.

Now comes the property Expression of IQueryable<T> to the scene. The Expression property gets the expression tree that is associated with the instance. In simple Expression trees represent code in a tree-like data structure (I am not going to go further with Expression trees here, may be it can be a separate post).

So what happens here is, the instruction set will get embedded as a Expression and passed to the compiler. The compiler analyses the instructions (in here it is a SQL Query) and selects the best way to execute it.

Now comes the question when to use IEnumerable<T> or IQueryable<T>.

  • If you want to select some data in the same process use IEnumerable<T>.
  • If you want to contact some other process to select some data (in here, it’s the SQL Server), use IQueryable<T> and let compiler decide the best way.

So that's it. Appreciate your feedback. And please correct me if I am wrong here.

References & Resources

Appendices


Appendix A: Simplified the Enumerable class In Linq

// Type: System.Linq.Enumerable
// Assembly: System.Core, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
// Assembly location: C:\Windows\Microsoft.NET\Framework\v4.0.30319\System.Core.dll
public static class Enumerable
{
    public static IEnumerable<TSource> Where<TSource>(
        this IEnumerable<TSource> source,
        Func<TSource, bool> predicate)
    {
        return (IEnumerable<TSource>) new Enumerable.WhereEnumerableIterator<TSource>(source, predicate);
    }
}

Appendix B: Simplified the Queryable class In Linq

// Type: System.Linq.Queryable
// Assembly: System.Core, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
// Assembly location: C:\Windows\Microsoft.NET\Framework\v4.0.30319\System.Core.dll
public static class Queryable
{
    public static IQueryable<TSource> Where<TSource>(
        this IQueryable<TSource> source,
        Expression<Func<TSource, bool>> predicate)
    {
        return source.Provider.CreateQuery<TSource>(
            Expression.Call(
                null,
                ((MethodInfo) MethodBase.GetCurrentMethod()).MakeGenericMethod(
                    new Type[] { typeof(TSource) }),
                    new Expression[] { source.Expression, Expression.Quote(predicate) }));
    }
}


Sort by: Published Date | Most Recent | Most Useful
Comments
  • Interesting article but I think needs more explanations and more examples.

  • BTW, I like your articles and your style - easy to follow