CAML Query for Values Not “In” (with sufficiently different title to let me post the question)

Question

I have the following CAML query:

<Where>
<In>
  <FieldRef Name='ObjectID' />
  <Values>
    <Value Type='Integer'>71</Value>
    <Value Type='Integer'>72</Value>
  </Values>
</In>
</Where>

But what I want in the result set if effectively the negation of that, or items that do not meet the condition.

Is there any way I can achieve this with a relatively simple query.

I'm new to CAML and I can't find anything like a Not operator.

I looked at this question but it's on 2007, apparently before the <In> element was introduced. Apologies for the title, it wouldn't let me post it with a sensible one because of the question linked to.

Answer 1

In is just a short form that saves you from writing a big long <Or><Eq>... statement. In your case, you can do <And><Neq>...

<Where>
   <And>
     <Neq>
       <FieldRef Name='ObjectID' />
       <Value Type='Integer'>71</Value>
     </Neq>
     <Neq>
       <FieldRef Name='ObjectID' />
       <Value Type='Integer'>72</Value>
     </Neq>
   </And>
</Where>

Answer 2

I'm not sure any Not operator exists, so I guess you have to fetch all IDs and do a Where.

However, you can be tricky about it, since IDs generated in SharePoint start from 1 and steadily increment upwards. You can't know the last ID just by counting items, one could delete a bunch in the middle, so you need to:

• Find the ID of last inserted item.
• Create a <Values> element containing all ids up to the largest ID, except those you don't want.
• Run a CAML with In.

I guess this would probably just as slow as a Where, or slower. But more fun, and fun is good.