让我们假设我有一个有效的需要直接执行实体框架中的sql命令。我很难弄清楚如何在我的sql语句中使用参数。下面的例子(不是我的真实例子)不起作用。

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

ExecuteSqlCommand方法不允许像ADO那样传入命名参数。Net和此方法的文档没有给出如何执行参数化查询的任何示例。

如何正确地指定参数?


当前回答

对于。net Core 2.2,你可以使用FormattableString进行动态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 < @targetDate", tableName);
var froamttedSql = FormattableStringFactory.Create(sql, param);
_db.Database.ExecuteSqlCommand(froamttedSql);

其他回答

在vb中有多个参数的存储过程中的多个参数:

Dim result= db.Database.ExecuteSqlCommand("StoredProcedureName @a,@b,@c,@d,@e", a, b, c, d, e)

对于。net Core 2.2,你可以使用FormattableString进行动态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 < @targetDate", tableName);
var froamttedSql = FormattableStringFactory.Create(sql, param);
_db.Database.ExecuteSqlCommand(froamttedSql);

Oracle的简化版本。如果您不想创建OracleParameter

var sql = "Update [User] SET FirstName = :p0 WHERE Id = :p1";
context.Database.ExecuteSqlCommand(sql, firstName, id);

试试这个:

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

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

对于async方法("ExecuteSqlCommandAsync"),你可以这样使用它:

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)
    });