maximum number of parameters in sql query...

Question

I do experiment with LINQ since some time. Typical method to enumerate through a collection and change some of its properties in my code would look like:

ATDataContext dc = new ATDataContext(Settings.connection_string);

int[] col = ListViewClass.getListViewSelectedPositionTags(listView);

try
{
    foreach (var item in col)
    {
        var ctx = (from r in dc.MailingLists
                   where r.ID == item
                   select r).Single();

        ctx.Excluded = 'Y';
        ctx.ExcludedComments = reason;
    }

    dc.SubmitChanges();
}

Some time a have got an advice to do this by... seems like much smarter way:

var ctx = from r in dc.MailingLists
    where col.Contains(r.ID)
    select r;

foreach (var item in ctx)
{
    item.Excluded = 'Y';
    item.ExcludedComments = reason;
}

dc.SubmitChanges();

Iit makes sense on so many levels and I love this solution. It’s smart and faster than the first one.

I have used this solution in a production environment for some time.

What was my surprise after few weeks when searching an application log files and see this:

"The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RCP request. The maximum is 2100."

The LINQ to SQL converts where col.Contains(r.ID) to INclause looking something like:
WHERE ID IN (@p1, @p1, @p2 … )

The col collection reached (in my case) more than 2100 elements and the query failed to perform. I have done some research on the problem and what I ended up is:

“… Maximum number of parameters in the sql query is 2100. There is more limitations, like the fact that the whole query string cannot be longer than 8044 characters.”

I have loved the second solution so much. I am so disappointed with these hard-coded limitations of the SQL Server.

Did I miss something? Is there anything I can do to be able to use the “where col.Contains(r.ID)” version?

Regards Mariusz

ps. (I use Win XP, C# with LINQ and SQL 2005 Express).

Answer 1

The limits are hard-coded:

• Parameters per stored procedure 2,100
• Parameters per user-defined function 2,100

I wrote some code before that split the Contains query into batches and combined the results... see here for more.