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...)

回答1:

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:

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:

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:

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