Querying SharePoint lists

The best way to get data from SharePoint lists is to query them. Using queries you guarantee that only list items you really need are retrieved from database and constructed to objects by SharePoint. Most of the time you don’t need the full collection of list items, so it is not point to ask all the items and then use only few of these. Let’s see how we can query SharePoint lists.

CAML queries

At first, let’s see how queries are built in CAML (Collaborative Applications Mark-up Language). Mostly we will work on select conditions of CAML queries that are translated to WHERE clause when query is executed. Here is little example of CAML query WHERE part.

<Where>
  <Eq>
    <FieldRef Name="Pet" />
    <Value Type="Text">Rabbit</Value>
  </Eq>
</Where>

This query selects all list items that have value Rabbit on field Pet. <Eq> is same as "=". FieldRef specifies the field and in this case we are specifying field by its name. It is also possible to specify the field by its unique ID (GUID).

Syntax

Let’s look at CAML queries syntax for a moment. Syntax of conditions is like this:

<Where>
  <Eq>
    <FieldRef Name="Pet" />
    <Value Type="Text">Rabbit</Value>
  </Eq>
</Where>

<Eq> means "equal" and works as sign "=". CAML is trickier than it seems at first sight. Suppose you have query that has three AND conditions. How to write this kind of query? Let’s try this way.

<Where>
  <And>
    <Eq>
      <FieldRef Name="Pet" />
      <Value Type="Text">Rabbit</Value>
    </Eq>
    <Lt>
      <FieldRef Name="Age" />
      <Value>5</Value>
    </Lt>
    <BeginsWith>
      <FieldRef Name="Name" />
      <Value>Tu</Value>
    </BeginsWith>
  </And>
</Where>

and let’s try to run it. This query is supposed to find all less than 5 year old rabbits whose name starts with letters Tu. But… hmm… there are some errors. Now let’s see correct version of this query.

<Where>
  <And>
    <And>
      <Eq>
        <FieldRef Name="Pet" />
        <Value Type="Text">Rabbit</Value>
      </Eq>
      <Lt>
        <FieldRef Name="Age" />
        <Value>5</Value>
      </Lt>
    </And>
    <BeginsWith>
      <FieldRef Name="Name" />
      <Value>Tu</Value>
    </BeginsWith>
  </And>
</Where>

As you can see there is more nesting than you thought at first place. Don’t forget it when writing more complex queries.

Problems with CAML queries

CAML queries may seem innovative at first sight. You know, everything that is XML and more better – everything that is like SOA or some other hot stuff – seems important. But there are many problems related to CAML queries. You should know these problems. Otherwise you may imagine that CAML is like SQL and put all your cards on CAML queries. When you once understand the limits it may be too late.

1. CAML queries are not as powerful as SQL queries. You can use only limited set of functions and conditions.
2. There are many crucial things related to SharePoint fields you should know. Otherwise you waste a lot of time to debug queries to find out why they are not working.
3. There are no joins like in SQL. You can use values of lookup fields but not values from other lists. 
4. CAML queries are not very convenient to read and write in code. Of course, you can always write your own helper classes or use existing ones. By example:
   • CAML.NET
   • Yet Another CAML Query tool (2)

Query options

There are some options you can specify when quering lists. SPQuery has property called ViewAttributes.

Scope

Using Scope attribute you can search from all folders of list. If you have folders then by default the search is made only from root folder. If you need also items from subfolders the use the following code.

query.ViewAttributes = "Scope=\"Recursive\"";

If you want to query some specific subfolder you can set the Folder property of query as follows.

query.Folder = list.ParentWeb.GetFolder("My Documents/Invoices");

RowLimit

Row limit is very important attribute as you can use it to limit the number of results returned by query. Using row limit right from start may save you in the future when some list grows very big and users really doesn’t need all results your query returns. You can use the following code to set row limit of SPQuery.

query.RowLimit = 100;

There are also some other options you can set with query. SharePointMagazine has great article by Karine Bosch Writing CAML Queries For Retrieving List Items from a SharePoint List.

Tips and tricks

I will offer now some tips and tricks that may save you a lot of time.

Lookup fields

Lookup fields have two different value: one value is for bound item ID and the other for bound item value. The value of lookup field is something like this when value is serialized: #20;Rabbit.

To query the lookup field by value you can use the following query.

<Where>
  <Eq>
    <FieldRef Name="Pet" />
    <Value Type="Lookup">Rabbit</Value>
  </Eq>
</Where>

To find Rabbit by its ID you can use this query.

<Where>
  <Eq>
    <FieldRef Name="Pet" LookupId="True" />
    <Value Type="Lookup">20</Value>
  </Eq>
</Where>

Note that FieldRef node has LookupId attribute specifying that we need that ID part of lookup field.

DateTime fields

DateTime fields are the tricky ones when first dealing with them. Let’s take a look at the following query.

<Where>
  <Eq>
    <FieldRef Name="BirthDateTime" />
    <Value Type="DateTime">1977-12-13T23:00:00Z</Value>
  </Eq>
</Where>

This query returns all birth moments that have date 13.12.1977. Only date part is checked. We have to update our query a little bit to get this one row we are looking for.

<Where>
  <Eq>
    <FieldRef Name="BirthDateTime" IncludeTimeValue="True" />
    <Value Type="DateTime">1977-12-13T23:00:00Z</Value>
  </Eq>
</Where>

This query returns only these list items that have birth moment as 13.12.1977 23:00:00.

You can also use Today placeholder in your queries to get today’s date.

<Where>
  <Eq>
    <FieldRef Name="BirthDateTime" />
    <Value Type="DateTime">
      <Today />
    </Value>
  </Eq>
</Where>

You can specify offset in days to get yesterday and tomorrow dates. First two queries are examples about it. First one is for yesterday and the second one for tomorrow.

<Where>
  <Eq>
    <FieldRef Name="BirthDateTime" />
    <Value Type="DateTime">
      <Today Offset="-1" />
    </Value>
  </Eq>
</Where>
 
<Where>
  <Eq>
    <FieldRef Name="BirthDateTime" />
    <Value Type="DateTime">
      <Today Offset="1" />
    </Value>
  </Eq>
</Where>

Boolean fields

You should not use named constants True and False as values of Boolean fields when querying list with CAML query.

<Where>
  <Eq>
    <FieldRef Name="JumpingAround" />
    <Value Type="Boolean">1</Value>
  </Eq>
</Where>

Use 0 as False and 1 as True.

There are many more things you can do with CAML queries but let’s take a look at these features at some other blog entries. For now you should be able to query lists using CAML queries and use some additional options you can specify.