How to pass parameters to the DbContext.Database.ExecuteSqlCommand method?

Question

Try this:

var sql = @"Update [User] SET FirstName = @FirstName WHERE Id = @Id";

ctx.Database.ExecuteSqlCommand(
    sql,
    new SqlParameter("@FirstName", firstname),
    new SqlParameter("@Id", id));

Turns out that this works.

var firstName = "John";
var id = 12;
var sql = "Update [User] SET FirstName = {0} WHERE Id = {1}";
ctx.Database.ExecuteSqlCommand(sql, firstName, id);

You can either:

1) Pass raw arguments and use the {0} syntax. E.g:

DbContext.Database.SqlQuery("StoredProcedureName {0}", paramName);

2) Pass DbParameter subclass arguments and use @ParamName syntax.

DbContext.Database.SqlQuery("StoredProcedureName @ParamName", 
                                   new SqlParameter("@ParamName", paramValue);

If you use the first syntax, EF will actually wrap your arguments with DbParamater classes, assign them names, and replace {0} with the generated parameter name.

The first syntax if preferred because you don't need to use a factory or know what type of DbParamaters to create (SqlParameter, OracleParamter, etc.).

The other answers don't work when using Oracle. You need to use : instead of @.

var sql = "Update [User] SET FirstName = :FirstName WHERE Id = :Id";

context.Database.ExecuteSqlCommand(
   sql,
   new OracleParameter(":FirstName", firstName), 
   new OracleParameter(":Id", id));