How Do LINQ TO SQL Stored Procedure Calls Work?

Question

I have a LINQ TO SQL Context that I have created that calls a stored proc. When I'm looping through the ISingleResult is it creating entities on the fly while the underlying DataReader reads the results or does it put every thing into Entities upfront before the function call returns.

Basically what is going on is I'm working with a stored proc that may sometimes return 10's of thousands of records while most of the time it only returns a few thousand records.

DatabaseDataContext context = new DatabaseDataContext();
var resultSet = context.MyStoredProc();

foreach (var result in resultSet)
{
    // some code here
}

Would that that load every thing into memory at once or would it be loaded one at a time while I loop through it??

Answer 1

The stored procedure will get called when you enumerate the result, so the execution is deferred. However, it is not lazily loaded. Meaning, once you enumerate the result of your stored procedure, you will effectively be executing the stored procedure in it's entirety and all the results will be brought back.

If you are binding the results of your stored procedure to a data model class, which supports lazy loading on it's child elements, then you get lazy loading on that class' properties.