Maximum number of parameters in sql query

Question

I have been experimenting with LINQ since some time. A typical method for listing in a collection and changing some of its properties in my code would look like this:

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

For a while, I have some advice to do this ... seems like a 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. Its smart and quick than the first.

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

What was my surprise a few weeks later when looking for application log files and seeing the following:

"Incoming call flow protocol (RPC) of the incoming tabular data stream (TDS) is incorrect. Too many parameters were provided in this RCP request. Maximum 2100.

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

The col collection reached (in my case) more than 2100 elements and the request could not complete. I did some research on the problem and I ended up:

"... The maximum number of parameters in an sql query is 2100. There are more restrictions, for example, the fact that the entire query string cannot be longer than 8044 characters."

I liked the second solution so much. I am so disappointed with these hard-coded SQL Server restrictions.

Did I miss something? Is there anything I can do to use the version of "where col.Contains (r.ID)"?

Relations Mariusz

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

Answer 1

Constraints are hardcoded :

• Parameters for the 2,100 stored procedure
• Parameters for User Function 2,100

I wrote the code before I broke the Contains query into batches and combined the results ... For more details, see here .