Entity Framework and SCOPE_IDENTITY

Question

I have a stored procedure that inserts into a table then executes this line

SET @returnVal = SCOPE_IDENTITY();

and after that I've tried both:

SELECT @returnVal

and

return @returnVal

When I execute the stored procedure from Microsoft SQL Server Management Studio, I get the expected result with SELECT @returnVal - the identity column for the inserted data is selected.

However when I add the stored procedure to my ADO.Net Entity Data Model / EntityFramework class / .edmx class and execute the stored procedure in my C# code, I get the value -1 returned without fail.

Is it possible to get the value that I want, the new identity value, returned?

I realize that I could manually bind the stored procedure to the insert action of the table in my model - but this is not an option. There are far too many insert procedures to do this manual work every time I regenerate my model class(es).

Answer 1

Declare a output type of parameter in your procedure definition:

 create procedure [dbo].[Procedurename] @returnVal int output
 as 
 SET @returnVal = SCOPE_IDENTITY();

and while calling the stored procedure call it as:

 declare @returnVal int
 exec Procedurename @returnVal output
 print @returnVal 

Answer 2

context.Database.ExecuteSqlCommand return the command executing result and not the query result. If you need to get data than use context.Database.SqlQuery.

SET @ReturnVal=SCOPE_IDENTITY() and then use the select.

Example: How to use DbContext.Database.SqlQuery<TElement>(sql, params) with stored procedure? EF Code First CTP5

Answer 3

If you open the edmx file and right click on the function import in the model browser you can tell entity framework that the stored procedure returns a collection of scalars. Then you can essentially call the sproc with mycontext.mysproc().Single() to get the ID.

Answer 4

_{taken from the OPs question}

Adding an output parameter worked (answer marked below).

The stored procedure signature looks like this now:

My stored procedure signature now looks like this:

CREATE PROCEDURE SP_MyStoreProc ([Multiple Parameters], @returnVal int output)

The last line of the stored procedure is:

return @returnVal

My C# code looks like this now: (db is an instance of my dbContext class)

System.Data.Objects.ObjectParameter identityParameter = 
new System.Data.Objects.ObjectParameter("returnVal", 0);

db.SP_MyStoredProc([Multiple Parameters], identityParameter);

int myNewIdentity = Convert.ToInt32(identityParameter.Value);