How to pass parameters to the DbContext Database ExecuteSqlCommand method

Question

Let s just suppose I have a valid need for directly executing a sql command in Entity Framework  I am having trouble figuring out how to use parameters in my sql statement  The following example  not my real example  doesn t work   var firstName    John   var id   12  var sql     Update  User  SET FirstName    FirstName WHERE Id    Id   ctx Database ExecuteSqlCommand sql  firstName  id     The ExecuteSqlCommand method doesn t allow you to pass in named parameters like in ADO Net and the documentation for this method doesn t give any examples on how to execute a parameterized query   How do I specify the parameters correctly

User · Answer

Multiple params in a stored procedure that has multiple params in vb   Dim result  db Database ExecuteSqlCommand  StoredProcedureName  a  b  c  d  e   a  b  c  d  e

User · Answer

public static class DbEx       public static IEnumerable lt T gt  SqlQueryPrm lt T gt  this System Data Entity Database database  string sql  object parameters            using  var tmp cmd   database Connection CreateCommand                  var dict   ToDictionary parameters               int i   0              var arr   new object dict Count               foreach  var one kvp in dict                    var param   tmp cmd CreateParameter                    param ParameterName   one kvp Key                  if  one kvp Value    null                        param Value   DBNull Value                    else                       param Value   one kvp Value                                    arr i    param                  i                              return database SqlQuery lt T gt  sql  arr                       private static IDictionary lt string  object gt  ToDictionary object data            var attr   System Reflection BindingFlags Public   System Reflection BindingFlags Instance          var dict   new Dictionary lt string  object gt             foreach  var property in data GetType   GetProperties attr                 if  property CanRead                    dict Add property Name  property GetValue data  null                                    return dict            Usage   var names   db Database SqlQueryPrm lt string gt   select name from position category where id key  id key   new   id key    mgr     ToList

User · Answer

Try this   var sql     Update  User  SET FirstName    FirstName WHERE Id    Id    ctx Database ExecuteSqlCommand      sql      new SqlParameter   FirstName   firstname       new SqlParameter   Id   id

User · Answer

Stored procedures can be executed like below   string cmd   Constants StoredProcs usp AddRoles ToString        userId   roleIdList                           int result   db Database                                         ExecuteSqlCommand                                                                                    cmd                                             new SqlParameter   userId   userId                                              new SqlParameter   roleIdList   roleId

User · Answer

Try this  edited    ctx Database ExecuteSqlCommand sql  new SqlParameter  FirstName   firstName                                        new SqlParameter  Id   id      Previous idea was wrong

User · Answer

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

User · Answer

For the async Method   ExecuteSqlCommandAsync   you can use it like this   var sql     Update  User  SET FirstName    FirstName WHERE Id    Id    await ctx Database ExecuteSqlCommandAsync      sql      parameters  new            new SqlParameter   FirstName   firstname           new SqlParameter   Id   id

User · Answer

If your underlying database data types are varchar then you should stick with the approach below  Otherwise the query would have a huge performance impact   var firstName   new SqlParameter   firstName   System Data SqlDbType VarChar  20                                                                Value    whatever                                  var id   new SqlParameter   id   System Data SqlDbType Int                                                                Value   1                                ctx Database ExecuteSqlCommand   Update  User  SET FirstName    firstName WHERE Id    id                                   firstName  id     You can check Sql profiler to see the difference

User · Answer

For  NET Core 2 2  you can use FormattableString for dynamic SQL      Assuming this is your dynamic value and this not coming from user input var tableName    LogTable       let s say target date is coming from user input var targetDate   DateTime Now Date AddDays -30   var param   new SqlParameter   targetDate   targetDate     var sql   string Format  Delete From  0  Where CreatedDate  lt   targetDate   tableName   var froamttedSql   FormattableStringFactory Create sql  param    db Database ExecuteSqlCommand froamttedSql

User · Answer

var firstName    John   var id   12   ctx Database ExecuteSqlCommand   Update  User  SET FirstName    0  WHERE Id    1     new object    firstName  id       This is so simple      Image for knowing parameter reference

User · Answer

For entity Framework Core 2 0 or above  the correct way to do this is   var firstName    John   var id   12  ctx Database ExecuteSqlCommand   Update  User  SET FirstName    firstName  WHERE Id    id      Note that Entity Framework will produce the two parameters for you  so you are protected from Sql Injection   Also note that it is NOT   var firstName    John   var id   12  var sql     Update  User  SET FirstName    firstName  WHERE Id    id    ctx Database ExecuteSqlCommand sql     because this does NOT protect you from Sql Injection  and no parameters are produced   See this for more

User · Answer

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

User · Answer

Simplified version for Oracle  If you don t want to create OracleParameter  var sql    Update  User  SET FirstName    p0 WHERE Id    p1   context Database ExecuteSqlCommand sql  firstName  id

User · Answer

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

[entity-framework] How to pass parameters to the DbContext.Database.ExecuteSqlCommand method?

Examples related to entity-framework

Examples related to entity-framework-4.1