IEnumerable vs IQueryable – Part 1

What’s the difference between IQueryable and IEnumerable? This is probably the second most frequent question I’ve been asked (number 1 has to be understanding delegates).

The purpose of this article isn’t to formally define the interfaces, but rather to paint an easy to understand picture of how how do they differ. Then in Part 2 we get practical with 8 code snippet questions where we can test our understanding of the topic.

The Difference (Short answer)

• IEnumerable – quering a collection in Memory
• IQueryable – quering an External Data Source (most commonly a database)

What is IEnumerable?

The IEnumerable interface exposes an GetEnumerator method, which returns an IEnumerator interface which allows us to iterate through the collection. In plain English: an IEnumerable is a collection of items you can loop through.

Did you know, even arrays are inherently IEnumerable. See this snippet below:

int[] nums = { 1, 2, 3, 4 };
bool isEnumerable = nums is IEnumerable<int>; // True

IsEnumerable in the above code is true. This kind of makes sense, since an array (just like IEnumerable) is a collection of items we can loop through.

What makes IEnumerable special?

The most special thing about IEnumerable is that we can query items using LINQ.

There are a bunch of LINQ methods (Where, Select, Take, OrderBy, First etc.) which are simply extension methods for the IEnumerable interface. Same as all extension methods, just include the namespace (System.Linq) and the whole range of LINQ extensions are available to filter our collection.

Something else that’s very important to understand when using IEnumerable is Deferred Execution. In short, a LINQ Query only captures the intent. It holds off as long as it can and only does the actual filtering when any of the following happens:

• Iterate the results (e.g. foreach)
• Call ToList
• Get a single result from the query (e.g. .Count() or .First())

Understanding Deferred Execution is key to using IEnumerable correctly. Not understanding this can lead to unnecessary performance issues. Check out Part 2 to see if you understand it correctly.

What is IQueryable?

Firstly IQueryable inherits from IEnumerable. This means inherently, it is also a collection of items that you can loop through. We can also write LINQ queries against an IQueryable.

IQueryable is used when querying a data source (let’s say a database).  So if we are using Entity Framework (EF), we can write a LINQ Query as follows and it will actually produce a SQL query:

In the above, our LINQ Query was translated to a SQL Query. When it is executed, the query will be run against our SQL database and return results to memory. Remember, the filtering does NOT happen in memory, but in the database (read this sentence again to make sure you’ve got it).

How does LINQ suddenly become SQL?

There are 2 important properties on the IQueryable interface: Expression and Provider

• Expression – This is the Expression Tree built up from the LINQ Query
• Provider – Tells us how to translate the Expression Tree into something else

In our case (using EF with a SQL database) what happened:

• We created a simple LINQ query
• This built up an Expression Tree
• The Expression Tree gets passed to the Provider
• Our provider translates the Expression Tree in SQL Query
• As soon as we use our results (deferred execution), the SQL Query will execute against a database.
• The results are returned and stored into memory.

Great uses for IQueryable

Think about the way IQueryable works for a moment. Let’s say we have a custom Data Source like a file which appends data with some separators we defined. If we find ourselves, constantly reading these files and trying to sift through the text to get hold of data;  we could instead use IQueryables and create our own Query Provider. This will allow us to write LINQ queries to get data from our files.

Another popular place IQueryable is used is for ASP.NET WebAPI OData. We can expose our REST endpoints and allow the person using our Web Service to filter only the data they need without pulling all data down to the client first. OData is basically just a standard that allows us to use URLs to filter specific data.

Example: Let’s say our REST service returns a list of 100 000 People: (http://mysite.com/People). But in our app we only want the people whose surnames contain the search text “Filter”.

Without the power of IQueryable and OData, we would either have to:

• Pull all 100 000 people down to our client and then locally in memory filter for those 10 people with surname “Filter” that we actually need.
• Or create an endpoint specfically for searching for people by surname, passing a query string parameter “Filter”.

Neither of these are great. But using Web API with OData, we could create a controller that returns IQueryable<Person> and then allow our app to:

• Send a custom URL: http://mysite.com/People$filter=contains(Surname,’Filter’)
• On the server, the IQueryable Expression Tree is built up from the OData URL
• The Provider translates the Expression Tree to a SQL Query
• The SQL executes against the database only getting 10 items from it
• These 10 items are returned to app as the client’s requested format (e.g. JSON)

So with the power of IQueryable and OData, we indirectly queried the database via a URL, without having to write server code and didn’t have to pull data we did not need. (less bandwidth, less server processing and minimal client memory footprint)

Side note: LINQ Query Syntax vs Extension Methods

Not directly related to the topic, but a question I’ve been asked several times as well. Is it better to use Query Syntax or Extension methods.

Query Syntax:

var result = from n in nums
             where n > 2
             select n;

Extension Methods:

var result = nums.Where(n => n > 2);

They both compile down to the Extension Methods in the end. The Query syntax is simply a language feature added to simplify complex queries. Use which ever is more readable and maintainable for you.

I prefer to use Extension Methods for short queries and Query Syntax for complex queries.

Conclusion

If you missed everything, just remember this:

• IEnumerable – queries a collection in Memory
• IQueryable – queries an External Data Source (most commonly a database)

If you are comfortable with these interfaces, go to Part 2 and test yourself with 8 quick code snippet questions on the topic.