title: maximum number of parameters in sql query
body: <p>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:</p>
  <pre><code>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();
  }
  </code></pre>
  <p>Later on I have got an advice to do this by... seems like much smarter way:</p>
  <pre><code>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();
              
  </code></pre>
  <p>Iit makes sense on so many levels and I love this solution. It’s smart and faster than the first one.</p>
  <p>I have used this solution in a production environment for some time.</p>
  <p>What was my surprise after few weeks when searching an application log files and see this:</p>
  <p><em><strong>&quot;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.&quot;</strong></em></p>
  <p>The LINQ to SQL converts <code>where col.Contains(r.ID)</code> to <code>IN</code>clause looking something like:<br />
  WHERE ID IN (@p1, @p1, @p2 … )</p>
  <p>The <code>col</code> 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:</p>
  <p><em><strong>“… 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.”</strong></em></p>
  <p>I have loved the second solution so much. I am so disappointed with these hard-coded limitations of the SQL Server.</p>
  <p>Did I miss something?
  Is there anything I can do to be able to use the “where col.Contains(r.ID)” version?</p>
  <p>Regards
  Mariusz</p>
  <p>ps. (I use Win XP, C# with LINQ and SQL 2005 Express).</p>