Entity framework database-first approach stored procedure with output parameter and result set

Question

I am using database-first approach for Entity Framework in my ASP.NET project. I have a stored procedure that has an output parameter @TotalCount and it returns a result set using a SELECT statement.

When I add the stored procedure in my MyDbContext.edmx file, the MyDbContext.cs has a function that returns an int (probably the output parameter) that is automatically generated for the stored procedure.

How can I access both the output parameter as well as the result set with this approach?

The stored procedure snippet is given below. Basically I am trying to do pagination in the stored procedure.

CREATE PROCEDURE [dbo].[sp_GetDetailsForStudent]
    @StudentId BIGINT,
    //....
    @OrderBy NVARCHAR(max),
    @Page INT OUTPUT,
    @Items INT = 200,
    @TotalCount INT OUTPUT
    //.....

    SET @SortSql = N'SELECT * FROM #tmpTable'
    //......    
    EXEC sp_executesql @SortSql;

In the MyDbContext.cs file

 public virtual int sp_GetDetailsForStudent(parameters ...){
     //......
     return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("sp_GetDetailsForStudent", input parameters..., totalCount);
 }

Hope this [link](http://stackoverflow.com/questions/5881359/i-cannot-get-the-output-parameter-when-use-function-import-by-entity-framework/7183184#7183184) will help you. — Devansh Nigam - SA, Sep 02 '16 at 05:56
Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Sep 02 '16 at 06:31

score 0 · Answer 1 · edited May 09 '19 at 18:12

First you need to update your EDMX model so that your function

public virtual int sp_GetDetailsForStudent(parameters ...){....}

Should look like

public virtual ObjectResult<YourResult_type> sp_GetDetailsForStudent(parameters ...){....}

For refreshing EDMX model go hear

You might need to choose "Create New Complex Type" option instead of "Update" as suggested in above link.

Then to fetch the result you can use below code

ObjectParameter outParam1 = new ObjectParameter("outParameter", typeof(long));
var db = new YourDbContext();
var res = db.sp_GetDetailsForStudent(parameter1,parameter2,outParam1 );
foreach (YourResult_type item in res)
{
    var prop1= item.property1;
}
var outParam1Value= outParam1.Value;

Entity framework database-first approach stored procedure with output parameter and result set

1 Answers1