How do you write a parameterized where-in raw sql query in Entity Framework
Solution 1
This isn't a problem specific to entity-framework, you can solve it by generating your own parameter names dynamically.
var parameters = new List<SqlParameter> {
new SqlParameter("@DateParam", dateQueryString),
new SqlParameter("@LineCode", chartModelData.LineCode),
new SqlParameter("@ModelNumber", chartModelData.ModelNum),
new SqlParameter("@EquipNumber", equipmentNumber),
new SqlParameter("@LotNumber", chartModelData.LotNum)
};
var dateParameters = chartModelData
.GetFormattedDateList()
.Select((date, index) => new SqlParameter("@date" + index, date));
.ToList();
parameters.AddRange(dateParameters);
var inValues = string.Join(", ", dateParameters.Select(p => p.ParameterName));
var query = @"SELECT MAX(DATA_SEQ) AS MaxSeq,
MIN(DATA_SEQ) AS MinSeq,
COUNT(1) AS TotSampleCnt
FROM SPCDATA_TB
WHERE DATA_WDATE IN (" + inValues + @")
AND LINE_CODE = @LineCode
AND MODEL_NO = @ModelNumber
AND LOT_NO = @LotNumber
AND EQUIP_NO LIKE @EquipNumber";
var myResult = _dbContext.Database
.SqlQuery<SPCDataSeqCntInfo>(query, parameters.ToArray());
The resulting query sent to SQL-Server will look like the following:
SELECT
MAX(DATA_SEQ) AS MaxSeq,
MIN(DATA_SEQ) AS MinSeq,
COUNT(1) AS TotSampleCnt
FROM SPCDATA_TB
WHERE DATA_WDATE IN (@date0, @date1, @date2)
AND LINE_CODE = @LineCode
AND MODEL_NO = @ModelNumber
AND LOT_NO = @LotNumber
AND EQUIP_NO LIKE @EquipNumber
Generally, you want to avoid doing string manipulation when writing queries, however, I believe this example is safe from sql-injection.
Solution 2
Here's how you would write your query in SQL.
select *
from MyTable
where dateColumn in ('2014-01-01', '2014-02-01', '2014-03-01')
So one shall not expect otherwise than having to represent this string entirely delimited by parenthesis.
var dateQueryString = string.Join(",", chartModelData.GetFormattedDateList());
// Dates shall be returned as DateTime.ToShortDateTimeString() as follows:
// '2014-01-01', '2014-02-01', '2014-03-01'
Then only remains to wrap it in parenthesis.
var sql = @"select max(data_seq) as MaxSeq
, min(data_seq) as MinSeq
, count(1) as TotSampleCnt
from spcdata_tb
where data_wadate in (@DateParam)
and line_code = @LineCode
and model_no = @ModelNumber
and lot_no = @LotNumber
and equip_no like @EquipNumber";
Provide the parameters values for each named parameter, and voilà! This shall do it!
Related videos on Youtube
TtT23
Updated on October 06, 2021Comments
-
TtT23 over 2 years
How do you write a parameterized where-in raw sql query in Entity Framework? I've tried the following:
string dateQueryString = String.Join(",", chartModelData.GetFormattedDateList()); //Dates returned in format of 20140402,20140506,20140704 const string selectQuery = @"SELECT MAX(DATA_SEQ) AS MaxSeq, MIN(DATA_SEQ) AS MinSeq, COUNT(1) AS TotSampleCnt FROM SPCDATA_TB WHERE DATA_WDATE IN @DateParam AND LINE_CODE = @LineCode AND MODEL_NO = @ModelNumber AND LOT_NO = @LotNumber AND EQUIP_NO LIKE @EquipNumber"; SPCDataSeqCntInfo dataSeqCntInfo = _dbContext.Database.SqlQuery<SPCDataSeqCntInfo>( selectQuery, new SqlParameter("@DateParam", dateQueryString), new SqlParameter("@LineCode", chartModelData.LineCode), new SqlParameter("@ModelNumber", chartModelData.ModelNum), new SqlParameter("@EquipNumber", equipmentNumber), new SqlParameter("@LotNumber", chartModelData.LotNum) ).SingleOrDefault() ?? new SPCDataSeqCntInfo();
But as expected, it throws an error on DateParam because it's expecting a single value.
-
Will Marcouiller over 9 years
@DateParam
shall be a comma-separated string representing the dates you wish to look for in the set, then it shall also be delimited by parenthesis.
-
-
TtT23 over 9 yearsAlright. I guess that's a fair alternative, but is there absolutely no way to accomplish what I'm trying to do without resorting into stored procedure? Just for the sake of curiosity.
-
Scotty over 9 yearsHave a look at using _dbContext.Database.ExecuteSqlCommand instead.