The incoming request has too many parameters. The server supports a maximum of 2100 parameters
Simple - as long as TheTAbleID's contains less than 2100 ID's then - it is not legal to do that.
Cut the table into blocks of 2000 and then query each block separately, possibly in multiple threads.
SQL doesn't support more than 2100 values in in
statement, but you can use in with table with more than 2100 rows so you can insert your data into a table and change your query to check the in
with selecting from that table
for example
Create TempIDs (bigint ID, uniqueidentifier guid)
guid column is for preventing mixing different user data
in your code
Guid myKey = Guid.New();
List<long> TheTableIDs = list of IDs (sometimes more than 2100)
TheDataContext.TempIDs.InsertAllOnSubmit(TheTableIDs.select(i => new TempIDs{ID = i, Guid = mykey});
TheDataContext.SubmitChanges();
var QueryOutput = (from x in TheDataContext.SomeTable
where TheDataContext.TempIDs.Contains(x.ID) &&
x.Col1.Contains(SomeString) ||
x.Col2.Contains(SomeString))
select x.ID).ToList();
also if you can retrive the ids from database , you can write a table value function in sql to return the ids and model this function in your code, let say its name is fnGetIds
.
Then use it in your code as below
var QueryOutput = (from x in TheDataContext.SomeTable
where TheDataContext.fnGetIds().Contains(x.ID) &&
x.Col1.Contains(SomeString) ||
x.Col2.Contains(SomeString))
select x.ID).ToList();
Use 2 where clauses:
List<long> TheTableIDs = list of IDs (sometimes more than 2100)
var _QueryOutput = (from x in TheDataContext.SomeTable
where x.Col1.Contains(SomeString) || x.Col2.Contains(SomeString))
select x.ID).ToList();
var QueryOutput = _QueryOutput.Where(w => TheTableIDs.Contains(w)).ToList();
For efficiency, you could refactor the code so it only does it this way if list contains more than 2000:
if (TheTableIDs.Count() > 2000)
// Code Here
else
// Code Here