Error trying to execute update stored procedure using entity framework core

Question

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.

I had them originally and took them out to try and mimic how you would run it in SQL. — Jhorra
– Jhorra, Commented May 4, 2017 at 22:18
Are you sure AdmissionId, assignments.UserId, assignments.PracticeId are all int? From the error it looks like one of them is and integer array int[] — degant
– degant, Commented May 4, 2017 at 22:21
You're creating int array with the three parameter values and passing that as a single parameter, instead of passing individual int parameters. That's the cause of the error message. — STLDev
– STLDev, Commented May 4, 2017 at 22:32

STLDev · Accepted Answer · 2017-05-04 23:40:19Z

5

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

edited May 4, 2017 at 23:40

answered May 4, 2017 at 23:06

STLDev

6,17829 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jhorra Over a year ago

One small change needed to your answer, you have to put a comma between the passed parameters in your command.

STLDev Over a year ago

Of course. Copy and paste error (mine, not yours). It's fixed now.

Jhorra · Accepted Answer · 2017-05-04 22:52:22Z

1

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

answered May 4, 2017 at 22:52

Jhorra

6,35121 gold badges76 silver badges124 bronze badges

Collectives™ on Stack Overflow

Error trying to execute update stored procedure using entity framework core

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related