Entity Framework .Net 6 with SQL Server stored procedure

This is my first attempt at using EF to call a SQL Server stored procedure passing in parameters. This is an update stored procedure that I'm testing, and I keep getting an error

Procedure or function 'Employee_Update' expects parameter '@EmployeeId', which was not supplied

If I put a breakpoint in the code where it's calling the stored procedure, I can see the parameters and that they are populated properly. I've also copied and pasted the parameter names between the stored procedure and my code to make sure I didn't misspell something.

Everything I've read about and see in the code suggests that it should work. I've also confirmed the stored procedure code is good by executing the stored procedure directly from SQL Server Management Studio, it works just fine.

Anyone have a suggestion on what I can try or see something that looks like it wouldn’t work?

List<SqlParameter> sqlParms = new List<SqlParameter>
    {
        new SqlParameter { ParameterName = "@EmployeeId", Value = employee.EmployeeID },
        new SqlParameter { ParameterName = "@FirstName ", Value = employee.FirstName },
        new SqlParameter { ParameterName = "@LastName", Value = employee.LastName},
        new SqlParameter { ParameterName = "@MiddleInitial", Value = employee.MiddleName }
    };
db.Database.ExecuteSqlRaw("EXEC dbo.Employee_Update", sqlParms.ToArray());

Update

Stored Proc Parameters

ALTER PROCEDURE [dbo].[Employee_Update]
(
    @EmployeeId varchar(8),
    @FirstName varchar(40),
    @LastName varchar(40),
    @MiddleInitial char(1)
)

CodePudding user response：

Even though you created the stored procedure with ordered parameters, EF's ExecuteSQLRaw() function appears to require you to pass the parameters via SQL parameterization in the query string you're providing to the function.

Also ensure that your parameters are in the same order as your SQLParameter[] array.

Try modifying your execute line to this:

db.Database.ExecuteSqlRaw("EXEC dbo.Employee_Update @EmployeeId, @FirstName, @LastName, @MiddleInitial", sqlParms.ToArray());

Parameter Ordering Entity Framework Core passes parameters based on the order of the SqlParameter[] array. When passing multiple SqlParameters, the ordering in the SQL string must match the order of the parameters in the stored procedure's definition. Failure to do this may result in type conversion exceptions and/or unexpected behavior when the procedure is executed.

See the following link for more info: https://docs.microsoft.com/en-us/ef/core/querying/raw-sql