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

[jamesgurung]

This code calls a Stored Procedure using two list parameters (IdList is a user-defined table type).

```c#
private async Task> GetResultsAsync(int[] students, int[] objectives)
{
var studentsParam = BuildListParameter("@p0", students);
var objectivesParam = BuildListParameter("@p1", objectives);

return await Db.Set()
.FromSqlInterpolated($"GetResults {studentsParam}, {objectivesParam}").ToListAsync();
}

private SqlParameter BuildListParameter(string paramName, IEnumerable list)
{
var table = new DataTable();
table.Columns.Add(new DataColumn("Id", typeof(int)));

foreach (var id in list)
{
var row = table.NewRow();
row[0] = id;
table.Rows.Add(row);
}

return new SqlParameter(paramName, table)
{
TypeName = "IdList",
SqlDbType = SqlDbType.Structured
};
}

Since updating to EF Core 3 Preview 7, this has started throwing an exception.

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

Stack trace:

Microsoft.Data.SqlClient.SqlParameterCollection.ValidateType(object value)
Microsoft.Data.SqlClient.SqlParameterCollection.Add(object value)
Microsoft.EntityFrameworkCore.Storage.Internal.RawRelationalParameter.AddDbParameter(DbCommand command, object value)
Microsoft.EntityFrameworkCore.Storage.Internal.CompositeRelationalParameter.AddDbParameter(DbCommand command, object value)
Microsoft.EntityFrameworkCore.Storage.Internal.RelationalParameterBase.AddDbParameter(DbCommand command, IReadOnlyDictionary parameterValues)
Microsoft.EntityFrameworkCore.Storage.RelationalCommand.CreateCommand(IRelationalConnection connection, IReadOnlyDictionary parameterValues)
Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.Relational.Query.Pipeline.RelationalShapedQueryCompilingExpressionVisitor+AsyncQueryingEnumerable+AsyncEnumerator.MoveNextAsync()
Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync(IQueryable source, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync(IQueryable source, CancellationToken cancellationToken)
MyProject.Controllers.TrackingController.GetResultsAsync(int[] students, int[] objectives) in TrackingController.cs
```

Further technical details

EF Core version: 3.0.0-preview7.19362.6
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10 Pro
IDE: Visual Studio 2019 Version 16.3 Preview 1

[divega]

According to https://twitter.com/jamesgurung/status/1155623802681876480?s=21 this could be a recent regression.

[jamesgurung]

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

[BundeAmos]

public void ExecuteCommand(string ProcedureName, SqlParameter[] para)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = ProcedureName;
cmd.Connection = sqlconnection;
if (para != null)
{
cmd.Parameters.AddRange(para);
}
cmd.ExecuteNonQuery();
}
}

[jefflaia]

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

This is the correct solution! It solved my issue.

[andreyciocan]

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

Worked for me too

[zez9787]

Make sure to include the Microsoft.Data.SqlClient Nuget package as well.

[RA-Work-A]

So, I'm getting the same issue. Only I'm on 2.1.x. I've also included

using Microsoft.Data.SqlClient; in my class.

sample code looks like this:

SqlParameter userId = sqlCmd.CreateParameter(); userId.ParameterName = @"@userID"; userId.DbType = DbType.Guid; userId.Direction = ParameterDirection.Input; userId.Value = userID; userId.Size = 450; sqlCmd.Parameters.Add(userID);

[zez9787]

@RA-Work-A

I believe before EF Core 3, you should still be using using System.Data.SqlClient;

[RA-Work-A]

@zez9787 Well, I feel silly now. I was able to get it to work. I was passing in the wrong parameter!

[ericklind]

I'm upgrading some code to .NET Core 3.1.1 Everything was going well until I hit a seemingly random error. So far, I keep getting this error (the title of this issue) on only one call to the database. I don't get it on the others, which are pretty much the same.

Here's the code:

 return _ctx.Set<TEntity>()
                .FromSqlRaw(sqlString)
                .AsEnumerable<TEntity>()
                .ToList();

This one throw the Exception:
EXECUTE MyDB.dbo.GetAdminRoles @Username='[email protected]'

This one works just fine:
EXECUTE MyDb.dbo.GetAPIUsers @Username='[email protected]'

I've added:
using Microsoft.Data.SqlServer

I'm pretty much dead in the water at this point. I don't know how to get this back up and running. any ideas?

[ErikEJ]

You should add a SqlParameter to your call, not use verbose text.

[ericklind]

I just realized I may be posting this on the wrong issue. Should I post elsewhere? I was and am still getting this error message:
FromSqlRaw or FromSqlInterpolated was called with non-composable SQL and with a query composing over it. Consider callingAsEnumerableafter the FromSqlRaw or FromSqlInterpolated method to perform the composition on the client side.

You should add a SqlParameter to your call, not use verbose text.

I tried that and had the same results. This one works:
EXECUTE MyDB.dbo.GetAPIUsers @Username = {0},

This one does not:
EXECUTE MyDB.dbo.GetAdminRoles @Username = {0}

The parameters for both are:
{Microsoft.Data.SqlClient.SqlParameter[1]} [0]: {Username}

This is the adjusted code:

return _ctx.Set<TEntity>()
                .FromSqlRaw(sqlString, parameters)
                .AsEnumerable<TEntity>()
                .ToList();

[ErikEJ]

@ericklind You are only sharing fragments - please share a complete repro.

I think your sql string should be:

EXECUTE MyDB.dbo.GetAdminRoles

And then create a named SqlParameter @UserName

[ericklind]

I don't understand what you mean. I have a SQL Parameter with that:
{Microsoft.Data.SqlClient.SqlParameter[1]} [0]: {Username}

Here are the input parameters:

var parameters = new List<SqlParameter>() { 
     new SqlParameter { ParameterName = 'Username, Value = '[email protected]' }
};
var proc = 'MyDB.dbo.GetAdminRoles';

Here's the method:

public List<TEntity> GetFromSql(string proc, List<SqlParameter> sqlParams)
{
      string sqlString = string.Format("EXECUTE {0} ", proc);
      var parameters = sqlParams.Where(p => p.Value.ToString() != string.Empty && p.Value != DBNull.Value).Select(p => p).ToArray();

        return _ctx.Set<TEntity>()
                .FromSqlRaw(sqlString, parameters)
                .AsNoTracking()
                .AsEnumerable<TEntity>()
                .ToList();
}

I still get the same issue. All the others work, one does not.

[ErikEJ]

And if you run GetAdminRoles in SSMS or ADS?

[ericklind]

And if you run GetAdminRoles in SSMS or ADS?

I get 73 rows back with
EXECUTE MyDB.dbo.GetAdminRoles @Username = '[email protected]'

[ericklind]

@ErikEJ - I found another stored proc that is having the same issue. It uses the Roles table just as the GetAdminRoles does. That is the only thing that they have in common. Is there possibly a table setting on SQL Server that could be causing issues?

[ErikEJ]

Please post a full, runnable repro, I see odd typos in your code fragments.

[kdehia]

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

I am on EF core 5.0 and this worked for me too