RE: IQueryable vs. IEnumerable in LINQ to SQL queriesMar27

Tuesday, 27 March 2012 by haemoglobin

I came across an interesting blog post today (admittedly an old one!) from Jon Kruger who experimented with some interesting behaviour differences between IQueryable<T> and IEnumerable<T>. The blog post can be found here, however the summary of the findings are below: 

NorthwindDataContext dc = new NorthwindDataContext();
IEnumerable<Product> list = dc.Products
     .Where(p => p.ProductName.StartsWith("A"));
list = list.Take<Product>(10);
Debug.WriteLine(list.Count<Product>());  //Does not generate TOP 10 !!

and

NorthwindDataContext dc = new NorthwindDataContext();
IEnumerable<Product> list2 = dc.Products
     .Where(p => p.ProductName.StartsWith("A"))
     .Take<Product>(10);
Debug.WriteLine(list2.Count<Product>()); //Works correctly

and

NorthwindDataContext dc = new NorthwindDataContext();
IQueryable<Product> list3 = dc.Products
     .Where(p => p.ProductName.StartsWith("A"));
list3 = list3.Take<Product>(10);
Debug.WriteLine(list3.Count<Product>()); //Works correctly

The first example will neglect the very important TOP 10 statement from the generated SQL query, returning all rows into memory and then returning the first 10 from there (obviously not ideal). The next two correctly include the TOP 10 statement returning only those rows from the database.

The reason the first statement fails is the call to Take is actually calling the IEnumerable<T> extension method from Enumerable. In ILSpy, this has the following implementation (TakeIterator being a private method returned from Take):

private static IEnumerable<TSource> TakeIterator<TSource>(IEnumerable<TSource> source, int count)
{
    if (count > 0)
    {
        foreach (TSource current in source)
        {
            yield return current;
            if (--count == 0)
            {
                break;
            }
        }
    }
    yield break;
}
 

In the second and third examples however, Take is called on a IQueryable<T> which executes the extension method defined in Querable. This has a totally different implementation as ILSpy shows below: 

public static IQueryable<TSource> Take<TSource>(this IQueryable<TSource> source, int count)
{
    if (source == null)
    {
        throw Error.ArgumentNull("source");
    }
    return source.Provider.CreateQuery<TSource>(Expression.Call(null, ((MethodInfo)MethodBase.GetCurrentMethod()).MakeGenericMethod(new Type[]
    {
        typeof(TSource)
    }), new Expression[]
    {
        source.Expression,
        Expression.Constant(count)
    }));
}

As per the MSDN documentation, calls made on IQueryable operate by building up the internal expression tree instead.
"These methods that extend IQueryable(Of T) do not perform any querying directly. Instead, their functionality is to build an Expression object, which is an expression tree that represents the cumulative query. "

When Count is called in the last two examples, Take has already been built into the expression tree causing a TOP 10 to appear in the SQL statement. In the first example however, Take on IEnumerable<T> will start iterating the IQueryable returned from Where, which does not have “Take” in it’s expression tree, hence the behaviour.  If you are wondering why an IQueryable can be cast to an IEnumerable as in the first two examples, IQueryable extends IEnumerable, IQueryable hence being pretty much the same interface but provides a few extra properties to house the LINQ provider and internal expression tree. The actual extension methods off each of these interfaces however are quite different.

Another thing that helps when thinking about LINQ queries is they in effect execute from the last call in, not the other way around like most traditional method call chains. So for example, calling Count will start enumerating over the IEnumerable returned from Take, which itself will enumerate over the IEnumerable returned from Where and so on, depending on how many LINQ operators you chain together.

Categories:   Development
Actions:   E-mail | Permalink | Comments
blog comments powered by Disqus

Powered by BlogEngine.NET 1.6.1.0 | Design by styleshout | Enhanced by GravityCube.net | 1.4.5 Changes by zembian.com | Adapted by HamishGraham.NET
(c) 2010 Hamish Graham. Banner Image (c) Chris Gin