CODEWITHSUNDEEP
c#sqlentity-frameworkentity-framework-4
Hadas
Hadas

Reputation: 10384

Convert stored procedure result to model object

I have a stored procedure In my asp.net mvc3 application using Entity Framework:

CREATE PROCEDURE dbo.MinMax
AS

DECLARE @T1 TABLE(MinColorsId int,MAXColorsId int)
INSERT @T1 
select MIN(ColorsId) as MinColorsId,MAX(ColorsId) as MAXColorsId
from DiamondInfoes 
SELECT * FROM @T1
RETURN

In my model I have same properties names as the procedure table result :

public class colorModel
{
    [Display(Name = "MinColorsId")]
    public float MinColorsId { get; set; }

    [Display(Name = "MaxColorsId")]
    public float MaxColorsId { get; set; }
}

In the controller I run the stored procedure :

private _ModelContainer m_db = new _ModelContainer();
var minmax = m_db.MinMax();

I want to set the colorModel with the minmax that contains the procedure result? (by short way, I have many properties...)

Upvotes: 2

Views: 5304

Answers (1)

marc_s
marc_s

Reputation: 755531

First of all, you can simplify your stored procedure to be:

CREATE PROCEDURE dbo.MinMax
AS
   SELECT  
       MIN(ColorsId) AS MinColorsId,
       MAX(ColorsId) AS MaXColorsId
   FROM dbo.DiamondInfoes

There's absolutely no need nor any point in having that table variable - it only puts strain on your TempDB.

Next - if you're on .NET 4.0 and using the database-first approach - you can import that stored procedure into your EDMX model from the wizard:

enter image description here

Next, go to the Model Browser in Visual Studio and find your newly imported stored procedure in the physical "store" section of your model. On your stored procedure, right-click and pick Add Function Import:

enter image description here

Now a dialog box pops up which gives you the opportunity to pick what this stored procedure returns - in your case, you said you already have a type that represents that result - so in your case, you should find this type in the dropdown for Entities - pick it and close the dialog:

enter image description here

You should now have a method on your ObjectContext-derived class that represents this stored procedure, and returns an entity of your chosen type.

Upvotes: 7

Related Questions