嘗試使用實體框架核心執行更新存儲過程時出錯

asp.net-core c# entity-framework-core sql-server stored-procedures

我正在嘗試在ASP.Net Core應用程序中運行以下命令。

await _context.Database.ExecuteSqlCommandAsync(
           "EXEC AdmissionConsultEndCurrentAndPending @PracticeId @UserId @AdmissionId", 
           parameters: new[] { AdmissionId, assignments.UserId, assignments.PracticeId });

我也試過這些組合的實際命令

EXEC AdmissionConsultEndCurrentAndPending @PracticeId, @UserId, @AdmissionId
AdmissionConsultEndCurrentAndPending, @PracticeId, @UserId, @AdmissionId

傳遞的三個值是三個整數。萬一這裡重要的是存儲過程

ALTER PROCEDURE [dbo].[AdmissionConsultEndCurrentAndPending]
    @AdmissionId INT,
    @UserId INT,
    @PracticeId INT
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE
        AdmissionConsults 
    SET
        CurrentConsult = 0
    WHERE
        AdmissionId = @AdmissionId AND
        PracticeId = @PracticeId AND
        CurrentConsult = 1
END

當我運行此操作時,我收到以下錯誤: No mapping to a relational type can be found for the CLR type 'Int32[]'.

我不確定這個錯誤是指我作為參數傳遞的int值,還是因為它是一個更新查詢,它試圖返回受影響行數的int值。無論哪種方式,我還沒有讓它工作。

一般承認的答案

這是一種應該滿足您要求的方法:

var parameters = new List<SqlParameter>
                 {
                     new SqlParameter("@PracticeId", assignmentsPracticeId),
                     new SqlParameter("@UserId", assignmentsUserId),
                     new SqlParameter("@AdmissionId", AdmissionId)
                 };
await _context.Database.ExecuteSqlCommandAsync(
       "EXEC AdmissionConsultEndCurrentAndPending @PracticeId, @UserId, @AdmissionId", 
       parameters.ToArray());

熱門答案

根據評論我發現問題是我直接傳遞了值,我需要傳遞一個參數,而不是int本身。那有用的是:

SqlParameter u = new SqlParameter("@UserId", assignments.UserId);
SqlParameter a = new SqlParameter("@AdmissionId", AdmissionId);
SqlParameter pr = new SqlParameter("@PracticeId", assignments.PracticeId);
await _context.Database.ExecuteSqlCommandAsync("EXEC AdmissionConsultEndCurrentAndPending @PracticeId, @UserId, @AdmissionId",
                                    parameters: new[] { a, u, pr });


Related

許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow
許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow