IEnumerable, IQueryable and the Entity Framework 4.0

by Larry Spencer Monday, June 27, 2011 4:51 PM

There's a lot on the Internet about IEnumerable<T> versus IQueryable<T>, but it can be hard to find the useful information amidst the merely fascinating. So, here's a very practical difference between IEnumerable<T> and IQueryable<T>, illustrated with the Entity Framework 4.0.

In this sample program, you'll notice that we're doing the same thing twice: once with some Entity Framework 4.0 data returned as an IQueryable<T> and again as an IEnumerable<T>. The output is the same, but what's of interest to us is the SQL that's generated.

 

using System;
using System.Collections.Generic;
using System.Linq;

namespace IEnumerableVsIQueryable
{
  class Program
  {
    static void Main(string[] args)
    {
      using (var db = new EFormsEntities())
      {
        Console.WriteLine("As IQueryable...");
        foreach (var evt in GetEventsAsIQueryable(db)
            .Where(e => e.EventID > 2)
            .OrderBy(e => e.EventID)
            .Take(1)) 
        {
            Console.WriteLine("EventID = {0}", evt.EventID);
        }

        Console.WriteLine("As IQueryable...");
        foreach (var evt in GetEventsAsIEnumerable(db)
            .Where(e => e.EventID > 2)
            .OrderBy(e => e.EventID)
            .Take(1))
        {
            Console.WriteLine("EventID = {0}", evt.EventID);
        }
        Console.WriteLine("Press Enter to quit.");
        Console.WriteLine();
      }
    }

    static IQueryable<Event> GetEventsAsIQueryable(EFormsEntities db)
    {
      // Returns the table as an IQueryable since an Entity Framework
      // ObjectSet (Events in this case) *is* an IQueryable.
      return db.Events.Where(e => e.EventID > 1);
    }

    static IEnumerable<Event> GetEventsAsIEnumerable(EFormsEntities db)
    {
      return db.Events.Where(e => e.EventID > 1).AsEnumerable();
    }
}
}

 

In the case of the IQueryable, here's what executes on the SQL server: 

SELECT TOP (10) 
[Extent1].[EventID] AS [EventID], 
[Extent1].[PerformedByUserID] AS [PerformedByUserID], 
[Extent1].[ActionType] AS [ActionType]
FROM [dbo].[Events] AS [Extent1]
WHERE ([Extent1].[EventID] > 1) AND ([Extent1].[EventID] > 2)
ORDER BY [Extent1].[EventID] ASC

You'll see that the Take(10) in our foreach became part of the query, as did the Where(e => e.EventID > 2) and the OrderBy(e => e.EventID).

Contrast that with what happens when we return an IEnumerable:

SELECT 
[Extent1].[EventID] AS [EventID], 
[Extent1].[PerformedByUserID] AS [PerformedByUserID], 
[Extent1].[ActionType] AS [ActionType]
FROM [dbo].[Events] AS [Extent1]
WHERE [Extent1].[EventID] > 1

This time, the Take, the extra Where and the OrderBy were not executed on the server. Only the Where that was executed before the AsEnumerable call became part of the SQL query.

Why is this important? The IEnumerable version would pass back much more data to the client, which would then filter it further, order it and take the first 10. If the SQL process is hosted on a separate machine (usually the case in production), that will mean a lot of unneeded data being passed from the SQL server to the client.

The Entity Framework 4.0 gives you IQueryables. It's usually best to leave them in that form for as long as possible.

Tags: ,

All | General

About the Author

Larry Spencer

Larry Spencer develops software with the Microsoft .NET Framework for ScerIS, a document-management company in Sudbury, MA.