CODEWITHSUNDEEP
c#sql-serverstored-proceduresasp.net-coreentity-framework-core
Jhorra
Jhorra

Reputation: 6321

Error trying to execute update stored procedure using entity framework core

I'm trying to run the following command in an ASP.Net Core app.

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

I've tried the actual command with these combinations as well

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

The three values that are passed are three integers. In case it matters here 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 though 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 is referring to the int values I am passing as parameters or perhaps since it's an update query it's trying to return an int value for the number of rows affected. Either way I've yet to get it working.

Upvotes: 3

Views: 1292

Answers (2)

STLDev
STLDev

Reputation: 6174

Here is a method that should meet 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());

Upvotes: 5

Jhorra
Jhorra

Reputation: 6321

Based on the comments I found that the issue was I was passing in the values directly, and I needed to be passing a parameter, and not the int itself. So what worked was this:

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 });

Upvotes: 1

Related Questions