DbParameterCollection does not contain a definition for AddWithValue

2
votes

Getting above error on the last line of the following code. I'm using EF Core 1.1. Trying to follow this suggestion.

using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient; //Visual Studio had greyed out this line suggesting this as unnecessary.

var conn = _context.Database.GetDbConnection();

var cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "MySproc";
cmd.Parameters.AddWithValue("@MyParameter", 42);
3
c#asp.net-coreentity-framework-core
You need to add the reference to System.Data.SqlClient - H. Herzl
@H.Herzl I tried your suggestion - still the same error. Also when I added System.Data.SqlClient Visual Studio has greyed it out suggesting this using statement as unnecessary. - nam
I think you need to cast your db connection to sql connection - H. Herzl
@H.Herzl It works with your suggested casting - thanks. - nam
You should also be aware of the performance implications of using AddWithValue. Rather than repeat them all, there is a good explanation of these in the accepted answer here - bornfromanegg

3 Answers

3
votes

Add reference for System.Data.SqlClient and cast your DbConnection instance to SqlConnection.

13
votes

Here's the equivalent code using just the System.Data.Common APIs. (It can be used with any ADO.NET provider.)

var parameter = cmd.CreateParameter();
parameter.ParameterName = "@MyParameter";
parameter.Value = 42;

cmd.Parameters.Add(parameter);
1
votes

In dotnet core 3+ running command is broken. To fix it, change System.Data.SqlClient to Microsoft.Data.SqlClient