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

possible duplicate of [Getting SCOPE\_IDENTITY from SQL Server on Insert](http://stackoverflow.com/questions/4918574/getting-scope-identity-from-sql-server-on-insert) — Grax32, Aug 27 '13 at 17:41
Are you using ExecuteNonQuery when you should be using ExecuteScalar? — Grax32, Aug 27 '13 at 17:42
I've taken your answer out of the question and added as a wiki answer below. If you want to do this yourself let me know and I will delete the wiki answer — Liam, Jul 12 '17 at 10:47

score 3 · Accepted Answer · answered Aug 27 '13 at 17:43

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

score 0 · Answer 2 · edited Jul 12 '17 at 10:48

0

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

edited Jul 12 '17 at 10:48

Liam

27,717
28
128
190

answered Aug 27 '13 at 18:01

Bassam Alugili

16,345
7
52
70

1

This is very likely a good solution, however I marked the other solution as the answer bc it solved my problem and I didn't have to change the way I was calling the stored proc (w EF DbContext) - all I had to do was add an out parameter to the stored procedure and C# code. – Dudeman3000 Aug 27 '13 at 18:29

score 0 · Answer 3 · answered Jul 12 '17 at 10:47

_{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);

score 0 · Answer 4 · answered Mar 12 '21 at 04:49

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.

Entity Framework and SCOPE_IDENTITY

4 Answers4

Linked

Related