none
Stored Procedures with output parameter and resultset RRS feed

  • Question

  • Hi, I'm following this example:

    http://blogs.microsoft.co.il/blogs/gilf/archive/2010/05/09/how-to-retrieve-stored-procedure-output-parameters-in-entity-framework.aspx

    for calling a stored procedure that returns an output parameter, this is working fine. Now my stored procedure needs to return a result set as well, is there any way of doing this without mapping to a complex type or an entity?, because my edmx file is in another project than my entities, I can´t see the classes from my Model Browse. I tried to modify the ExecuteFunction in the edmx designer adding my entityClass in the return but I receive an error

    The stored procedure or function does not have a return type. ExecuteFunction only supports stored procedures and functions that have a return type.

    Any idea?

    Thank you very much!



    Tuesday, March 13, 2012 2:45 AM

Answers

  •  
    You can use the backdoor and run the sproc the traditional way, get a
    resultset back and use a datareader too the traditional way if need be.
     
     
     
    Tuesday, March 13, 2012 2:45 PM
  • Hi rguarnieri,

    Welcome to MSDN Forum.

    We have two ways to work with stored procedure in Entity Framework, one is mapping stored procedure to entities, and the other is importing functions to map stored procedure. The first way, you can modify the inserting, updating and deleting functions by mapping your own stored procedures to the entities. The second way, you can work with other types of stored procedure. You can import procedures that return data, whether that is a set of entities, a set of scalar values, or even a set of results for which you don’t have a matching type in your model. Function Import also lets you map procedures that affect the database even if they don’t return any data at all.

    Based on the issue, you want to return a result set which doesn't have a matching type in the model, in this scenario, you have to create a complex type to map the result. If you don't want to do that, @darnold924's solution is a workaround, you can call the stored procedure directly in Entity Framework. Please refer to the link, I think it could help you to solve the issue.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, March 14, 2012 5:29 AM
    Moderator

All replies

  •  
    You can use the backdoor and run the sproc the traditional way, get a
    resultset back and use a datareader too the traditional way if need be.
     
     
     
    Tuesday, March 13, 2012 2:45 PM
  • Hi rguarnieri,

    Welcome to MSDN Forum.

    We have two ways to work with stored procedure in Entity Framework, one is mapping stored procedure to entities, and the other is importing functions to map stored procedure. The first way, you can modify the inserting, updating and deleting functions by mapping your own stored procedures to the entities. The second way, you can work with other types of stored procedure. You can import procedures that return data, whether that is a set of entities, a set of scalar values, or even a set of results for which you don’t have a matching type in your model. Function Import also lets you map procedures that affect the database even if they don’t return any data at all.

    Based on the issue, you want to return a result set which doesn't have a matching type in the model, in this scenario, you have to create a complex type to map the result. If you don't want to do that, @darnold924's solution is a workaround, you can call the stored procedure directly in Entity Framework. Please refer to the link, I think it could help you to solve the issue.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, March 14, 2012 5:29 AM
    Moderator
  •   Thank you both!

    Wednesday, March 14, 2012 12:30 PM