When you try to execute a query with EF Core that has parameters, you get the following exception:
System.InvalidCastException: The SqlParameterCollection only accepts non-null SqlParameter type objects, not SqlParameter objects
The error message is confusing because it’s not showing the full type names. This error means you’re using System.Data.SqlClient.SqlParameter, but EF Core only accepts Microsoft.Data.SqlClient.SqlParameter. Note: They made the error message smarter in EF Core 7 by showing full type names.
Solution – Use Microsoft.Data.SqlClient.SqlParameter
You need to use Microsoft.Data.SqlClient.SqlParameter instead of System.Data.SqlClient.SqlParameter with EF Core. Here’s an example of executing a parameterized query with Microsoft.Data.SqlClient.SqlParameter:
var nameParameter = new Microsoft.Data.SqlClient.SqlParameter("@Name", "Bob");
dbContext.Database.ExecuteSqlInterpolated($"INSERT INTO dbo.Names (Name) VALUES ({nameParameter})");
Code language: C# (cs)
This SqlParameter type is part of the Microsoft.Data.SqlClient package. Since you’re using EF Core with SQL Server, you should already have this package (it’s part of Microsoft.EntityFrameworkCore.SqlServer). If you don’t have the package, you can install it:
Install-Package Microsoft.Data.SqlClient
Code language: PowerShell (powershell)
Note: This is using the Package Manager Console in Visual Studio (View > Other Windows > Package Manager Console)
Thank you for sharing this article. It’s helpful to know about the error message and its solution when executing parameterized queries with EF Core. I appreciate the clear explanation and example of using Microsoft.Data.SqlClient.SqlParameter instead of System.Data.SqlClient.SqlParameter. It’s good to know that the Microsoft.Data.SqlClient package is already included in EF Core with SQL Server. Do you have any other tips for working with EF Core and SQL Server?
Hi, I’m glad you found this helpful! Regarding your question, you may be interested in checking out these other articles we have about EF Core (they are all using SQL Server).
Thanks for sharing this! I was getting crazy trying to solve the problem.
Hey, you’re welcome! I’m glad to hear this helped.