Thursday, September 12, 2013

IEnumerable<T> Vs. IQueryable<T>

I am sure most of you have seen following interfaces when you are writing some code with .NET Framework.
Have you ever wondered what these really are. Let’s see the beauty of these items and specially let’s have a dig in to 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, 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 some 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 have a compare and contrast on IEnumerable<T> and IQueryable<T>.

IEnumerable<T> Vs. IQueryable<T>

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

image
Sample Data
Now I have added a 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]
Basically it’s 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.

Another similar example would be while doing a projection. When using IEnumerable<T> it will run the query to select all the properties while IQueryable<T> will only run the query selecting the necessary properties to do the projection.

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 separate a post).

So what happens here is, the instruction set will get embed as a Expression and passes 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.

You can find the sample code here.


Happy Coding.

Regards,
Jaliya

1 comment:

  1. Good post, I've an idea that IQueryable is an expression. A wise way to return 'var' types. :)

    And in your sample if I write something like this
    var employeeList = oDemoDBEntities.Employees.Where(e => e.LastName == "Smith");
    employeeList = employeeList.Take(1);

    What would be the type of the employeeList ? IEnumerable or IQueryable

    ReplyDelete