I know some differences of LINQ to Entities and LINQ to Objects which the first implements IQueryable
and the second implements IEnumerable
and my question scope is within EF 5.
My question is what's the technical difference(s) of those 3 methods? I see that in many situations all of them work. I also see using combinations of them like .ToList().AsQueryable()
.
-
What do those methods mean, exactly?
-
Is there any performance issue or something that would lead to the use of one over the other?
-
Why would one use, for example,
.ToList().AsQueryable()
instead of.AsQueryable()
?
Best Answer
There is a lot to say about this. Let me focus on
AsEnumerable
andAsQueryable
and mentionToList()
along the way.What do these methods do?
AsEnumerable
andAsQueryable
cast or convert toIEnumerable
orIQueryable
, respectively. I say cast or convert with a reason:When the source object already implements the target interface, the source object itself is returned but cast to the target interface. In other words: the type is not changed, but the compile-time type is.
When the source object does not implement the target interface, the source object is converted into an object that implements the target interface. So both the type and the compile-time type are changed.
Let me show this with some examples. I've got this little method that reports the compile-time type and the actual type of an object (courtesy Jon Skeet):
Let's try an arbitrary linq-to-sql
Table<T>
, which implementsIQueryable
:The result:
You see that the table class itself is always returned, but its representation changes.
Now an object that implements
IEnumerable
, notIQueryable
:The results:
There it is.
AsQueryable()
has converted the array into anEnumerableQuery
, which "represents anIEnumerable<T>
collection as anIQueryable<T>
data source." (MSDN).What's the use?
AsEnumerable
is frequently used to switch from anyIQueryable
implementation to LINQ to objects (L2O), mostly because the former does not support functions that L2O has. For more details see What is the effect of AsEnumerable() on a LINQ Entity?.For example, in an Entity Framework query we can only use a restricted number of methods. So if, for example, we need to use one of our own methods in a query we would typically write something like
ToList
– which converts anIEnumerable<T>
to aList<T>
– is often used for this purpose as well. The advantage of usingAsEnumerable
vs.ToList
is thatAsEnumerable
does not execute the query.AsEnumerable
preserves deferred execution and does not build an often useless intermediate list.On the other hand, when forced execution of a LINQ query is desired,
ToList
can be a way to do that.AsQueryable
can be used to make an enumerable collection accept expressions in LINQ statements. See here for more details: Do i really need use AsQueryable() on collection?.Note on substance abuse!
AsEnumerable
works like a drug. It's a quick fix, but at a cost and it doesn't address the underlying problem.In many Stack Overflow answers, I see people applying
AsEnumerable
to fix just about any problem with unsupported methods in LINQ expressions. But the price isn't always clear. For instance, if you do this:...everything is neatly translated into a SQL statement that filters (
Where
) and projects (Select
). That is, both the length and the width, respectively, of the SQL result set are reduced.Now suppose users only want to see the date part of
CreateDate
. In Entity Framework you'll quickly discover that......is not supported (at the time of writing). Ah, fortunately there's the
AsEnumerable
fix:Sure, it runs, probably. But it pulls the entire table into memory and then applies the filter and the projections. Well, most people are smart enough to do the
Where
first:But still all columns are fetched first and the projection is done in memory.
The real fix is:
(But that requires just a little bit more knowledge...)
What do these methods NOT do?
Restore IQueryable capabilities
Now an important caveat. When you do
you will end up with the source object represented as
IQueryable
. (Because both methods only cast and don't convert).But when you do
what will the result be?
The
Select
produces aWhereSelectEnumerableIterator
. This is an internal .Net class that implementsIEnumerable
, notIQueryable
. So a conversion to another type has taken place and the subsequentAsQueryable
can never return the original source anymore.The implication of this is that using
AsQueryable
is not a way to magically inject a query provider with its specific features into an enumerable. Suppose you doThe where condition will never be translated into SQL.
AsEnumerable()
followed by LINQ statements definitively cuts the connection with entity framework query provider.I deliberately show this example because I've seen questions here where people for instance try to 'inject'
Include
capabilities into a collection by callingAsQueryable
. It compiles and runs, but it does nothing because the underlying object does not have anInclude
implementation anymore.Execute
Both
AsQueryable
andAsEnumerable
don't execute (or enumerate) the source object. They only change their type or representation. Both involved interfaces,IQueryable
andIEnumerable
, are nothing but "an enumeration waiting to happen". They are not executed before they're forced to do so, for example, as mentioned above, by callingToList()
.That means that executing an
IEnumerable
obtained by callingAsEnumerable
on anIQueryable
object, will execute the underlyingIQueryable
. A subsequent execution of theIEnumerable
will again execute theIQueryable
. Which may be very expensive.Specific Implementations
So far, this was only about the
Queryable.AsQueryable
andEnumerable.AsEnumerable
extension methods. But of course anybody can write instance methods or extension methods with the same names (and functions).In fact, a common example of a specific
AsEnumerable
extension method isDataTableExtensions.AsEnumerable
.DataTable
does not implementIQueryable
orIEnumerable
, so the regular extension methods don't apply.