如何在Entity Framework中编写一个参数化的in-in raw sql查询 [英] How do you write a parameterized where-in raw sql query in Entity Framework

问题描述

如何在Entity Framework中编写参数化的in-in raw sql查询？我尝试了以下内容：

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

但是正如预期的那样，它会在DateParam上抛出一个错误，因为它期待一个值。

But as expected, it throws an error on DateParam because it's expecting a single value.

推荐答案

这不是特定于实体框架的问题，您可以通过动态生成自己的参数名来解决它。

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

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

发送到SQL Server的结果查询将如下所示：

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

一般来说，你想避免在编写查询时进行字符串操作但是，我相信这个例子是从sql-injection安全的。

Generally, you want to avoid doing string manipulation when writing queries, however, I believe this example is safe from sql-injection.

这篇关于如何在Entity Framework中编写一个参数化的in-in raw sql查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！