ORM中in查询条件过多导致的查询失败问题解决

使用多线程分批查询,避免ORM中in查询条件过多导致的查询失败

使用EF,SqlSuger等ORM查询数据的时候, 经常会碰到需要使用Contains这种in查询的时候,但是如果没办法控制in条件中的个数, 就会导致生成的sql语句过长, 直接挂掉, 解决这个问题, 以下介绍两种方法

---

1. 使用多线程分批查询

/// <summary>
/// 分组查询
/// </summary>
/// <typeparam name="T">查询条件类型</typeparam>
/// <typeparam name="T1">返回结果类型</typeparam>
/// <param name="param">查询条件</param>
/// <param name="pre">每批次数量</param>
/// <param name="action">要执行的查询方法</param>
/// <returns></returns>
public static List<T1> GetDataByList<T,T1>(List<T> param,int pre, Func<List<T>,List<T1>> action) 
{
	var parallelOptions = new ParallelOptions
	{
		MaxDegreeOfParallelism = 2
	};
	if (param.Count > 10000)
	{
		parallelOptions.MaxDegreeOfParallelism = 3;
	}

	List<T1> resultList = new List<T1>();

	var listGroup = param.GetListGroup(pre);
	Parallel.ForEach(listGroup, parallelOptions,item=> 
	{
		resultList.AddRange(action(item));
	});
	return resultList;
}

/// <summary>
/// 按指定数量对List分组
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list">原list</param>
/// <param name="groupNum">分组后每个组的数量</param>
/// <returns></returns>
public static List<List<T>> GetListGroup<T>(this List<T> list, int groupNum)
{
	List<List<T>> listGroup = new List<List<T>>();
	for (int i = 0; i < list.Count; i += groupNum)
	{
		listGroup.Add(list.Skip(i).Take(groupNum).ToList());
	}
	return listGroup;
}

然后是调用的地方

var paramList = new List<string>();//查询条件,
//每100个param查一次db获取数据
var resultList =ListHelper.GetDataByList(paramList, 100, splitItems =>
{
	return _service.GetListFromDB(splitItems);
});

---

2. 使用SQL自定义表类型

sql本身支持使用自定义表类型, 将一张表作为参数传入存储过程, 使用自定义表类型, 我们就可以将List传入后台存储过程, 然后在存储过程中, 使用表参数直接在存储过程中做关联查询, 避开in查询,同时也可以显著提升查询效率


创建表类型脚本如下

CREATE TYPE [dbo].[ParamListType] AS TABLE(
	[GuidParam] [uniqueidentifier] NOT NULL,
	[StringParam] [varchar](200) NOT NULL,
	[IntParam] [int] NOT NULL
)
GO

存储过程中接收参数如下

create procedure  aaa
@ParamList ParamListType readonly
as 

select * from a
join @ParamList b on b.GuidParam=a.id

然后在使用的地方正常调用存储过程就行

var parameters = new SqlParameter[1];
//dt为将list转成的table
parameters[0] = new SqlParameter("@ParamListType", dt);
parameters[0].SqlDbType = System.Data.SqlDbType.Structured;
var ds = SqlHelper.SqlQueryForDataSet(_connStr, "aaa", parameters);