I’m on a team developing a solution for a client of ours that involves an out-of-browser Silverlight UI with backend communication to both SharePoint 2010 and another data source. SharePoint lists are used as the data repository for much of the application, with foreign keys to the other data source stored as columns in the SharePoint lists for relating data. Due to the nature of the other data source, the “foreign keys” are really long strings that are guaranteed unique but appear to be just base64-encoded strings.
Communication between Silverlight and the SharePoint lists is done via SharePoint 2010’s RESTful WCF Data service and communication to the other data source is done via standard WCF SOAP-based web services.
Now that I’ve framed the application, on to the problem. The general application workflow is the following:
– User launches a ‘Create’ wizard experience to generate a new record in the non-SharePoint data source.
– The key from that source is used to seed new records in SharePoint
– Once new SharePoint records are ready, extra-metadata about the current record is stored in SharePoint.
Seems simple. That is, it’s simple until you start to find pre-existing records for your “unique” IDs from the other data source, then it becomes Data Corruption!
The Root Cause
Using the application’s tracing mechanism, I found that the ultimate cause of the bug appeared to be a case-insensitive string comparison between the foreign keys from the other data source as they were retrieved from the SharePoint lists. Example:
If our code logged that the outgoing ID was something like “AbCdEFg”, the next log entry would indicate that the returning ID was “aBCdEFg”…
After scouring our code base for any case-insensitive string comparisons and coming up empty, I was left with the theory that one of our underlying technologies must be doing this. I took a look at WCF Data Services first and found the following statement from a MSFT employee on the team that wrote the technology (source here):
“The WCF Data Services framework doesn’t make any assumptions on the data flowing through the system.” – Phani Raju
That is a pretty definitive statement stating that WCF Data Services is pass-through only. That leaves only SharePoint…
SharePoint’s query language is CAML. If you follow the link, you’ll find that the very first thing that MSDN tells you is “CAML is case-sensitive.” This statement is very misleading. I used the U2U CAML Query Builder tool found here to perform the exact ID-based query I was executing in WCF Data Services and found the exact same result! The incorrect ID is returned. I did some more research (see web searching…) and found this MSDN thread that confirms my findings.
So what does this all mean? Well, it means that while CAML is a case-sensitive language in syntax, it’s querying capabilities aren’t. It also means that a buyer should beware when consuming WCF Data Services custom providers because you are at their mercy when it comes to obscure requirements. Finally, it ultimately means that any time you query SharePoint based on a string comparison, you should double check the results in your code upon retrieval to validate the results.
Oh, and that strings are obviously bad key values to begin with.
– Nick Nieslanik