使用 Contains() 时达到 2100 参数限制 (SQL Server)

2021-12-09 00:00:00 limit parameters sql-server linq 
from f in CUSTOMERS
where depts.Contains(f.DEPT_ID)
select f.NAME

depts 是部门 ID 的列表(IEnumerable)

depts is a list (IEnumerable<int>) of department ids

这个查询工作正常，直到你传递一个大列表(比如大约 3000 个部门 ID)..然后我得到这个错误:

This query works fine until you pass a large list (say around 3000 dept ids) .. then I get this error:

传入的表格数据流 (TDS) 远程过程调用 (RPC) 协议流不正确.此 RPC 请求中提供的参数过多.最大为 2100.

The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RPC request. The maximum is 2100.

我将查询更改为:

var dept_ids = string.Join(" ", depts.ToStringArray());
from f in CUSTOMERS
where dept_ids.IndexOf(Convert.ToString(f.DEPT_id)) != -1
select f.NAME

使用 IndexOf() 修复了错误但使查询变慢.有没有其他方法可以解决这个问题?非常感谢.

using IndexOf() fixed the error but made the query slow. Is there any other way to solve this? thanks so much.

推荐答案

我的解决方案(Guids 是您想要过滤的 ID 列表):

My solution (Guids is a list of ids you would like to filter by):

List<MyTestEntity> result = new List<MyTestEntity>();
for(int i = 0; i < Math.Ceiling((double)Guids.Count / 2000); i++)
{
    var nextGuids = Guids.Skip(i * 2000).Take(2000);
    result.AddRange(db.Tests.Where(x => nextGuids.Contains(x.Id)));
}
this.DataContext = result;

相关文章