c# - 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?

Thanks.

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.