Tuesday, 17 February 2015

What happens when you cast an IQueryable to an IEnumerable

Recently I was digging through some code and came across an issue where the "Where" expressions I was applying in code were not being run against the database. After a bit of digging it turned out to be related to an implicit cast of an IQueryable<T> to and IEnumerable<T>. This cast affects the query generation process with every clause added after the cast being run in memory.

So how about an example to demonstrate it and make it clearer. Looking at the two unit tests below, what would you expect the resulting SQL query to be? Keep in mind the IQueryable inherits from IEnumerable, so the casting is valid.

    public class UnitTest1
    {
        [TestMethod]
        public void RunQueryAsIQueryable()
        {
            IQueryable<Record> records = new ModelContext().Records;
            var result = records.Take(10).ToArray();
        }

        [TestMethod]
        public void RunQueryAsIEnumerable()
        {
            IQueryable<Record> records = new ModelContext().Records;
            var result = ((IEnumerable<Record>)records).Take(10).ToArray();
        }
    }

Here are the results from SQL profiler

As IQueryable
    SELECT TOP (10) 
    [c].[Id] AS [Id], 
    [c].[Name] AS [Name]
    FROM [dbo].[Records] AS [c]

As IEnumerable
    SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Name] AS [Name]
    FROM [dbo].[Records] AS [Extent1]

As you can see the IQueryable "Take/Top" clause is run against the database, where as the IEnumerable "Take" clause is not. Now consider the following example.

    public class UnitTest1
    {
        [TestMethod]
        public void RunHybridQuery()
        {
            IQueryable<Record> records = new ModelContext().Records;
            var result = ((IEnumerable<Record>)records.Where(record => record.Name.StartsWith("something"))).Take(10).ToArray();
        }   
    }

    SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Name] AS [Name]
    FROM [dbo].[Records] AS [Extent1]
    WHERE [Extent1].[Name] LIKE N'something%'

As you can see with this example everything up until the cast(note the top 10 is missing) runs against the database.

This "unexpected" behavior is actually by design and relates to the reason why we have the two interfaces, as opposed to just IEnumerable. An IEnumerable is something that is enumerated, i.e. looped over. An IQueryable on the other hand represents something that can be queried, generally by a query language.

Seems pretty self explanatory, the confusion that comes in is when we use the LINQ syntax to mix these two concepts into a single statement. Because of the fluent nature of the LINQ API it can often be hard to know exactly what the parameter and return types of each method in your chain are and what the actual query being run is(without profiling). So the lesson here, be careful and don't rely on LINQ to do all of your work for you, it is only as smart as the person who wrote it.