如何使用EF Core获取从存储过程插入的记录的id值?

Question

我正在使用EF Core 5，并且在获取插入表的记录的userId时遇到了麻烦。我试过不同的方法，但都没有成功。我得到的是-1，我知道这是受影响的行。我似乎无法检索UserID，它被声明为存储过程的OUTPUT参数。

存储过程:

ALTER PROCEDURE [dbo].[User.Save]
(@Return_Code  INT OUTPUT,
@Error_Description_Code NVARCHAR(50) OUTPUT,
@idCallerSite INT = 0, --default if not specified
@idCaller     INT = 0,
@idUser       INT OUTPUT, 
@firstName     NVARCHAR(50),
@middleName    NVARCHAR(50),
@lastName      NVARCHAR(50),
@preferredName NVARCHAR(50),
@username      NVARCHAR(512),
@password      NVARCHAR(512),
@isActive      BIT,
@isSalesperson BIT,
@userPreferences NVARCHAR(512),
@phoneNumber     NVARCHAR(25),
@phoneFax        NVARCHAR(25),
@email           NVARCHAR(255),
@photo           NVARCHAR(255),
@lastEditedBy    INT)
AS
BEGIN
SET NOCOUNT ON
DECLARE @idPermission INT  -- define the permission required to perform this function
SET @idPermission = 141

/* 
validate uniqueness
*/
IF (SELECT COUNT(1)
FROM Users
WHERE SiteID = @idCallerSite
AND (username = @username)
AND (@idUser IS NULL OR @idUser <> UserID)) > 0
BEGIN
SET @Return_Code = 2
SET @Error_Description_Code = 'UserSave_UserNotUnique'
RETURN 1 
END
/*
insert/update the user
*/
IF (@idUser = 0 OR @idUser IS NULL)
BEGIN
-- insert the new user
INSERT INTO Users (SiteID, firstName, middleName, lastName,
preferredName, username,
HashedPassword, isActive, IsSalesperson,
UserPreferences, PhoneNumber, FaxNumber,
EmailAddress, Photo, LastEditedBy)
VALUES (@idCallerSite, @firstName, @middleName, @lastName,
@preferredName, @username, 
dbo.GetHashedString('SHA1', @password), @isActive, @isSalesperson,
@userPreferences, @phoneNumber, @phoneFax,
@email, @photo, @lastEditedBy)
/*
get the new user's id
*/
SET @idUser = SCOPE_IDENTITY()
SELECT @idUser
-- return successfully
SET @Return_Code = 0 --(0 is 'success')
SET @Error_Description_Code = ''
END

控制器调用

var retCode = new SqlParameter { ParameterName = "@Return_Code", Value = null, SqlDbType = (SqlDbType.Int), Direction = System.Data.ParameterDirection.Output };
var errorDesc = new SqlParameter { ParameterName = "@Error_Description_Code", Value = null, SqlDbType = SqlDbType.NVarChar, Size = 50, Direction = System.Data.ParameterDirection.Output };
var idCallerSite = new SqlParameter { ParameterName = "@idCallerSite", Value = 1, SqlDbType = (SqlDbType.Int), Direction = System.Data.ParameterDirection.Input };
var idCaller = new SqlParameter { ParameterName = "@idCaller", Value = 0, SqlDbType = (SqlDbType.Int), Direction = System.Data.ParameterDirection.Input };
var idUser = new SqlParameter { ParameterName = "@idUser", Value = user.UserID, SqlDbType = (SqlDbType.Int), Direction = System.Data.ParameterDirection.Output };
var fName = new SqlParameter { ParameterName = "@firstName", Value = user.FirstName.ToString(), SqlDbType = SqlDbType.NVarChar, Size = 50, Direction = System.Data.ParameterDirection.Input };
var mName = new SqlParameter { ParameterName = "@middleName", Value = user.MiddleName.ToString(), SqlDbType = SqlDbType.NVarChar, Size = 50, Direction = System.Data.ParameterDirection.Input };
var lName = new SqlParameter { ParameterName = "@lastName", Value = user.LastName.ToString(), SqlDbType = SqlDbType.NVarChar, Size = 50, Direction = System.Data.ParameterDirection.Input };
var pName = new SqlParameter { ParameterName = "@preferredName", Value = user.PreferredName.ToString(), SqlDbType = SqlDbType.NVarChar, Size = 50, Direction = System.Data.ParameterDirection.Input };
var userName = new SqlParameter { ParameterName = "@username", Value = user.UserName.ToString(), SqlDbType = SqlDbType.NVarChar, Size = 512, Direction = System.Data.ParameterDirection.Input };
var password = new SqlParameter { ParameterName = "@password", Value = user.HashedPassword.ToString(), SqlDbType = SqlDbType.NVarChar, Size = 512, Direction = System.Data.ParameterDirection.Input };
var isActive = new SqlParameter { ParameterName = "@isActive", Value = user.IsActive, SqlDbType = (SqlDbType.Bit), Direction = System.Data.ParameterDirection.Input };
var isSalesperson = new SqlParameter { ParameterName = "@isSalesperson", Value = user.IsSalesperson, SqlDbType = (SqlDbType.Bit), Direction = System.Data.ParameterDirection.Input };
var userPref = new SqlParameter { ParameterName = "@userPreferences", Value = user.UserPreferences.ToString(), SqlDbType = SqlDbType.NVarChar, Size = 512, Direction = System.Data.ParameterDirection.Input };
var phone = new SqlParameter { ParameterName = "@phoneNumber", Value = user.PhoneNumber.ToString(), SqlDbType = SqlDbType.NVarChar, Size = 25, Direction = System.Data.ParameterDirection.Input };
var fax = new SqlParameter { ParameterName = "@phoneFax", Value = user.FaxNumber.ToString(), SqlDbType = SqlDbType.NVarChar, Size = 25, Direction = System.Data.ParameterDirection.Input };
var email = new SqlParameter { ParameterName = "@email", Value = user.EmailAddress.ToString(), SqlDbType = SqlDbType.NVarChar, Size = 255, Direction = System.Data.ParameterDirection.Input };
var photo = new SqlParameter { ParameterName = "@photo", Value = user.Photo.ToString(), SqlDbType = SqlDbType.NVarChar, Size = 255, Direction = System.Data.ParameterDirection.Input };
var lastEditedBy = new SqlParameter { ParameterName = "@lastEditedBy", Value = user.LastEditedBy, SqlDbType = (SqlDbType.Int), Direction = System.Data.ParameterDirection.Input };
var _user = await _db.Database.ExecuteSqlRawAsync("EXEC [dbo].[User.Save] @Return_Code, @Error_Description_Code, @idCallerSite, @idCaller, @idUser OUT, @firstName, @middleName, @lastName, @preferredName, @username, @password, @isActive, @isSalesperson, @userPreferences, @phoneNumber, @phoneFax, @email, @photo, @lastEditedBy", retCode, errorDesc, idCallerSite, idCaller, idUser, fName, mName, lName, pName, userName, password, isActive, isSalesperson, userPref, phone, fax, email, photo, lastEditedBy);
return Ok(_user);

我使用.FromSql，得到以下错误:

var _user = _db.Users.FromSqlRaw("EXEC [dbo].[User.Save] @Return_Code, @Error_Description_Code, @idCallerSite, @idCaller, @idUser OUT, @firstName, @middleName, @lastName, @preferredName, @username, @password, @isActive, @isSalesperson, @userPreferences, @phoneNumber, @phoneFax, @email, @photo, @lastEditedBy", retCode, errorDesc, idCallerSite, idCaller, idUser, fName, mName, lName, pName, userName, password, isActive, isSalesperson, userPref, phone, fax, email, photo, lastEditedBy);

系统。InvalidOperationException: FromSql操作的结果中不存在所需的列UserID。

at Microsoft.EntityFrameworkCore.Query.Internal.FromSqlQueryingEnumerable1.BuildIndexMap(IReadOnlyList1 columnNames, DbDataReader dataReader)
at Microsoft.EntityFrameworkCore.Query.Internal.FromSqlQueryingEnumerable1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func4 operation, function4 verifySucceeded, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Query.Internal.FromSqlQueryingEnumerable1. asyncenumerator . movenextasync ()
at microsoft . aspnetcore . mvc . infrastruct.asyncenumerablereader .ReadInternal[T](Object value)
at Microsoft.AspNetCore.Mvc.Infrastructure.AsyncEnumerableReader。ReadInternal[T](Object value)
at Microsoft.AspNetCore.Mvc.Infrastructure.ObjectResultExecutor。ExecuteAsyncEnumerable(ActionContext context, ObjectResult result, Object asyncEnumerable, Func ' 2 reader)
在Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker。__await |29_0[TFilter,TFilterAsync](ResourceInvoker invoker，任务lastTask，状态next，范围范围，对象状态，布尔值isCompleted)
at microsoft . aspnetcore . mvc . infrastrucstruct .ResourceInvoker。Rethrow(ResultExecutedContextSealed context)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.ResultNext[TFilter,TFilterAsync](State&接下来,Scope&范围,Object&状态,Boolean&
在Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker。g__Awaited | 27 _0 (ResourceInvoker调用程序、任务lastTask状态下,空间范围、对象状态,布尔isCompleted)在Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker
。g__Awaited | 19 _0 (ResourceInvoker调用程序、任务lastTask状态下,空间范围、对象状态,布尔isCompleted)在Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker
。__await |17_0(ResourceInvoker invoker, Task Task, IDisposable作用域)
在Microsoft.AspNetCore.Routing.EndpointMiddleware。
在Microsoft.AspNetCore.Authorization.AuthorizationMiddleware. g__AwaitRequestTask|6_0(Endpoint Endpoint, Task requestTask, ILogger logger)。调用(HttpContext context)
在swashbuckle . aspnetcore . swaggerui . swaggerumiddleware。在Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware中调用(HttpContext HttpContext)
调用(HttpContext HttpContext, ISwaggerProvider swaggerProvider)
在Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware。调用(HttpContext上下文)

标题:

Accept: */*
Accept-Encoding: gzip, deflate, br
Accept-Language: en-US,en;q=0.9
Connection: keep-alive
Content-Length: 301
Content-Type: application/json
Cookie: fblo_329420117573382=y
Host: localhost:62374
Referer: http://localhost:62374/swagger/index.html
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.124 Safari/537.36
sec-ch-ua: " Not;A Brand";v="99", "Google Chrome";v="91", "Chromium";v="91"
sec-ch-ua-mobile: ?0
Origin: http://localhost:62374
Sec-Fetch-Site: same-origin
Sec-Fetch-Mode: cors
Sec-Fetch-Dest: empty

Answer 1

我修复了对所有字段执行select语句，然后从结果集中提取UserID的问题。谢谢你@ programman的帮助。是你的建议帮助了我。

Answer 2

您只能使用输出参数idUser来获取记录Id

_db.Database.ExecuteSqlRaw(....);
var id=0;
if (idUser.Value != DBNull.Value)
{
id = (int) idUser.Value;
}
return Ok(id);

和修复存储过程。删除SELECT @idUser，只留下

SET @idUser = SCOPE_IDENTITY()

如果sp包含SELECT Database。

Answer 3

Entity Framework要求至少存在主键，因此您需要执行

SET @idUser = SCOPE_IDENTITY();
SELECT UserID = @idUser;

然而，这只能得到一个字段，它不会得到任何其他列。要获取其他列，只需OUTPUT(不需要连接)

INSERT INTO Users (SiteID, firstName,
.........
OUTPUT inserted.ID, inserted.SiteID, inserted.firstName
.........
VALUES (@idCallerSite, @firstName
.........

请注意，用于调用存储过程的语法并不理想，因为它依赖于参数位置而不是名称。如果使用EXEC [dbo].[User.Save] @Return_Code = @Return_Code OUTPUT, @Error_Description_Code = @Error_Description_Code, @idCallerSite = @idCallerSite ...，您还需要在输出参数上指定OUTPUT，如下所示。