在EntityFramework和`FromSql`中使用参数 [英] Using parameters with EntityFramework and `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必须声明标量变量 @variableName
我如何创建安全命令字符串？

This code gives "SQLException Must declar scalar variable "@variableName" " How i do create security command string ?

编辑答案返回_repositoryCustom.FromSql（command，parameterS，parameterD，parameterP）.ToList（） ;

推荐答案

您不需要通过执行SqlCommand来设置参数，您需要传递参数进入 FromSql 语句。从文档

You don't set parameters by doing a SqlCommand, you need to pass the parameters in to the FromSql statement. From the documention

您还可以构造DbParameter并将其作为参数
值提供。这使您可以在SQL查询中使用命名参数
string +

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

因此对于您的代码，您应该这样做

So for your code you would do

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 也是一个参数。

You should also make id a parameter too.

这篇关于在EntityFramework和`FromSql`中使用参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！