Which LINQ statements force Entity Framework to return from the DB?

Question

I know of several LINQ statements that will cause EF to evaluate and return results form the DB to memory. .ToList() is one. Does anyone have a comprehensive list of the statements that do this?

Not sure of...

.SingleOrDefault()
.Union()

EDIT: Wish I could accept all these answers. Great info from everyone!

Answer 1

It's a long list. They boil down to

Aggregate
All<TSource>
Any
Average
Contains
Count
ElementAt<TSource>
ElementAtOrDefault<TSource>
Empty<TResult>
First
FirstOrDefault
Last
LastOrDefault
LongCount
Max
Min
SequenceEqual
Single
SingleOrDefault
Sum
ToArray<TSource>
ToDictionary
ToList<TSource>
ToLookup

The rest are either Deferred Streaming Execution or Deferred Non-Streaming Execution.

In light of your question, SingleOrDefault() is Immediate Execution and Union() is Deferred Streaming Execution.

Answer 2

Anything that returns a concrete object or data structure (Count, Sum Single, First, ToList, ToArray, etc.) is evaluated immediately, so SingleOrDefault certainly does.

Anything that returns an IQueryable<T> (Select, GroupBy, Take) will be deferred (so that operations can be chained), so Queryable.Union will be deferred.

Anything that returns an IEnumerable<T> will also be deferred, but subsequent queries will be done in Linq-to-objects, so subsequent operations won't be translated to SQL. (Empty is an exception since there's not really anything to defer - it just returns an empty collection)

Answer 3

From MSDN,

Queries that perform aggregation functions over a range of source elements must first iterate over those elements.

Examples of such queries are Count, Max, Average, and First. These execute without an explicit foreach statement because the query itself must use foreach in order to return a result.

Note also that these types of queries return a single value, not an IEnumerable collection.

To force immediate execution of any query and cache its results, you can call the ToList<TSource> or ToArray<TSource> methods.

Answer 4

In the case of Entity Framework, there's an easy way to refresh your memory.

Entity Framework has Async variants of all such methods defined in System.Data.Entity, because Async doesn't make sense with the others as it's the very act of querying the database that the Async variants do asynchronously.

So if there's an Async variant, then it hits the database and otherwise it does not.

Enumerating or producing an IEnumerable<T> is a part-way case though: The act of obtaining an IEnumerable<T> obtaining its enumerator (as happens at the start of foreach blocks) does not hit the database, but the first MoveNext() (which happens immediately within the foreach does hit the database, and it then continues to stream from the resultset until either MoveNext() returns false (as when the foreach reaches a "natural" end) or the enumerator is disposed prior to that (as when the foreach is aborted).