.net - 将参数与 EntityFramework 和 `FromSql` 一起使用

Question

public List<PostJobListModel> GetPostsByCompanyId(int id, int s, int d, int p)
{
    string command = @"select Id,Title,Cities = STUFF(
     (SELECT  ',' + City.Name  
      FROM City where City.Id in (select Id from LocaitonJobRelationship as ljr where ljr.JobId = PostJob.Id)
      FOR XML PATH ('')), 1, 1, ''),
      Features = STUFF(
     (SELECT  ',' + Feature.Name  
      FROM Feature where Feature.Id in (select FeatureId from FeatureJobRelationship as fjr where fjr.JobId = PostJob.Id and (fjr.CategoryId in (@s,@d,@p) ) )FOR XML PATH('')), 1, 1, '')from PostJob where CompanyId = " + id + "";

    SqlParameter parameterS = new SqlParameter("@s", s);
    SqlParameter parameterD = new SqlParameter("@d", d);
    SqlParameter parameterP = new SqlParameter("@p", p);

    return _repositoryCustom.FromSql(command, s, d, p).ToList();
}

//存储库

public List<PostJobListModel> FromSql(string sql, params object[] objects)
{
    return _context.PostJobListModel.FromSql(sql,objects).ToList();
}

此代码给出“SQLException 必须声明标量变量“@variableName””我如何创建安全命令字符串？

编辑答案return _repositoryCustom.FromSql(command,parameterS,parameterD,parameterP).ToList();

Answer 1

您不能通过执行 SqlCommand 来设置参数，您需要将参数传递到 FromSql 语句中。来自 the documention

You can also construct a DbParameter and supply it as a parameter value. This allows you to use named parameters in the SQL query string+

var user = new SqlParameter("user", "johndoe");

var blogs = context.Blogs
    .FromSql("EXECUTE dbo.GetMostPopularBlogsForUser @user", user)
    .ToList();

所以对于你的代码你会这样做

public List<PostJobListModel> GetPostsByCompanyId(int id, int s, int d, int p)
{
    string command = @"select Id,Title,Cities = STUFF(
     (SELECT  ',' + City.Name  
      FROM City where City.Id in (select Id from LocaitonJobRelationship as ljr where ljr.JobId = PostJob.Id)
      FOR XML PATH ('')), 1, 1, ''),
      Features = STUFF(
     (SELECT  ',' + Feature.Name  
      FROM Feature where Feature.Id in (select FeatureId from FeatureJobRelationship as fjr where fjr.JobId = PostJob.Id and (fjr.CategoryId in (@s,@d,@p) ) )FOR XML PATH('')), 1, 1, '')from PostJob where CompanyId = " + id + "";

    SqlParameter parameterS = new SqlParameter("@s", s);
    SqlParameter parameterD = new SqlParameter("@d", d);
    SqlParameter parameterP = new SqlParameter("@p", p);

    return _repositoryCustom.FromSql(command, parameterS, parameterD, parameterP).ToList();
}

您还应该将 id 也设置为参数。