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();
}
// repository
public List<PostJobListModel> FromSql(string sql, params object[] objects)
{
return _context.PostJobListModel.FromSql(sql,objects).ToList();
}
This code gives "SQLException Must declare scalar variable" @variableName "" How can I create a security command line string?
source to share
You are not setting parameters by executing the SqlCommand, you need to pass parameters to the statement FromSql
. From the documentation
You can also create a DbParameter and provide it as a cost parameter. This allows named parameters to be used in a 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 must also make a id
parameter.
source to share
In .Net Core 2.0+, below code worked for me. There is no need to go through the 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 is declared in the DbContext class as: public DbQuery XXSearchResults {get; set; }
// udf_textContentSearch is my legacy function in SQL Server database. SELECT * FROM [dbo]. [Udf_textContentSearch]
source to share
When I call a SQL stored process through EF Core, I like to create a SqlParameter array to help me parse the SQL with the parameters needed in the FromSql call, for example:
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);
}
source to share