none
Stored procedure with output parameter RRS feed

  • Question

  • I know Entity Framework supports output parameters. What I really I don't understand is its behavior.

    public ObjectResult<OrderDetail> GetPagedOrderDetails(int pageIndex, int rowsPerPage, ObjectParameter parameter) { 
      var r = ExecuteFunction<OrderDetail>("GetPagedOrderDetails",  
        new ObjectParameter("pageIndex", pageIndex),  
        new ObjectParameter("rowsPerPage", rowsPerPage),  
        parameter); 
      var count = Convert.ToInt32(parameter.Value); 
      return r; 

    parameter.Value remains 0. Since it is a parameter of the function, when I check parameter.Value in the calling code it is set to the correct value.

    My question is, why is parameter.Value set correctly only after the flow returns to the calling code?
    Sunday, February 1, 2009 5:58 PM

Answers

  • The ObjectResult is lazy: it reads new rows from the database as the caller reads new elements. This avoids the need to buffer the entire result before returning it to the caller. Until the reader has been consumed, there is no guarantee that the value for the output parameter will be available (e.g. when calling a SQL Server stored procedure), so the Entity Framework waits until the elements have been read before passing through the parameter value.

    ObjectParameter outputParameter = new ObjectParameter(typeof(int));
    foreach (OrderDetail detail in context.GetPagedOrderDetails(0, 10, outputParameter))
    {
        // process detail (underlying database reader is still active)
    }
    // reader is finished and the parameter value is now available
    int output = (int)outputParameter.Value;


    Thanks,
    -Colin


    This posting is provided "AS IS" with no warranties and confers no rights.
    Monday, February 9, 2009 10:20 PM

All replies

  • From what I understand, ObjectParameter parameter is defined in the scope of the calling code. It is used as an output parameter for  the Function being executed.

    Once the functions in evaluated and the reslut is available, it is assigned to the output parameter.

    Hence, you can access the value of parameter.Value only after the function is executed and the ontrol returns to the calling code.


    George Varghese
    Thursday, February 5, 2009 11:19 PM
  • The ObjectResult is lazy: it reads new rows from the database as the caller reads new elements. This avoids the need to buffer the entire result before returning it to the caller. Until the reader has been consumed, there is no guarantee that the value for the output parameter will be available (e.g. when calling a SQL Server stored procedure), so the Entity Framework waits until the elements have been read before passing through the parameter value.

    ObjectParameter outputParameter = new ObjectParameter(typeof(int));
    foreach (OrderDetail detail in context.GetPagedOrderDetails(0, 10, outputParameter))
    {
        // process detail (underlying database reader is still active)
    }
    // reader is finished and the parameter value is now available
    int output = (int)outputParameter.Value;


    Thanks,
    -Colin


    This posting is provided "AS IS" with no warranties and confers no rights.
    Monday, February 9, 2009 10:20 PM