Which methods to close a compiled query

Question

As we know you can't add an extra clause like .Where() or .First() to a compiled query, because that changes the query and forces a recompile. What I would like to know is which methods can be used to "close" a compiled query.

I know most people use either .AsEnumerable() or .ToList(), but which other methods work as well? Can I use .AsQueryable(), or is this a no-op?

And which is better performance-wise? I know .AsEnumerable() is faster than .ToList(), but if I want an IQueryable, is .AsEnumerable().AsQueryable() better than .ToList()?

Answer 1

In most cases, AsEnumerable().AsQueryable() is probably what you want, because:

• By doing an explicit AsEnumerable(), you're not running the risk of the underlying implementation making AsQueryable() into a no-op, thereby ruining your attempt to close the query. I'm not saying that today's EF makes AsQueryable() a no-op (as far as I can tell, it doesn't), only that the behavior-- either no-op or transparently call AsEnumerable()-- isn't documented so relying on it isn't safe.
• AsEnumerable(), unlike ToList(), doesn't load your entire resultset in memory in order to query it. This matters a lot with large resultsets. It's theoretically possible that for smaller resultsets, there could be some advantage to using ToList() (e.g. an optimized ToList() implementation pulls data in big chunks from the underlying provider, while enumeration involves more context switching) but that seems unlikely and hard to depend on across providers and versions, while the large-resultset advantage of AsEnumerable() will live forever.

The one case where I do like calling ToList() is when I explicitly do want to force a query to execute right now. For example, if I want to catch errors in a query earlier in a method so that I can simplify error handling later, or I want to validate all the underlying data before continuing with the rest of the query. Or if the query is more easily tested when chopped in two. And I'll never do this, unless I know that my recordset is going to be small, since calling ToList() on a multi-million-row query will kill your RAM.

To answer your other question, Converting Data Types in the LINQ documentation on MSDN details which LINQ methods force query execution. According to that page, ToArray(), ToDictionary(), ToList(), and ToLookup() all force query execution.

AsEnumerable(), by contrast, doesn't force immediate query execution, but does "close" the query (using your term here, not sure if there's an official term for this). Per http://msdn.microsoft.com/en-us/library/bb335435.aspx:

the AsEnumerable method can be used to hide the custom methods and instead make the standard query operators available.

In other words, running AsEnumerable will force all calls like Take() and Where() to use the generic LINQ implementations and not anythign custom which would cause a re-compile.