none
Create a Model from a Stored Procedure RRS feed

  • Question

  • Can someone tell me the step by step process to automatically generate a model from the results of a stored procedure?
    Friday, September 30, 2011 8:35 PM

Answers

  • jmcpsd,

    I don't know of a way to generate a model (entity) from a stored proc.  You can however map the results of a stored procedure to an Entity you've created.  You would need to first create an entity in the EDMX designer (right click and do Add/Entity) and give it properties that match your stored proc results.  Then, once you have an entity (assume you called it MyEntity) do this:

    1.  First of all, add your stored procedure to the edmx (update model from database, select the sp, etc).

    2.  When viewing the edmx, go to the Model Browser window and dbl click on the Function Import related to your stored procedure.

    3.  In the section "Returns a Collection Of" select Entities and select your entity you created earlier here.  Click "Get Column Information" to make sure EF can evaluate your stored procedure correctly and see the columns that it's returning. 

    4.  Now, single click on the Function Import related to your stored proc again.  View the "Mapping Details" to make sure they look right.

    5.  Now in code you should be able to execute the stored proc and get back an ObjectResult<MyEntity> list of entities when you execute the stored proc.  Write code similiar to this:

    var results = MyContext.usp_MyProc(1, 5);

    results should be an ObjectResult<MyEntity> list.

    Here is another tutorial on how to map to a custom Entity:

    http://blogs.microsoft.co.il/blogs/gilf/archive/2009/03/13/mapping-stored-procedure-results-to-a-custom-entity-in-entity-framework.aspx


    Tom Overton
    • Edited by Tom_Overton Friday, September 30, 2011 9:10 PM
    • Marked as answer by jmcpsd Friday, September 30, 2011 9:23 PM
    Friday, September 30, 2011 9:08 PM

All replies

  • jmcpsd,

    I don't know of a way to generate a model (entity) from a stored proc.  You can however map the results of a stored procedure to an Entity you've created.  You would need to first create an entity in the EDMX designer (right click and do Add/Entity) and give it properties that match your stored proc results.  Then, once you have an entity (assume you called it MyEntity) do this:

    1.  First of all, add your stored procedure to the edmx (update model from database, select the sp, etc).

    2.  When viewing the edmx, go to the Model Browser window and dbl click on the Function Import related to your stored procedure.

    3.  In the section "Returns a Collection Of" select Entities and select your entity you created earlier here.  Click "Get Column Information" to make sure EF can evaluate your stored procedure correctly and see the columns that it's returning. 

    4.  Now, single click on the Function Import related to your stored proc again.  View the "Mapping Details" to make sure they look right.

    5.  Now in code you should be able to execute the stored proc and get back an ObjectResult<MyEntity> list of entities when you execute the stored proc.  Write code similiar to this:

    var results = MyContext.usp_MyProc(1, 5);

    results should be an ObjectResult<MyEntity> list.

    Here is another tutorial on how to map to a custom Entity:

    http://blogs.microsoft.co.il/blogs/gilf/archive/2009/03/13/mapping-stored-procedure-results-to-a-custom-entity-in-entity-framework.aspx


    Tom Overton
    • Edited by Tom_Overton Friday, September 30, 2011 9:10 PM
    • Marked as answer by jmcpsd Friday, September 30, 2011 9:23 PM
    Friday, September 30, 2011 9:08 PM
  • Thank you, Tom!
    Friday, September 30, 2011 9:22 PM