So there I was, tip-tapping away at my keyboard when I became aware of the slowly loudening noise of a debate. It wasn't about poverty, war, civil rights or anything like that. No; this was far more contentious. It was about the behaviour of
<a href="https://msdn.microsoft.com/en-gb/library/bb351562(v=vs.100).aspx">IQueryable<T></a> when mixed with
<a href="https://msdn.microsoft.com/en-gb/library/9eekhta0(v=vs.100).aspx">IEnumerable<T></a>. I know, right, how could I not get involved?
The code that was being debated was a database query that was being facilitated by Entity Framework. Now let me ask you a question: what is the problem with the methods below?
I've rather emphasised the problem by expressly declaring types in the
GetSagesWithSayings method. More typically the
IQueryable<Sage> would be hiding itself beneath a
var making the problem less obvious. But you get the point; it's something to do with an
IQueryable<Sage> being passed back as an
The debate was raging around what this piece of code (or one much like it) actually did. One side positing "it'll get every record from the database and then throw away what it doesn't need in C#-land..." The opposing view being "are you sure about that? Doesn't it just get the records from the last hundred years from the database?"
So it comes down the SQL that ends up being generated. On the one hand it's going to get everything from the Sages table...
Or does it include a filter clause as well?
You probably know the answer... It gets everything. Every record is brought back from the database and those that are older than 100 years are then casually thrown away. So kinda wasteful. That's the problem. But why? And what does that tell us?
The term "LINQ to Objects" refers to the use of LINQ queries with any
IEnumerable<T>collection directly, without the use of an intermediate LINQ provider or API such as LINQ to SQL or LINQ to XML.
<a href="https://msdn.microsoft.com/en-gb/library/bb351562(v=vs.100).aspx">IQueryable<T></a>interface is intended for implementation by query providers.
This interface inherits the
<a href="https://msdn.microsoft.com/en-gb/library/9eekhta0(v=vs.100).aspx">IEnumerable<T></a>interface so that if it represents a query, the results of that query can be enumerated. Enumeration forces the expression tree associated with an
<a href="https://msdn.microsoft.com/en-gb/library/bb351562(v=vs.100).aspx">IQueryable<T></a>object to be executed. Queries that do not return enumerable results are executed when the
<a href="https://msdn.microsoft.com/en-gb/library/bb549414(v=vs.100).aspx">Execute<TResult>(Expression)</a>method is called.
The definition of "executing an expression tree" is specific to a query provider. For example, it may involve translating the expression tree to a query language appropriate for an underlying data source.
I know - check me out with my "quotes".
IQueryable are similar; for instance they are both considered "lazy" as they offer deferred execution. But there is an important difference between
IQueryable; namely that
IQueryable hands off information about a query to another provider in order that they may decide how to do the necessary work.
IEnumerable does not; its work is done in memory by operating on the data it has.
So let's apply this to our issue. We have an
IQueryable<Sage> and we return it as an
IEnumerable<Sage>. By doing this we haven't changed the underlying type; it's still an
IQueryable<Sage>. But by upcasting to
IEnumerable<Sage> we have told the compiler that we don't have an
IQueryable<Sage>. We've lied. I trust you're feeling guilty.
No doubt whoever raised you told you not to tell lies. This was probably the very situation they had in mind. The implications of our dirty little fib come back to haunt us when we start to chain on subsequent filters. So when we perform our filter of
.Where(x => x.DateOfBirth > aHundredYearsAgo) the compiler isn't going to get LINQ to Entities's extension methods in on this. No, it's going to get the LINQ to object extension methods instead.
This is the cause of our problem. When it comes to execution we're not getting the database to do the heavy lifting because we've moved away from using
There are 2 courses of action open to you. The obvious course of action (and 99% of the time what you'd look to do) is change the signature of the `` method to return an IQueryable like so:
The other alternative is what I like to think of as "the escape hatch":
<a href="https://msdn.microsoft.com/en-gb/library/bb353734(v=vs.100).aspx">AsQueryable</a>. This takes an
IEnumerable, checks if it's actually an
IQueryable slumming it and casts back to that if it is. You might use this in a situation where you didn't have control over the data access code. Using it looks like this: (and would work whether
GetSagesWithSayings was returning