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

Hitting the 2100 parameter limit (SQL Server) when using Contains()(使用 Contains() 时达到 2100 参数限制 (SQL Server))

问题描述

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;

这篇关于使用 Contains() 时达到 2100 参数限制 (SQL Server)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！