Karine Bosch’s Blog

On SharePoint

CAML and the Client Object Model


SharePoint 2010 comes with a whole set of new features. One of these novelties is the Client Object Model. (Well it are 3 novelties because there are 3 different Client Object Models that you can use depending on the situation you are in).

When SharePoint 2010 came out, there were some rumours that CAML was dead because now we have LINQ to SharePoint. But these rumors are not true: CAML is still there and is still very important for the performance of your SharePoint application. In this blog post I’m going to detail how CAML can be used with the .NET Client Object Model.

Working with the .NET Client Object Model

Before you can start working with the .NET Client Object Model, you have to add a reference to the assemblies Microsoft.SharePoint.Client.dll and Microsoft.SharePoint.Client.Runtime.dll. You can find these assemblies in the 14\ISAPI folder.

The Where clause

The Where clause can range from very simple to very complex with one or more nested <And> or <Or> elements. The CAML for the Where clause has not been changed since SharePoint 2003, and can be used with the SharePoint Server Object Model and with the three Client Object Models. Also the Web Services are still there, for which you can also use CAML to retrieve and/or update list items.

To retrieve list items from a SharePoint list, you have to define an instance of type CamlQuery, defined within the Microsoft.SharePoint.Client.CamlQuery namespace. You can specify your CAML query within the ViewXml property. This property is of type string but its content must be XML. The root element for this property is <View>. The Where clause needs to be embedded within a <Query> element.

Microsoft.SharePoint.Client.CamlQuery query = new Microsoft.SharePoint.Client.CamlQuery();
query.ViewXml = "<View>"
   + "<Query>"
   + "<Where><Eq><FieldRef Name='Country' /><Value Type='Text'>Belgium</Value></Eq></Where>"
   + "</Query>"
   + "</View>";
// execute the query
ListItemCollection listItems = spList.GetItems(query);
clientContext.Load(listItems);
clientContext.ExecuteQuery();

OrderBy

The OrderBy element is the most simple one: you can define a sort order using one or more <FieldRef> elements that you include in the ViewXml property of the CamlQuery object:

query.ViewXml = "<View>"
   + "<Query>"
   + "<Where><Eq><FieldRef Name='Country' /><Value Type='Text'>Belgium</Value></Eq></Where>"
   + "<OrderBy><FieldRef Name='City'/></OrderBy>"
   + "</Query>"
   + "</View>";

ViewFields

You can also limit the number of columns returned to the client, using the good old ViewFields element, which you can also include in the ViewXml property of the CamlQuery object:

CamlQuery query = new CamlQuery();
query.ViewXml = "<View>"  
   + "<Query>"
   + "<Where><Eq><FieldRef Name='Country' /><Value Type='Text'>Belgium</Value></Eq></Where>"
   + "</Query>"
   + "<ViewFields>"
   + "  <FieldRef Name='Title' /><FieldRef Name='City' />"
   + "</ViewFields>"
   + "</View>";
// execute the query
ListItemCollection listItems = spList.GetItems(query);
clientContext.Load(listItems);
clientContext.ExecuteQuery();

But this also returns a number of system columns. If you really want to limit the columns returned to the columns you specify, you have to use a LINQ query within the Load method. The code looks as follows:

CamlQuery camlQuery = new CamlQuery();
camlQuery.ViewXml = "<View><Where><Eq><FieldRef Name='Country' /><Value Type='Text'>Belgium</Value></Eq></Where></View>";
ListItemCollection listItems = spList.GetItems(camlQuery);
clientContext.Load(listItems,
      items => items.Include(
      item => item.Id,
      item => item.DisplayName,
      item => item.HasUniqueRoleAssignments));
clientContext.ExecuteQuery();

Query Options

The different Query Options need to be handled a bit differently than with the SharePoint Server Object Model.

The row limit can also be specified within the ViewXml property:

query.ViewXml = "<View>"
   + "<Query>"
   + "<Where><Eq><FieldRef Name='Country' /><Value Type='Text'>Belgium</Value></Eq></Where>"
   + "<OrderBy><FieldRef Name='City'/></OrderBy>"
   + "</Query>"
   + "<RowLimit>5</RowLimit>"
   + "</View>";

Dates in UTC

You can choose to return dates in UTC (Coordinated Universal Time)  by setting the DatesInUtc property of the CamlQuery instance:

query.DatesInUtc = true;

Include attachment URLs

Using CAML you are able to know if list items have attachment by adding a w> element to the ViewFields element in the ViewXml property:

query.ViewXml = "<View>"  
   + "<ViewFields>"
   + "  <FieldRef Name='Title' /><FieldRef Name='City' /><FieldRef Name='Attachments' />"
   + "</ViewFields>"
   + "</View>";

 SharePoint will return a boolean indicating whether the list item has attachments or not.

The attechments are not stored in the list item itself, but are stored in a sub folder of the list. More specifically, the list contains a folder named Attachments and if a list item has one or more attachments, a folder is created based on the ID of the list item. This sub folder will then contain the attachment(s). The URL of the attachment is not stored in the list item itself.

CAML contains an option IncludeAttachmentURLs that can be used to retreive the URL of the attachment(s), together with the other properties of the list item. It works on the server side SPQuery and with the <QueryOptions> node of the GetListItems method of the Lists.asmx web service , but it doesn’t seem to be available with the CamlQuery object of the .NET Client Object Model.

If you need to retrieving the attachments itself you will have to write some extra code that retrieves the files from the Attachment folder:

Folder folder = clientContext.Web.GetFolderByServerRelativeUrl(
    spList.RootFolder.ServerRelativeUrl + "/Attachments/" + item.Id);    
FileCollection files = attFolder.Files;    
// If you only need the URLs    
ctx.Load(files, fs => fs.Include(f => f.ServerRelativeUrl));    
ctx.ExecuteQuery();

Limitations

Following CAML subtilities doesn’t seem to be working with the CamlQuery object of the .NET Client Object Model, although they exist when retrieving list items with the server object model and the SharePoint web services:

  • IncludeMandatoryColumns: this option also returns the required fields besides the other fields specified in the ViewFields property or element.
  • ExpandUserField: when you query a User field, you only see the login name of the user. When you indicate that you want to expand a user field, SharePoint will also return information like the user name and the email address.
  • IncludeAttachmentURLs: cfr. higher
  • IncludeAttachmentVersion:

Files and folders options

CAML for retrieving files and folders at different levels of a document library, is always a bit more complex. To make the explanation hereunder a bit more readable, I have created a document library with the following structure:

You can easily query the files and folders in the root folder of a document library without having to use specific CAML elements. Only if you want to start querying the folder structure within a document library, you have to apply specific CAML.

To be able to better demonstrate the subtilities I created a folder structure in my Shared Documents library, and added a set of files to the different folders. (Needless to say that SharePoint Manager 2010 provided me this insight in my document library :) ).

 

 

 

 

 

 

 

For example, if you want to query all files and folders in your document library, no matter how deep they are nested,  you have to add a Scope attribute to the View element, and set its value to RecursiveAll:

query.ViewXml = "<View Scope='RecursiveAll'></View>";

You can always add a Query element in the View element and specify a Where clause to add an extra filter to the query, or an OrderBy clause to sort the result.

If you want to query only the folders, you have to add an extra where clause:

query.ViewXml = "<View Scope='RecursiveAll'>"
   + "<Query>"
   + "   <Where>"
   + "      <Eq><FieldRef Name='FSObjType' /><Value Type='Integer'>1</Value></Eq>"
   + "   </Where>"
   + "</Query>"
   + "</View>";

If you want to query only the files, the extra where clause can be changed as follows:

query.ViewXml = "<View Scope='RecursiveAll'>"
   + "<Query>"
   + "   <Where>"
   + "      <Eq><FieldRef Name='FSObjType' /><Value Type='Integer'>0</Value></Eq>"
   + "   </Where>"
   + "</Query>"
   + "</View>";

If you want to retrieve the content of a specific folder, i.e files and folders, you have to add the relative URL to that folder to the FolderServerRelativeUrl property of the query instance:

query.FolderServerRelativeUrl = "/Shared Documents/Folder 1";

If you only want to see the files of a specific sub folder, you have to set the Scope attribute of the ViewXml property to FilesOnly:

query.ViewXml = "<View Scope='FilesOnly' />";
query.FolderServerRelativeUrl = "/Shared Documents/Folder 1";

Of course you can also query all files in a specific sub folder and its underlying sub folders. In that case you also have to specify the relative URL to the folder, but you also have to set the Scope attribute of the ViewXml property to Recursive:

query.ViewXml = "<View Scope='Recursive' />";
query.FolderServerRelativeUrl = "/Shared Documents/Folder 1";

If you want to retrieve all files AND folders from a specific folder and its underlying sub folders, you have to set the Scope attribute of the ViewXml property to RecursiveAll

query.ViewXml = "<View Scope='RecursiveAll' />";
query.FolderServerRelativeUrl = "/Shared Documents/Folder 1";

And for all those who read through the whole post: Yes, CAML girl is back :)

About these ads

February 3, 2012 - Posted by | SharePoint 2010

26 Comments »

  1. Lokk at http://camljs.codeplex.com/

    Comment by Dkm Sci (@DkmSci) | February 3, 2012 | Reply

  2. Thanks Karine and its very useful and productive to me.

    Comment by Kalyan | May 10, 2012 | Reply

  3. Any way to do recursive but only go 3 levels deep in the tree?

    Comment by abhubbart | May 17, 2012 | Reply

  4. Any way to limit the recursive function to folders only 3 levels deep into the tree?

    Comment by abhubbart | May 17, 2012 | Reply

  5. Hi abhubbart, I’m affraid that’s not possible. Or you query the current folder, or you query all folders deep.

    Comment by Karine Bosch | May 18, 2012 | Reply

  6. Thanks. I was stuck with getting the items out of a specific folder. I’ll try your solution. Thanks for sharing :)

    Comment by bryian tan | June 2, 2012 | Reply

  7. I’ve been trying to figure out how to list the items in folders for hours. You would not believe how many suggestions on the web did not work for me. Finally, something that does — the Scope attribute on . Thank you, thank you, thank you! (For others who might be going through this, remember that the case matters; “scope” won’t cut it.)

    Comment by Jim Owens | July 22, 2012 | Reply

  8. [...] with the Client Object model. You have to add additional code to retrieve the attachments. Read my blog post on the client object model for more [...]

    Pingback by Caml Designer » Caml Designer 2010 | January 3, 2013 | Reply

  9. Great have Caml gril back!

    Comment by Tao | February 22, 2013 | Reply

  10. Thanks for the enthousiasm! :)

    Comment by Karine Bosch | February 23, 2013 | Reply

  11. Hi Karine,

    I have a requirement in my project. I need to checked out a page in the Pgaes library and edit the page content using webservice. For this right now I need to chcek whether the page alreday checked out or not. Fo rthe purpose I added the field in View fields.

    viewFields.InnerXml = ” “;

    I havent specified any queryOptions. Butr this i snot returning me the expected result.

    Could you please suggest how can achieve this ?

    Thanks in advance.

    Regards,
    Nimisha

    Comment by Nimisha | April 24, 2013 | Reply

    • Hi Nimisha,
      You posted your comment on a blog post on the Client Object Model. Can you just tell me if you need help on Client Object Model or Web Service?

      Comment by Karine Bosch | April 24, 2013 | Reply

  12. Hi Karine,

    Thanks a lot for the immediate reply.
    Yes, I need your help in Web Service. Iam using lists.asmx for returning all pages from Pages library and then editing the webpart page content uisng webpartpages.asmx.Iam running this tool against my site collection. So when I try to edit a page which is not checked out by current user , it throws SOAP exception. So now I need to check whether the page is already checked out by the current user, if else need to chcek out the file and edit the page.

    I hope you got my scenario.

    Could you please help me out ?

    Thanks.

    Regards,
    Nimisha

    Comment by Nimisha | April 24, 2013 | Reply

    • Nimisha,
      I think I can help you out with a code snippet but unfortunately I have it at home (and I’m at the office now). I’ll post it tonight. Hope you can wait for it.
      Karine

      Comment by Karine Bosch | April 24, 2013 | Reply

  13. Thanks alot Karine.

    Comment by Nimisha | April 24, 2013 | Reply

  14. [...] with the Client Object model. You have to add additional code to retrieve the attachments. Read my blog post on the client object model for more [...]

    Pingback by CamlDesigner » CamlDesigner 2010 | April 27, 2013 | Reply

    • Hi Nimisha, sorry for the late reply but we had our SharePoint Saturday last saturday and that requested a lot of my free time. You can check if a file is checked out or not using the web services but unfortunately the action of checking it out does not exist. If you need this in a web service, you will have to write your own web service that runs against the server-side object model and then call this method from your web part code.

      Kind regards,

      Karine

      Comment by Karine Bosch | April 29, 2013 | Reply

  15. […] query.ViewXml = “<View Scope=’RecursiveAll’ />”; query.FolderServerRelativeUrl = “/Shared Documents/Folder 1″; ——————————————————————– Ref:http://karinebosch.wordpress.com/2012/02/03/caml-and-the-client-object-model/ […]

    Pingback by Sample Code to Load data from SharePoint List Using CamlQuery | Share your knowledge | May 31, 2013 | Reply

  16. Hi. Great post – its really helped me. I’m trying to query a Task list to find which users or groups have been assigned a task. The code listItem["AssignedTo"] doesn’t return the property value. I can see the property value in the visual studio debugger so I know it’s loaded but how to access in the running code. I have no issue reading text type properties like Title or Body. Any ideas?

    Comment by Jasper | June 18, 2013 | Reply

  17. Great post. Maybe you can help with a small problem. I am either having a challenge in understanding exactly how to use FolderServerRelativeURL OR it does not avoid the 5,000 limit issue.

    1) I’ve set the FolderServerRelativeUrl. After a bit of playing around I found it must include the whole. I constructed from the pieces off of the LIST object:
    myList.PafrentWebUrl + “/” + myList.Title + “/my folder name/my sub folder name”
    This works fine.
    My test list has 65,000 items. If my first item in the where statement gets less than 5,000 hits I’m OK. If my first item gets more than 5,000 hits I get the threshold limit.

    Now I try the solution using the FolderServerRelative URL.

    I set myQuery.FolderServerRelativeURL to the full path.

    I set scope to Recursive. (don’t want the folders).

    I fire the query and hit the 5,000 limit even though the sub folder and its children only have 2,200 items.

    I then add an extra where statement for the FileDirRef = myFolderRelativeUrl.

    Still hit the 5,000 limit.

    My understanding is that using a folderSrverRelativeUrl should avoid the 5,000 limit as long as there are less than 5,000 items in the specified folder or its children?

    Am I missing something?

    HELP!!

    Thanks,

    Savin

    Comment by Savin Smith | September 10, 2013 | Reply

    • Hi Savin,

      I tend to think like you: if the folder doesn’t contain 5000 items and the query is built up correctly, you shouldn’t hit the threshold. I never tested my queries against a list of 65.000 items but I’ll do that somewhere during the weekend.
      Kind regards,
      Karine Bosch

      Comment by Karine Bosch | September 11, 2013 | Reply

      • Hi,
        Were you able to resolve the Problem . Actually I am also facing the same issue.

        Comment by Anubhav | February 5, 2014

      • Hi Anubhav, I was not able to solve the issue. I think it is better to post your question to the forum and see if you can get a response there. Of course I’m curious to know how, if you are able to solve the issue.
        Kind regards,
        Karine Bosch

        Comment by Karine Bosch | February 6, 2014

  18. How to fetch the all the list items from Spcific view instead of fetching all list items from lists

    Comment by Sivaramakrishnan | December 10, 2013 | Reply

  19. Thanks a lot..It helps.

    Comment by Buddhi Madarasinghe | April 4, 2014 | Reply


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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 155 other followers

%d bloggers like this: