System.InvalidCastException: 'The SqlParameterCollection only accepts non-null SqlParameter type objects, not SqlParameter objects.'

Question

I migrated my project from ASP.NET Core 2.2 to ASP.NET Core 3.0. Now I get this exception. In ASP.NET Core 2.2 it was using FromSql(); now it is using FromSqlRaw(). I am calling my procedure using Entity Framework Core.

SqlParameter Username = new SqlParameter
            {
                ParameterName = "USERNAME",
                SqlDbType = SqlDbType.NVarChar,
                Value = user.Username,
                Direction = ParameterDirection.Input,
                Size = 50
            };

SqlParameter Password = new SqlParameter
            {
                ParameterName = "PASSWORD",
                SqlDbType = SqlDbType.NVarChar,
                Value = user.Password,
                Direction = ParameterDirection.Input,
                Size = 50
            };

SqlParameter msgOut = new SqlParameter
            {
                ParameterName = "MSG",
                SqlDbType = SqlDbType.NVarChar,
                Direction = ParameterDirection.Output,
                Size = 1000
            };

SqlParameter statusOut = new SqlParameter
            {
                ParameterName = "STATUS",
                SqlDbType = SqlDbType.Int,
                Direction = ParameterDirection.Output
            };

var sql = @"EXEC PRC_USERS_LOGIN
                        @USERNAME, 
                        @PASSWORD, 
                        @MSG OUT, 
                        @STATUS OUT";

Users resultUser = new Users();
resultUser = ctx.Users.FromSqlRaw(sql, Username, Password, msgOut, statusOut)
                      .FirstOrDefault();

score 37 · Accepted Answer · answered Oct 03 '19 at 05:16

37

This code worked after replacing

System.Data.SqlClient.SqlParameter

to

Microsoft.Data.SqlClient.SqlParameter

and

FirstOrDefault();

to

ToList();

answered Oct 03 '19 at 05:16

Muhammad Aftab

1,098
8
19

2

The first portion of you answer here is actually what fixes your issue not the `FirstOrDefault();` to `ToList();`. – ChronoZZ Feb 25 '20 at 16:51

score 28 · Answer 2 · answered Oct 01 '19 at 17:54

28

Fixed by changing using System.Data.SqlClient to using Microsoft.Data.SqlClient

https://github.com/aspnet/EntityFrameworkCore/issues/16812#issuecomment-516013245

answered Oct 01 '19 at 17:54

LRFalk01

961
8
17

Yes that was also the problem. Thanks, – Muhammad Aftab Oct 03 '19 at 05:57
Meanwhile I'm getting error when I'm using 'Microfoft.Data.SqlClient' namespace. but 'Sytem.Data.SqlClient' can pass null column without any error message. This is my code (https://stackoverflow.com/questions/59499253/the-sqlparametercollection-only-accepts-non-null-sqlparameter-type-objects-not) – Daleman Dec 27 '19 at 10:25

score -1 · Answer 3 · edited Jan 31 '23 at 07:13

-1

The reverse is true also: you must use System.Data.SqlClient.SqlParameters with EF 6, not Microsoft.Data.SqlClient.SqlParameters.

Also be sure to use Microsoft.SqlServer.Server.SqlDataRecords, not Microsoft.Data.SqlClient.Server.SqlDataRecords.

edited Jan 31 '23 at 07:13

MD. RAKIB HASAN

3,670
4
22
35

answered Jan 28 '23 at 00:39

Tinged Bubba

1

System.InvalidCastException: 'The SqlParameterCollection only accepts non-null SqlParameter type objects, not SqlParameter objects.'

3 Answers3

Linked