Technical blog where I share some information from different areas of my own experience, which might be useful to other software developers.

Saturday, April 13, 2013

Entity Framework: IQueryable vs. IEnumerable

Many of us sometimes get confused of different aspects of using IEnumerable<T> and IQueryable<T> in Entity Framework. One of considered opinions is that Entity Framework and DbContext and deffered execution just do all the magic and there is no difference. But even though LINQ query result remains basically the same, it may significantly impact the query performance.

Code sample

I'm going to create a simplified database consisting of one table with million records with Entity Framework Code First.

Performance results

The difference is impressive: IEnumerable query is about 127 times slower than its IQueryable version. And the working time is really demonstrative: 5 seconds is obviously too much for this simple and trivial query.

SQL Server inside

We can use IntelliTrace or any SQL profiler to see what happens on SQL server side.

In case it's IQueryable the following SQL query is executed:

SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Random] AS [Random]
FROM [dbo].[Foos] AS [Extent1]
WHERE [Extent1].[Random] < 1

And the following in case it's IEnumerable:

SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Random] AS [Random]
FROM [dbo].[Foos] AS [Extent1]

Impact

The obvious conclusion is that the IEnumerable query requests all the million records from SQL Server and performs the Where extension method through enumerating all the obtained entities after their materialization.

Reasons

Why it happens? We should expect that in both cases different extension methods are exectuted: Enumerable.Where and Queryable.Where. Let's dig deeper and look into the code of these methods. We can use .NET Reflector or JetBrains dotPeek (which is free) or any other .NET decompiler. The following code is simplified to show the most essential info:

We can see that Enumerable returns WhereEnumerableIterator which knows nothing about query provider and can only perform Where predicate through enumerating all entities. On the other hand Queryable keeps IQueryable.Provider, in Entity Framework it's System.Data.Entity.Internal.Linq.DbQueryProvider, which can generate combined SQL query.

Workaround

But what can we do if our architecture supposes IEnumerable and we cannot change method signature?

The AsQueryable() extension method will cast the parameter to IQueryable if it's possible, otherwise create EnumerableQuery, which will be a query provider itself and generate queries basing on existing enumerable source.