Is Looping Through Iqueryable The Same As Looping Through A Datareader With Reader.read() In C#

Sorry for the long title :) I couldn't really find any answers this and this question has been circling through my head for some time now. Short summary of question at the end. Bas

Solution 1:

I'm afraid the correct answer here is: it depends.

First of all - it depends on the kind of IQueryable implementation we're talking about; IQueryable<T> is simply an interface and the actual implementation is left to the implementer. To the best of my knowledge Microsoft didn't provide any strict guidelines on how this should be done (heck, no one said IQueryable<T> needs to have anything in common with a database which would make the question pointless).

When we limit ourselves to linq-to-sql/entity-framework, the answer is still: it depends (at least for linq-to-sql, though I'd be surprised if EF works much differently). It depends on several factors (e.g. the type of query you're executing, data volume, etc.). Basically it works similarly to your second pseudo-code (i.e. not loading all the data at once), but optimizations can be used to retrieve data in chunks/buffer it, so in some cases the query execution on the db side might end quicker than your enumeration (though attempting to utilize this is any way might be ... non-trivial).

If you want to simulate the bulk loading version, explicitly call ToList()/ToArray() (or use any other method that would cause all the data to be read).

Solution 2:

AFAIK EF will use your second option. The reason for me saying this is that I have experienced that EF will open a second connection when you execute another query while looping through the results of the first query. Since I was using a transaction, AND I disabled distributed transaction, and a second open connection (while the first connection is still open and is used) disables the re-use of the current transaction, my second query threw an exception saying that DTC wasn't enabled.

So my conclusion was: if you are executing a query and looping through the results (so no .ToList()), then the query is still executing, and the connection is still open, and if EF is using a data-reader, then yes, it is within the loop which reads from the data-reader.