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

问题描述

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

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

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

该查询工作正常，直到您传递一个大列表(例如约3000个dept 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.

推荐答案

我的解决方案(指南-> Guid列表):

My solution (Guides -> List of Guid):

List<tstTest> tsts = new List<tstTest>();
for(int i = 0; i < Math.Ceiling((double)Guides.Count / 2000); i++)
{
    tsts.AddRange(dc.tstTests.Where(x => Guides.Skip(i * 2000).Take(2000).Contains(x.tstGuid)));
}
this.DataContext = tsts;

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