ExecutesSqlRaw throwing weird error about "SqlParameterCollection only accepts non-null SqlParameter type objects"

[BobbyCannon]

I'm trying to execute a raw sql statement with a sql parameter that is not null but I'm getting an odd error.

"UPDATE [a] SET [a].[IsDeleted] = @param_0 FROM [dbo].[Addresses] AS [a] WHERE [a].[IsDeleted] = 0"

"SqlParameterCollection only accepts non-null SqlParameter type objects"

Steps to reproduce

Further technical details

EF Core version: 3
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET Core 3.0
Operating system: Windows 10 1903 - 18362.356
IDE: Visual Studio 2019 16.3.2

[usmalik78]

Just upgraded to .Net Core 3.0 and EF Core 3.0 and ran into this exact issue with ExecuteSqlRaw/FromSqlRaw. A temporary workaround is to use ExecuteSqlInterpolated/FromSqlInterpolated and provide it individual strings instead of SqlParameter or SqlParameterCollection. This workaround will still create parameterized statements so it is still safe. However, if you end up executing a stored procedure with either of the "Interpolated" methods, make sure that your stored procedure parameter order is the exact same as your statement parameter order (parameter names are irrelevant). For this reason, we are going to revert to EF Core 2.2 until this issue is fixed because we primarily invoke stored procedures and don't want to take this chance.

[snerks]

Have you tried:

Replace:
using System.Data.SqlClient;
with
using Microsoft.Data.SqlClient;

[BobbyCannon]

@snerks, how in the world did you know that? I just swapped nuget packages and change the using statement and everything worked.

[roji]

EF Core 3.0 changed its dependency from System.Data.SqlClient to the newly-released Microsoft.Data.SqlClient. This doesn't affect most scenarios, but if you're passing SqlParameter instances to raw SQL methods, you indeed need to make sure you're passing the right type.

Opened dotnet/EntityFramework.Docs#1815 to document.

[snerks]

@jamesgurung solved it:

#16812 (comment)

[roji]

Duplicate of #16812