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

• Simon Whitehead: It would be horribly inefficient otherwise...

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.

• carewithl: I know only one query is sent to the DB, but I remember reading somewhere that results of that query are then streamed one row at the time back to the application
• carewithl: I apologize, but just to be 100% sure - so results (requested by a single query) are never streamed back as they are requested but are always retrieved at once?
• Euphoric: Sorry. I don't know. Intuitively, I assume that results are all returned in one go, but I might be wrong.