C#查询条件中存在in，为了避免拼脚本，参数化查询数据库，提高安全性，规避脚本注入。网上找了好多，最后发现 SqlParameter 是无法实现in的操作，所以只能变相来实现，结果还是不错的，性能上各位自己去测试一下吧，因为in操作本身就比较慢（无法使用索引）。下面给出SQl脚本
--传统in操作
SELECT a.NAME
FROM ( SELECT '张源' AS NAME
UNION ALL
SELECT '赵明' AS NAME
UNION ALL
SELECT '王刚' AS NAME
UNION ALL
SELECT '陈红' AS NAME
UNION ALL
SELECT '孙强' AS NAME
UNION ALL
SELECT '李伟' AS NAME
UNION ALL
SELECT '钱昆' AS NAME
UNION ALL
SELECT '郑芳' AS Name
) a
WHERE name IN ( '张源', '郑芳' )
--使用CHARINDEX实现in操作
SELECT a.NAME
FROM ( SELECT '张源' AS NAME
UNION ALL
SELECT '赵明' AS NAME
UNION ALL
SELECT '王刚' AS NAME
UNION ALL
SELECT '陈红' AS NAME
UNION ALL
SELECT '孙强' AS NAME
UNION ALL
SELECT '李伟' AS NAME
UNION ALL
SELECT '钱昆' AS NAME
UNION ALL
SELECT '郑芳' AS Name
) a
WHERE CHARINDEX(','+CAST(Name AS NVARCHAR(MAX))+',',',张源,郑芳,')>0
下面在给出一段EF代码：
var ids = string.Join(",", id);
SqlParameter[] para = new SqlParameter[] {
//-1表示最大max
new SqlParameter("@DetialIDs", SqlDbType.VarChar, -) { Value=ids}
};
var sql = @"SELECT DetialID
FROM OrderDetial
WHERE CHARINDEX(',' + cast( DetialID as varchar(max)) + ',', ','+@DetialIDs +',')> 0";
return Context.Database.SqlQuery<OrderDetial>(sql, para);