c# – 如何在Entity Framework中编写一个参数化的in-in raw sql

发布时间：2020-12-15 18:03:29 所属栏目：百科来源：网络整理

导读：在Entity Framework中如何编写参数化的in-in raw sql查询？我尝试过以下操作： string dateQueryString = String.Join(",",chartModelData.GetFormattedDateList());//Dates returned in format of 20140402,20140506,20140704const string selectQuery = @"

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

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上抛出一个错误,因为它期待一个单一的值.

解决方法

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

var parameters = new List<SqlParameter> {
    new SqlParameter("@DateParam",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,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的结果查询将如下所示：

SELECT 
   MAX(DATA_SEQ) AS MaxSeq,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安全的.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!