將參數與 EntityFramework 和 FromSql 一起使用

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 Must declar scalar variable “@variableName” “我如何創建安全命令字元串？

編輯答案return _repositoryCustom.FromSql(command, parameterS , parameterD , parameterP ).ToList();

您不通過執行SqlCommand來設置參數，您需要將參數傳遞給FromSql語句。從文件

您還可以構造 DbParameter 並將其作為參數值提供。這允許您在 SQL 查詢字元串中使用命名參數+

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一個參數。

引用自：https://stackoverflow.com/questions/45416949