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:

 CAML operators

Eqis equal
Neqis not equal
Gtgreater than
Geqgreater or equal
Ltless than
Leqless or equal
IsNullis null
BeginsWithbegins with string
Containscontains the string
<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:

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.

Gunnar Peipman

Gunnar Peipman is ASP.NET, Azure and SharePoint fan, Estonian Microsoft user group leader, blogger, conference speaker, teacher, and tech maniac. Since 2008 he is Microsoft MVP specialized on ASP.NET.

    13 thoughts on “Querying SharePoint lists

    • October 29, 2008 at 6:51 pm
      Permalink

      In our current project, we are using LINQ to query the Lists. It’s going ok, but I don’t really know whether it is considered a good practice or not. How is it compared to CAML in terms of performance and efficiency ?

    • October 29, 2008 at 10:44 pm
      Permalink

      Can you give me some examples about how do you use LINQ on SharePoint lists? When talking about performance you should always use CAML queries.

    • October 30, 2008 at 2:43 am
      Permalink

      It looks similar to this:

      var x = from d in SPContext.Current.Web.Lists[“YOUR_LIST NAME”].Items.GetDataTable().AsEnumerable()

      where d[“ATTRIBUTE”].Equals(SomeValue)

      select d;

      it looks more like traditional SQL and you can even do joins.

    • October 30, 2008 at 9:40 am
      Permalink

      Reggio, this code has hard performance loss on larger lists. Just take reflector and check out what is going on inside Items property of SPList. You may be surprised. :)

    • July 16, 2009 at 2:24 pm
      Permalink

      …if <Today Offset=”1″ /> does not work for you, try <Today OffsetDays=”1″ />

    • August 3, 2009 at 3:24 am
      Permalink

      Hi,

      I am trying to get listitems using the same method. I have a order no field in the list and some other fields are Customers which is a lookup and date which is a calculated column and Agency which is a text.

      I am querying by requestno

      I get Agency as it is a simple text, but I can’t seem to get Customer name and Date as one is a lookup and one is a date. it is my necessity that I query by order number only and for each order number, get the rest of the data for each listitem.

      Can you share your ideas please?

    • February 24, 2010 at 7:07 pm
      Permalink

      Hi Gunnar,

      I’m trying to get this CAML to work. I’m doing this from within SPD and this is the selectcommand of a spdatasource. For some reason it doesn’t do anything with the GroupBy tag. Is it even supported in this scenario ?

      Also, I want to format the output of this query to something like “MM/yyyy”. Does CAML support Date Formatting in any way ?

      I would really appreciate your help on this.

    • February 27, 2010 at 10:02 pm
      Permalink

      Hi Juan and thanks for your question. CAML has very strict syntax of dates. You have to format dates appropriately so CAML can parse them.

    • August 17, 2010 at 3:54 am
      Permalink

      Thank you so much.

    • January 26, 2011 at 8:13 pm
      Permalink

      When you have

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

      it should be

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

      Caused me some problems before :)

    • August 12, 2011 at 12:06 am
      Permalink

      Hi

      Thanks for the post. I have quick question I need to write a query like this but i donot have access to code behind How can I achieve this with the SP designer 2007?

      I have a list and I need to select distict value of a perticular column.

      Thanks,

      Lakshmi

    • September 26, 2012 at 1:17 pm
      Permalink

      Your article was excellent and eurtdie.

    Leave a Reply

    Your email address will not be published. Required fields are marked *