Error while trying to execute an update stored procedure using the entity framework engine
I am trying to run the following command in an ASP.Net Core application.
await _context.Database.ExecuteSqlCommandAsync(
"EXEC AdmissionConsultEndCurrentAndPending @PracticeId @UserId @AdmissionId",
parameters: new[] { AdmissionId, assignments.UserId, assignments.PracticeId });
I have also tried a valid command with these combinations
EXEC AdmissionConsultEndCurrentAndPending @PracticeId, @UserId, @AdmissionId
AdmissionConsultEndCurrentAndPending, @PracticeId, @UserId, @AdmissionId
The three transmitted values are three integers. In case it matters, this is the stored proc
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
When I run this I get the following error: No mapping to a relational type can be found for the CLR type 'Int32[]'.
I'm not sure if this error applies to the int values that I am passing as parameters, or perhaps because it is an update request that tries to return an int value for the number of rows affected. I haven't got the job yet anyway.
source to share
Here is the method that should suit your requirements:
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());
source to share
Based on the comments, I found that the problem was that I was passing values directly, and I needed to pass the parameter, not the int itself. So what happened:
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 });
source to share