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.
Advertisement
Answer
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.