sql-server - 使用 Contains() 时达到 2100 个参数限制 (SQL Server)

Question

from f in CUSTOMERS
where depts.Contains(f.DEPT_ID)
select f.NAME

depts是部门 ID 的列表 ( IEnumerable<int> )

此查询工作正常，直到您传递一个大列表(例如大约 3000 个部门 ID)..然后我收到此错误:

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()修复了错误，但使查询变慢。还有其他方法可以解决这个问题吗？非常感谢。

Answer 1

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

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;