实体框架和SCOPE_IDENTITY [英] Entity Framework and SCOPE_IDENTITY

问题描述

我有一个存储过程，插入到表中然后执行此行

  SET @returnVal = SCOPE_IDENTITY（）; 
  

之后，我同时尝试了两种方法：

  SELECT @returnVal 
  

和

  return @returnVal 
  

当我从Microsoft SQL Server Management Studio执行存储过程时，通过 SELECT @returnVal 得到预期的结果-选择插入数据的标识列。

但是，当我将存储过程添加到ADO.Net实体数据模型/ EntityFramework类/.edmx类并在C＃代码中执行存储过程时，我得到的值是-1返回。

是否有可能获得我想要的值，即新的标识值，返回了？

<我意识到我可以将存储过程手动绑定到模型中表的插入操作上，但这不是一个选择。每次我重新生成模型类时，插入过程太多，无法完成此手动工作。

解决方案

过程定义中参数的输出类型：

 创建过程[dbo]。[过程名称] @returnVal int输出
作为
 SET @returnVal = SCOPE_IDENTITY（）; 
  

，并且在调用存储过程时将其称为：

 声明@returnVal int 
 exec Procedurename @returnVal输出
打印@returnVal 
  

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

解决方案

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 

这篇关于实体框架和SCOPE_IDENTITY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！