none
Using OUTPUT parameter in Entity Framework RRS feed

  • Question

  • Hi All,

    I have a stored procedure that returns three output parameters later i want to use those parameters for next subsequent inserts. This is my stored procedure.

    1  CREATE PROCEDURE [dbo].[spTransactionInsert]
    2  	@TransactionType VARCHAR(255) ,
    3  	@TransactionDate DATETIME ,
    4  --	.... (More fields)
    5  	@TransactionID VARCHAR(50) OUTPUT,
    6  	@TransactionNo VARCHAR(50) OUTPUT,
    7  	@TransactionSerialNo BIGINT OUTPUT
    8  
    9  AS
    10  BEGIN
    11  
    12  	-- Get transaction id if parameter value is zero 
    13  	-- (i.e) First time it will generate the transaction id and send back 
    14  	-- So next time the same transaction id will be used
    15  	IF @TransactionID = '0'
    16  		SELECT @TransactionID = [......] FROM TransactionTable
    17  	ELSE
    18  		SET @TransactionID = @TransactionID
    19  
    20  	-- Get transaction no if parameter value is zero 
    21  	-- (i.e) First time it will generate the transaction no and send back 
    22  	-- So next time the same transaction id will be used
    23  	IF @TransactionNo = '0'
    24  	BEGIN
    25  		SELECT @TransactionSerialNo = [......] FROM TransactionTable
    26  		SET @TransactionNo = @Type + '-' + CAST(@TransactionSerialNo AS VARCHAR)
    27  	END
    28  	ELSE
    29  	BEGIN
    30  		SET @TransactionNo = @TransactionNo
    31  		SET @TransactionSerialNo = @TransactionSerialNo
    32  	END
    33  
    34  	INSERT INTO TransactionTable ....
    35  
    36  
    37  END
    

    I added the table and procedures in the entity model. If I set stored procedure mapping to the table it gives the exception (Error 2047: A mapping function binding specifies a function Model.Store.spTransactionInsert with an unsupported parameter: TransactionID. Output parameters may only be mapped through the RowsAffectedParameter property. Use result bindings to return values from a function invocation.)

    I am using VS 2010 and SL 4. How do I achieve this?

    Thanks is advance.


    Regards, Arun
    Sunday, April 18, 2010 8:30 AM

Answers

All replies

  • Hi Arun,

     

    Welcome to ADO.NET Entity Framework and LINQ to Entities forum!

     

    The output parameter in stored procedure is not supported in EF except the RowsAffectedParameter.   Instead, we can use the result binding of the stored procedure to work around this problem.  For detail, please see http://www.timacheson.com/Blog/2009/jul/entity_framework_stored_procedure_with_output_parameter.  

     

    We may need to modify the original stored procedure to change all the output parameters to input ones.   Then after the INSERT INTO TransactionTable… clause, please add a SELECT clause like:

    =============================================================================================

    SELECT @TransactionID as TransactionID, @TransactionNo as TransactionNo, @TransactionSerialNo as TransactionSerialNo

    =============================================================================================

     

    Then we map the insert stored procedure to the TranactionTable entity in the EDM designer, please add three parameters in Result Column Bindings (below the Parameters mapping) and map them to the corresponding properties of the TransactionTable.   You can refer to this PG blog for references, http://blogs.msdn.com/adonet/archive/2008/03/26/stored-procedure-mapping.aspx. 

     

    If you encounter any problems, please feel free to let me know.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, April 19, 2010 6:24 AM
    Moderator
  • Hi Lingzhi,

    Thanks for your reply.

     

    How do i get the return values (result column binding) after inserting?


    Regards, Arun
    Tuesday, April 20, 2010 6:14 AM
  • Hi Arun,

     

    The result column binding values will be set back to the entity properties.  In your case, the properties would be TransactionID, TransactionNo and TransactionSerialNo in the TransactionTable entity.   

     

    Good day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, April 20, 2010 6:31 AM
    Moderator
  • Hi Arun,

     

    Could you please tell me how is the problem now?   If you have any questions, please be free to let me know.

     

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, April 22, 2010 1:37 AM
    Moderator
  • Hi Lingzhi,

     

    Thanks for your solutions. I got worked.

    Sunday, April 25, 2010 12:25 PM
  • Hi Arun,

      

    Glad to hear that.  :-)

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, April 26, 2010 1:58 AM
    Moderator
  • I´m making a advanced search with dynamic sql that returns data from the serach. I send ID´s and lists of various columns on several for serach in related objects. The sql is dymaicly buildand I´m using paging inside the stored procedure for maximum performance. I´m using the OUTPUT parameter to count the total results from the query but I only retreive the paging size results (ex the search would return 1000  but the paging is set to 10 then i return 10 sets and the output parameter returns the value 1000). The function in my BusinessLayer is as follows:

    GenericOrderSearch(string OrderIDList, string InvoiceIDList, string ContactName, string ContactAddress, string ContactCity, string ContactZipCode, string InvoiceTotal, string SaleName, string OrderStatusIDList, int pagesize, int startrow, string sort, ref int? count).

    See the count parameter (ref int?). And in my SP: "@count int OUTPUT". I can use this parameter using Linq to SQL straight off, but when refactoring my datalayer to EF4 I needed to create a ObjectParameter of my count paramater: ObjectParameter op = new ObjectParameter("count", typeof(int));. The thing is that it doesn´t work on EF. Does anyone have any suggestions or worksround to this problem? 

    Friday, June 18, 2010 9:59 AM
  • why should microsoft continually keep at changing how we author scripts and write codes. Had to search my MSDN only to realize this. Take a look. My inserts works with my stored procedures with OUTPUT parameters but the response returns a dataservicequeryexception.

     

    The InsertFunction element can have the following child elements when applied to an EntityTypeMapping element:

    Thursday, August 12, 2010 11:39 AM
  • I can't believe I'm readnig this. EF doesn't support output params on sprocs? What on earth are they smoking?

    They give us this code-first nonsense but neglect to support perfectly sound stored procedure syntax. Fools!


    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson
    Tuesday, January 25, 2011 8:39 PM
  • go get 'em Jamie!  I've been struggling and working-around EF for years (!?) now, got fired from a project for having proposed it (and watched it fail). EF4 is such a big improvement in some ways (yeah to poco!) but _such_ a freaking unbelievable blistering disappointment in so many other ways that, I greatly fear, the struggle to make it do something useful is just doomed to go on until I have no hair left .. yeah maybe I need to start toking too ..
    Wednesday, March 16, 2011 2:55 PM
  • not sure what they are thinking but the truth is we always have legacy applicaitons relying on db that has tremendous amount business logic in stored procedure that happened to use output parameters.  one one can afford to throw away such investment. and writting new stored proc just for EF is not realistic to many people

    I understand some people like pure object etc but this is a real world

    another point, EF may be quick to code but can make debugging difficult: Edit and Continue not allowed in a member or function with EF or Linq

    finally, I found applicaiton with EF 3.5 does not appear as robust. Minor change on part of the EF model or back end  DB can cause significant problem besides updating the model is not always that easy. one may have to rename procedure delete the corresponding part in model, rename and update.

    business rule and need can change over time unfortunately.

    for some applications relying heavily on stored procedures, ado.net with a utility to generate the required call parameters or even member is far easier and amenable to change while probably just aa productive over time compared to EF 3.5.

    Maybe some day some future version of EF will be significantly to more amenable to change, more robust w/o throwing oeverwhelming tons of error messages some minor change.
    • Edited by fs - ab Thursday, April 19, 2012 8:54 PM
    Thursday, April 5, 2012 4:43 AM