The Difference Between IEnumerable and IQueryable Explained
This post is part of this year’s C# Advent Series. Which counts down to Christmas with two C# articles published every day from Dec 1 – Dec 25.
I’ve always been curious how ORMs work. How does C# code get converted into SQL statements?
Research on that topic led me to blog about Expressions and the Visitor Pattern. I’ve also read a lot about the difference between the IEnumerable and IQueryable interfaces. But it took me awhile to really understand what made them different.
I have recently been walking through a great series of blog posts by Matt Warren describing in detail how to write a query provider. I’ve learned a lot, and seeing and coding examples myself has helped me grasp how they’re both used.
I wanted to summarize some of the things that I’ve learned and provide a resource for anyone else that might be struggling with this same topic.
Similar But Different
In a C# project, if you navigate to the LINQ extension method definitions for IEnumerable and IQueryable, you’ll see that they are very similar.
IEnumerable:
IQueryable:
Since the methods available are basically the same, what really sets these two interfaces apart? The main difference is where these interfaces are intended to be run.
IEnumerable – intended to be run against an in memory collection.
IQueryable – intended to be run against a “queryable” collection (ex. a database).
Lets take the following examples.
When we have an in memory list, this Where LINQ statement is defined on the IEnumerable interface.
On the other hand, when we are using EF Core to connect to a database, this Where LINQ statement is defined in the IQueryable interface.
The Users class is a stand in for the Users database table. The Users data doesn’t exist in our app. In order to have access to the user who’s Id is 1, it needs to run this LINQ statement against a database. Since that’s the case, we’re using the IQueryable methods.
Source Code
Lets take a look at the implementation for a where statement for the IEnumerable and IQueryable interfaces. (Which I’ve slightly modified to make it easier to read)
In this first IEnumerable example, the where method will basically iterate over the IEnumerable, applying the predicate Where(n => n.Contains("J") , returning any elements for which the predicate is true.
The IQueryable method on the other hand, invokes an Expression.Call() method? What is the point of that?
Expressions and Query Providers
Because a database doesn’t exist in memory, we need some mechanism to take our LINQ statements and convert them into a SQL statement.
Expressions are the tools that allow us to do that. When LINQ methods on the IQueryable object are called, what really happens is it builds an Expression Tree.
Then when results are expected from the IQueryable, that Expression Tree is converted into the source query language (ex. SQL), that source query is run against the source (ex. SQL Database), results returned and mapped to C# objects.
The conversion between Expression Trees and a source query language is the responsibility of the Query Provider.
There are Query Providers for all kinds of external queryable collections. SQL Server, Oracle Sql, Postgres, MySQL/MariaDb, CosmosDb, Sqlite, etc.
If any arbitrary data store wants to allow C# developers to use LINQ to query against their data source, then someone needs to write a Query Provider.
Simple Query Provider
To see the basic workings of a query provider lets go ahead and create a very minimal query provider. This allows us to know how a C# expression tree would be mapped into a SQL statement.
This example very much builds off the work in Matt Warren’s series, which I mentioned above.
Lets start off by taking a look at the IQueryable interface.
You’ll notice that it implements from the IEnumerable interface, so IQueryable is an IEnumerable. It has three properties, Type, Expression and IQueryProvider.
The IQueryProvider in turn has 4 methods total, a generic and a non generic version of an Execute and CreateQuery.
Lets start coding by creating a Query class that will implement the IQueryable interface.
You’ll notice that lines 18-20 implement the IQueryable properties we mentioned above. Then the other methods are merely a pass through to the provider.
So lets turn our attention to our Query Provider.
For the most part, this class is responsible for calling the QueryTranslator to get the SQL statement representation of the Expression Tree.
Which the QueryTranslator takes the Expression and using the Visitor Pattern, converts it in a valid SQL statement.
There are many different types of Expressions, and each Expression has specific rules for how that Expression type gets mapped to some SQL statement.
Lets run this example to see what happens.
This example outputs query: SELECT * FROM (SELECT * FROM Customer) AS T WHERE(Id = 1). Which is a valid SQL statement!
Which means we’ve written the simplest QueryProvider ever.
Closing Thoughts
I hope this article was helpful in helping you understand the difference between the IEnumerable and IQueryable interfaces are.
If you’re interested in learning more about writing a Query Provider I would check out Matt’s series that I have mentioned previously.
I hope you enjoy this and the rest of the articles published during the 2019 C# Advent! Thanks to Matt Groves for setting this up again this year!