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 (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; 

Answer 2

Why not write the query in sql and attach your entity?

It's been awhile since I worked in Linq, but here goes:

IQuery q = Session.CreateQuery(@"          select *           from customerTable f          where f.DEPT_id in (" + string.Join(",", depts.ToStringArray()) + ")"); q.AttachEntity(CUSTOMER); 

Of course, you will need to protect against injection, but that shouldn't be too hard.