© 2011 By: Dov Trietsch. All rights reserved

Passing the CAML thru the EY of the NEEDL

Definitions: CAML (Collaborative Application Markup Language) is an XML based markup language used in Microsoft SharePoint technologies 

Anonymous: A camel is a horse designed by committee 

Dov Trietsch: A CAML is a HORS designed by Microsoft 

I was advised against putting any Camel and Sphinx rhymes in here. Look it up in Google! 

_____

Now that we have dispensed with the dromedary jokes (BTW, I have many more, but they are not fit to print!), here is an interesting problem and its solution. 

We have built a list where the title must be kept unique so I needed to verify the existence (or absence) of a list item with a particular title. Two methods came to mind: 

1: Span the list until the title is found (result = found) or until the list ends (result = not found). This is an algorithm of complexity O(N) and for long lists it is a performance sucker.

2: Use a CAML query instead. Here, for short list we’ll encounter some overhead, but because the query results in an SQL query on the content database, it is of complexity O(LogN), which is significantly better and scales perfectly.

Obviously I decided to go with the latter and this is where the CAML s--t hit the fan.  

A CAML query returns a SPListItemCollection and I simply checked its Count. If it was 0, the item did not already exist and it was safe to add a new item with the given title. Otherwise I cancelled the operation and warned the user. The trouble was that I always got a positive. Most of the time a false positive. The count was greater than 0 regardles of the title I checked (except when the list was empty, which happens only once).

This was very disturbing indeed. To solve my immediate problem which was speedy delivery, I reverted to the “Span the list” approach, but the problem bugged me, so I wrote a little console app by which I tested and tweaked and tested, time and again, until I found the solution. Yes, one can pass the proverbial CAML thru the ey of the needle (e’s missing on purpose).

 So here are my conclusions: 

CAML that does not work: 

Note: QT is my quote:  char QT = Convert.ToChar((int)34);
string titleQuery = "<Query>><Where><Eq>";
titleQuery += "<FieldRef Name=" + QT + "Title" + QT + "/>";
titleQuery += "<Value Type=" + QT + "Text" + QT + ">" + uniqueID + "</Value></Eq></Where></Query>";
titleQuery += "<ViewFields><FieldRef Name=" + QT + "Title" + QT + "/></ViewFields>"; 

Why? Even though U2U generates it, the <Query> and </Query> tags do not belong in the query that you pass. Start your query with the <Where> clause. 

Also the <ViewFiels> clause does not belong. I used this clause to limit the returned collection to a single column, and I still wish to do it. I’ll show how this is done a bit later.  

When you use the <Query> </Query> tags in you query, it’s as if you did not specify the query at all. What you get is the all inclusive default query for the list. It returns evey column and every item. It is expensive for both server and network because it does all the extra processing and eats plenty of bandwidth.  

Now, here is the CAML that works 

string titleQuery = "<Where><Eq>";
titleQuery += "<FieldRef Name=" + QT + "Title" + QT + "/>";
titleQuery += "<Value Type=" + QT + "Text" + QT + ">" + uniqueID + "</Value></Eq></Where>"; 

You’ll also notice that inside the unusable <ViewFields> clause above, we have a <FieldRef> clause. This is what we pass to the SPQuery object.

Here is how: 

SPQuery query = new SPQuery();
query.Query = titleQuery;

query.ViewFields = "<FieldRef Name=" + QT + "Title" + QT + "/>";
query.RowLimit = 1;
SPListItemCollection col = masterList.GetItems(query); 

Two thing to note: we enter the view fields into the SPQuery object and we also limited the number of rows that the query returns. The latter is not always done, but in an existence test, there is no point in returning hundreds of rows. The query will now return one item or none, which is all we need in order to verify the existence (or non-existence) of items. Limiting the number of columns and the number of rows is a great performance enhancer.

That’s all folks!!