CAML Query Quirk

Recently I had the requirement to be able to retrieve items from a SharePoint list by using CAML queries in a custom webpart. I know the preferred way is to use LINQ to SharePoint, but the particular problem couldn’t be solved by that (and management clearly wanted CAML).

The problem

We store user preferences in an SQL Server table, and in this case, we had negative preferences, things the user doesn’t want to see. The task was to filter a list by a lookup field where the values doesn’t fall in the user preferences. I quickly thought the following:

<Query>
 <Where>
  <And>
   <Neq>
    <FieldRef Name=’MyField’ LookupId=’TRUE’/>
    <Value Type=’Lookup’>1</Value>
   </Neq>
   <Neq>
    <FieldRef Name=’MyField’ LookupId=’TRUE’/>
    <Value Type=’Lookup’>2</Value>
   </Neq>
  </And>
 </Where>
</Query>

Of course the actual query was generated by some C# code, nothing hardcoded.

And it worked nice – I left it as it is. One or two days later the code started to break down – no results arrived from the list. It took a while to figure out that the only thing changed was that someone added a third option to the preferences. After fiddling around with for a while it became apparent that the query is working with two values, but not with three (or for the record, anything higher than two).

After some hours of looking around I found a someone commenting on this behavior – CAML queries work only with two (or one) elements on a given level. So the following is fine:

<Query>
 <Where>
  <And>
   <Neq>
    <FieldRef Name=’MyField’ LookupId=’TRUE’/>
    <Value Type=’Lookup’>0</Value>
   </Neq>
  </And>
  <And>
   <Neq>
    <FieldRef Name=’MyField’ LookupId=’TRUE’/>
    <Value Type=’Lookup’>1</Value>
   </Neq>
   <Neq>
    <FieldRef Name=’MyField’ LookupId=’TRUE’/>
    <Value Type=’Lookup’>2</Value>
   </Neq>
  </And>
 </Where>
</Query>

But should you add another And tag in the Where element it would break again, so you’d had to break it up like the Neq tags.

Solution

I thought a lot about how to solve this problem recursively – but found a much more practical method instead: I switched the filter from specifying values to exclude into stating what should be included. I used the In element, which has a Values element, where you can specify multiple (yes, more than two, if you wish) values to search for.

Conclusion

I’m astonished that this issue persisted in generations of SharePoint but maybe we should just as well opt for using LINQ to SharePoint and forget how things work closer to the metal (I’m joking of course).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s