Hitting the 2100 parameter limit (SQL Server) when using Contains()

Question

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

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

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

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.

I changed my query to:

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

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

Answer 1

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;