CAML query limitation of values in IN operator

Question

I have less than 5000 items in my library. I want to get 530 items by ids. So I am using following query.

<Where>
  <In>
    <FieldRef Name="ID" />
    <Values>
       <Value Type="Number">1</Value>
       <Value Type="Number">2</Value>
       .
       .
       .
       <Value Type="Number">530</Value>
    </Values>
  </In>
</Where>

If I use this query on list then while iterating SPListItemCollection following exception is occurred.

System.ArgumentException: Value does not fall within the expected range.
   at Microsoft.SharePoint.Library.SPRequestInternalClass.GetListItemDataWithCallback2(IListItemSqlClient pSqlClient, String bstrUrl, String bstrListName, String bstrViewName, String bstrViewXml, SAFEARRAYFLAGS fSafeArrayFlags, ISP2DSafeArrayWriter pSACallback, ISPDataCallback pPagingCallback, ISPDataCallback pPagingPrevCallback, ISPDataCallback pFilterLinkCallback, ISPDataCallback pSchemaCallback, ISPDataCallback pRowCountCallback, Boolean& pbMaximalView) 
   at Microsoft.SharePoint.Library.SPRequest.GetListItemDataWithCallback2(IListItemSqlClient pSqlClient, String bstrUrl, String bstrListName, String bstrViewName, String bstrViewXml, SAFEARRAYFLAGS fSafeArrayFlags, ISP2DSafeArrayWriter pSACallback, ISPDataCallback pPagingCallback, ISPDataCallback pPagingPrevCallback, ISPDataCallback pFilterLinkCallback, ISPDataCallback pSchemaCallback, ISPDataCallback pRowCountCallback, Boolean& pbMaximalView) 
   at Microsoft.SharePoint.SPListItemCollection.EnsureListItemsData() 
   at Microsoft.SharePoint.SPListItemCollection.GetEnumerator() 

If I rerun the query with Values from IN operator less than 500 then SPListItemCollection is not throwing an exception.

Does CAML IN operator has limit of having less than equal 500 items? Or is there any other better approach.

Answer 1

It sure looks so from your story, but isn't there a quite easy workaround for this?

<Where>
  <Or>
      <In>
        <FieldRef Name="ID" />
        <Values>
           <Value Type="Number">1</Value>
           <Value Type="Number">2</Value>
           .
           .
           .
           <Value Type="Number">499</Value>
        </Values>
      </In>
      <In>
        <FieldRef Name="ID" />
        <Values>
           <Value Type="Number">500</Value>
           <Value Type="Number">501</Value>
           .
           .
           .
           <Value Type="Number">999</Value>
        </Values>
      </In>
   </Or>
</Where>

and so on, with more nesting if over 1000 and so on.

Answer 2

Can't comment due to low rep so posting as an answer, purists forgive.

Breaking a 500 element In into several nested Or tags does not work, to my despair. The limit is 500 elements hard apparently.

I have tried breaking using a limit of 500 elements, 400, 300, 200, 100, and the SPServices query failed, returning: 500 Internal Server Error with the following responseText:

Exception of type 'Microsoft.SharePoint.SoapServer.SoapServerException' was thrown.

Trying to update 500 elements at most (with a single In tag or breaking using nested Or per 400, 300, 200, 100 elements) works. Then the query returns 200 success. To my knowledge there should also be some limit of nested Or tags.

I'm using: SPServices 2014.01, jQuery 1.11, SharePoint 2013:

$().SPServices.SPUpdateMultipleListItems({
    listName: "some list display name",
    CAMLQuery: CAMLQuery,
    valuepairs: [["static name of the field", 1]],
    completefunc: function (xData) {
        if (xData.status !== 200) {
            console.log("failure");
        }
    }
});

Example query 5 * 100 + 1 that fails since there are 501 elements:

<Query>
  <Where>
    <Or>
      <In>
        <FieldRef Name='ID' LookupId='TRUE' />
        <Values>
          100 times <Value Type='Lookup'>2688</Value>
        </Values>
      </In>
      <Or>
        <In>
          <FieldRef Name='ID' LookupId='TRUE' />
          <Values>
            100 times value
          </Values>
        </In>
        <Or>
          <In>
            <FieldRef Name='ID' LookupId='TRUE' />
            <Values>
              100 times value
            </Values>
          </In>
          <Or>
            <In>
              <FieldRef Name='ID' LookupId='TRUE' />
              <Values>
                100 times value
              </Values>
            </In>
            <Or>
              <In>
                <FieldRef Name='ID' LookupId='TRUE' />
                <Values>
                  100 times value
                </Values>
              </In>
              <In>
                <FieldRef Name='ID' LookupId='TRUE' />
                <Values>
                  <Value Type='Lookup'>3089</Value>
                </Values>
              </In>
            </Or>
          </Or>
        </Or>
      </Or>
    </Or>
  </Where>
</Query>

Answer 3

Also can't comment, but what I have done when confronted with this type of situation (yes, I have encountered this limitation), is to run the query using the min & max of ID

<Where>
    <And>
        <Geq><FieldRef Name='ID' /><Value Type='Counter'>x</Value></Geq>
        <Leq><FieldRef Name='ID' /><Value Type='Counter'>y</Value></Leq>
    </And>
</Where>

Then, assuming I have the IDs that I actually do want in some type of enumerable, use LINQ to get the items that you actually do want from the query results.

Answer 4

Following the answers above, you can use this extention method for split array of values:

public static List<string[]> SplitArray(this string[] main_array, int num = 500)
    {
        var result = new List<string[]>();
        try
        {
            if (main_array.Length > 0)
            {
                var step = Math.Ceiling((double)main_array.Length / num);

                for (int i = 0; i < step; i++)
                    result.Add(main_array.Skip(i * num).Take(num).ToArray());

            }
        }
        catch (Exception ex)
        {
            Logger.log("SplitArray ==> Error : " + ex.Message);
            throw ex;
        }
        return result;

    }