The incoming request has too many parameters. The server supports a maximum of 2100 parameters

Question

I have this seemingly simple linq-to-sql query that searches some data in several columns; something like this:

List<long> TheTableIDs = list of IDs (sometimes more than 2100)  var QueryOutput = (from x in TheDataContext.SomeTable                     where TheTableIDs.Contains(x.ID) &&                                        x.Col1.Contains(SomeString) ||                     x.Col2.Contains(SomeString))                     select x.ID).ToList(); 

The error I get is this:

Additional information: The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.

What's the best way to solve this?

I've looked around and a solution I found looks like this:

var QueryOutput = TheDataContext.SomeTable.AsEnumerable()          .Join(TheTableIDs, x => x.LeadID, ci => ci, (x, ci) => x)          .Where(x => SomeString.Contains(x.Col1) == true ||                     SomeString.Contains(x.Col2) == true)                  .Select(x => x.ID).ToList(); 

This compiles and doesn't throw any exceptions but seems to ignore the Where clauses with SomeString.Contains

What's the solution to make this query work?

Answer 1

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.

Answer 2

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