I had a discussion at DevWeek about when SqlDataReader.Read() gets called for resultsets coming back from stored procedures accessed with LINQ to SQL.

I've got this stored proc;

create procedure dbo.GetCustomersByCountry  
 @country nvarchar(max)  
as  
 set nocount on  
 select * from dbo.customers  
 where country = @country

and I've brought it into LINQ to SQL using the designer and that means that I can write code such as;

 using (NorthwindModelDataContext ctx =
      new NorthwindModelDataContext())
    {
      var query = ctx.GetCustomersByCountry("UK");

      foreach (Customer c in query)
      {
        Console.WriteLine(c.CustomerID);
      }
    }

Question is - when does SqlDataReader.Read() get called here? Do we read all of the data up front and then just provide access to an in-memory collection or is the data read from the wire as the loop iterates over it?

I wanted to check so I went ahead and set some breakpoints in a few places;

1. Inside the constructor of my Customer class.
2. Inside SqlDataReader.Read()

and what I see there is an interleaved set of calls which go [Read->Construct->Read->Construct] and so the results are being returned as they are read from the wire - i.e. as the foreach loop pulls them out object by object.

Now, here's another stored procedure;

create procedure TestOutputProc(  
 @id int output  
)  
as  
 set nocount on  
 select * from dbo.customers  
 set @id = 10

This has an output parameter. I bring this into the LINQ to SQL environment and I program against it like this;

  using (NorthwindModelDataContext ctx =
      new NorthwindModelDataContext())
    {
      int? x = null;

      var query = ctx.TestOutputProc(ref x);

      Console.WriteLine(x);

      foreach (Customer c in query)
      {
        Console.WriteLine(c.CustomerID);
      }
    }

Notice - the output parameter is available before I've started to enumerate through the query results. If I go and run this under my debugger then what I see is [Read->Read->Read->Read-> ....] and so the entire resultset is read before we get to the Console.Writeline.

This isn't entirely unexpected - AFAIK you need to read the resultset before you can get to the output parameter and that's what is surfacing inside of LINQ to SQL here. However, it does mean that you need to apply some thought if you're going to return a lot of data from a procedure with an outgoing parameter.