Execute stored procedure in EF Core 3.0 vs 2.2

Question

I am trying to update my code to accommodate changes in EF Core 3.0, specifically the deprecation of ExecuteSqlCommand.

The following code was working in 2.2 but as stated, I need to get rid of ExecuteSqlCommand:

SqlParameter srcid = new SqlParameter("@srcCharacterId", participantApplication.CharacterId);
SqlParameter newid = new SqlParameter("@newCharacterId", newCharacterId);
SqlParameter pResult = new SqlParameter
{
    ParameterName = "@pResult",
    SqlDbType = System.Data.SqlDbType.Bit,
    Direction = System.Data.ParameterDirection.Output
};

_db.Database.ExecuteSqlCommand("pCharacterCopy @srcCharacterId, @newCharacterId, @pResult OUTPUT", srcid, newid, pResult);

I've tried changing the call to ExecuteSqlRaw (leaving everything else identical) but that, although it compiles, throws the following exception at run time:

The SqlParameterCollection only accepts non-null SqlParameter type objects, not SqlParameter objects

I've checked with the debugger and none of the SqlParameter are null. I suspect my call to ExecuteSqlRaw is not formatted correctly but I cannot find any examples other than integrating calls into Linq queries which I don't need to do. I just want to fire off the call to the stored procedure and take a look at the output parameter when it is done.

Answer 1

This is most likely due to the switch to Microsoft.Data.SqlClient.

Remove the reference to the System.Data.SqlClient assembly and replace the namespaces.

using System.Data.SqlClient; => using Microsoft.Data.SqlClient;