Are elements returned by Linq-to-Entities query streamed from the DB one at the time or are they retrieved all at once?

Question

Are elements returned by Linq-to-Entities query streamed from the database one at the time ( as they are requested ) or are they retrieved all at once:

    SampleContext context = new SampleContext(); // SampleContext derives from ObjectContext
    var search = context.Contacts;

    foreach (var contact in search)
    {
       Console.WriteLine(contact.ContactID); // is each Contact retrieved from the DB 
                                             // only when foreach requests it?
    }

thank you in advance

Answer 1

They are retrieved using one query. Your example does simple select for all rows of Contacts table.

Streaming would be highly ineffective.

But this is only for query itself. There might be lazy loading involved for navigation entities stored in properties or collection of entities. Those are loaded on-demand as you access them. This might result in in-famous N+1 problem.