I've just spent the last, uh, 6 hours trying to figure out why a query I've been writing in SharePoint would not filter the returned data properly. It would always return more data than I was anticipating. Consider the following snippet:

sopv = "{" + soplib.Views["WS_Browse"].ID.ToString().ToUpper() + "}";
sopq = new CamlQuery(CAML.Where(yaddi yaddi yadda)).CreateQuery();
sopmtch = soplib.GetItems(sopq, sopv);

In the first line I'm fetching a friendly handle to the name of a view I've built in a document library. I had constructed the view inside SharePoint and specified the columns I wanted returned as well as including a filter so it would weed some of the gunk out before my own filter ran over it in the query.

In the second line I'm using John Holliday's CAML.net to build the query xml, and in the last line I'm running the query, specifying the query object and the view name in the method call.

For the life of me I couldn't figure out why in the Sam Hill it was returning more items than I was expecting. It wasn't returning everything, but just one or two more than I was expecting. And the one or two extra were definitely outside the range I was yaddi yaddi yadda'ing about.

So my next step was to crack open U2U's CAML Query Builder application and reconstruct the query in what I knew was a working a environment. Voila, the query worked perfectly. And theeeeeen, I outputted the actual query xml from my SPQuery object (sopq) and compared it to the query xml generated by U2U's tool. Identical!

At this point I was really getting creeped out, thinking that there was some bug in using the SharePoint object model to run queries vs. using the SharePoint web services (which is what I was doing with the U2U tool). As a last ditch effort I removed the view specification from the GetItems method. BINGO.

So, you know what? I learned something today! If you specify a view to use in your query the SharePoint query processor happily ignores the CAML you've whipped up. Not very nice considering the effort it takes to to put one together that doesn't get stuck in SharePoint's throat.

And while I have your attention, I'd like to make the following public service announcements:

1. John Holliday's CAML.Net is a great shortcut for creating CAML, although even a somewhat simple query can lose you if you put it all on one line. I'll have to check out his Visual Studio plugin.


2. The U2U CAML Query Builder rocks. I would actually use it more often and just store the raw, generated queries in text files, but I wanted the flexibility of making queries up on the fly. At the very least it's a good debugging tool.


3. The existence of CAML is hella-silly. I mean, it's just dreadful. XML and SQL, two not-so-great tastes that taste like [ expletive deleted ] together.


4. Programming data operations in SharePoint, in general, is an abomination. In fact, at the end of a day of programming data operations in SharePoint, I can't wait to stop and ram shards of glass in my eyes. For you DBA types, just consider writing queries as I have described in the previous point. Now consider doing that plus doing data joins manually. Hallelujah... where's the Tylenol!

Labels: sharepoint