Mike Taulty's Blog
Bits and Bytes from Microsoft UK
LINQ to SQL, Stored Procs, Output Params, Readers Reading

Blogs

Mike Taulty's Blog

Elsewhere

Archives

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.


Posted Thu, Mar 13 2008 3:31 PM by mtaulty
Filed under: , ,

Comments

Wöchentliche Rundablage: ASP.NET MVC, Silverlight 2, LINQ… | Code-Inside Blog wrote Wöchentliche Rundablage: ASP.NET MVC, Silverlight 2, LINQ… | Code-Inside Blog
on Mon, Mar 17 2008 1:15 PM
SimonS Blog on SQL Server Stuff wrote LINQ to SQL Gotchas - Using Stored procedures
on Tue, Mar 25 2008 11:08 AM
I'm currently on the "I like LINQ to SQL" side of the fence. I think it is very much a
Mike Taulty's Blog wrote "When Do Queries Execute"
on Wed, May 21 2008 7:18 AM
Julie's got a great post about when stored procedures execute when using LINQ to Entities. It made me...
get a list quarrel cave value - Achord wrote get a list quarrel cave value - Achord
on Thu, Sep 11 2014 3:56 PM

Pingback from  get a list quarrel cave value - Achord