How do you write a parameterized where-in raw sql query in Entity Framework

Question

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.

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

Answer 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!