Using parameters with EntityFramework and `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();
}

//repository

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

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

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

Answer 1

In .Net Core 2.0+ , below code worked for me. No need to pass through SQLParameter class.

public List<XXSearchResult> SearchXXArticles(string searchTerm,bool published=true,bool isXX=true)
    {

        var listXXArticles = _dbContext.XXSearchResults.FromSql($"SELECT * FROM [dbo].[udf_textContentSearch] ({searchTerm}, {published}, {isXX})").ToList();
        return listXXArticles;
    }

// XXSearchResult declared in DbContext class as: public DbQuery XXSearchResults { get; set; }

// udf_textContentSearch is my legacy function in SQL Server database. SELECT * FROM [dbo].[udf_textContentSearch]

Answer 2

You don't set parameters by doing a SqlCommand, you need to pass the parameters in to the FromSql statement. From 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();

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

You should also make id a parameter too.

Answer 3

While I am calling on SQL stored proc via EF Core, I like to build an array of SqlParameter to help me mash the SQL with params needed in the FromSql call, like this:

using (var scope = _services.CreateScope())
{
    var parameters = new []
    {
        new SqlParameter("param1", System.Data.SqlDbType.VarChar, 10) { Value = someInboundMethodParam },
        new SqlParameter("param2", System.Data.SqlDbType.VarChar, 50) { Value = Environment.UserName },

        // etc..
    };

    var sql = new System.Text.StringBuilder("exec YourStoreProc");

    sql.Append(string.Join(separator: ",", values: parameters.Select(p => $" @{ p.ParameterName }")));

    var ctx = scope.ServiceProvider.GetRequiredService<YourContext>();

    return await ctx
        .Query<SomeView>()
        .AsNoTracking()
        .FromSql(sql.ToString(), parameters.ToArray<object>())
        .FirstOrDefaultAsync(cancellationToken);
}

Over some iterations, I built up the following to help me compose the SQL:

public static string GetStoredProcSql(string name, SqlParameter[] parameters)
{
    var stringOfParameters =
        string.Join(
            separator: ",",
            values: parameters.Select(GetStringOfParameterNameValuePairs)
        );
    return $"exec [{name}] {stringOfParameters};";
}

public static string GetStringOfParameterNameValuePairs(SqlParameter parameter)
{
    var name = parameter.ParameterName;
    var value =
        ShouldSqlDbTypeValueRequireQuotes(parameter)
            ? $"\'{parameter.Value}\'"
            : parameter.Value;
    return $" @{name}={value}";
}

public static bool ShouldSqlDbTypeValueRequireQuotes(SqlParameter parameter)
{
    return new Enum[] {
        // SqlDbTypes that require their values be wrapped in single quotes:
        SqlDbType.VarChar,
        SqlDbType.NVarChar
    }.Contains(parameter.SqlDbType);
}

And to use:

public async Task<List<JobView>> GetJobList(
    CancellationToken cancellationToken,
    int pageStart = 1,
    int pageSize = 10
)
{
    using (var scope = _services.CreateScope())
    {
        var logger = scope.ServiceProvider.GetRequiredService<ILogger<ISomeService>>();

        logger.LogInformation($"Getting list of share creation jobs..");

        var ctxJob = scope.ServiceProvider.GetRequiredService<JobContext>();

        var sql = SqlDataHelper.GetStoredProcSql(  // use here!
            "GetJobList",
            new[]
            {
                new SqlParameter("pageStart", System.Data.SqlDbType.Int) { Value = pageStart },
                new SqlParameter("pageSize", System.Data.SqlDbType.Int) { Value = pageSize },
                //new SqlParameter("filterFieldValuePairs", System.Data.SqlDbType.VarChar) { Value = filter },
                new SqlParameter("itemType", System.Data.SqlDbType.VarChar, 10) { Value = "Share" },
            });

        return await ctxJob
            .Query<JobView>()
            .AsNoTracking()
            .FromSql(sql)
            .ToListAsync(cancellationToken);
    }
}