SQL脚本IN在EF中的应用

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

巴特西

SQL脚本IN在EF中的应用

最新文章

热门文章