Blog Archives

Querying Large Lists using CAML

Struggling with this one for a large point of the day, before I realised what was happening.  I was running a query using SPQuery and CAML against a list that needed to be restricted for a subset of users.  I was happily running it as me, but my test (restricted) user was getting Null reference errors.

I quickly realised that my list had over 36,000 items in it (split into folders of < 2000 items) so I had to come up with a way of running the same CAML query over the list independent of user.

There are number of options (well explained by <this excellent blog entry by Steve Peschka to deal with list thresholds and programmatic overrides.

SPList has an EnableThrottling property, which is set to True by default.  You can use the Object Model to set it to False temporarily.  The problem is that the permissions to do so cannot be done by anyone who isn’t a Farm Administrator.  SPSecurity.RunWithExecutivePrivileges run as the Application Pool account which is, unless you’ve configured something wrongly should not be a Farm Admin level account.

To cut a long story short, you need to either do this in code or Powershell when the list is created.  Alternatively, if your list has popped over the item count threshold, you can do it at any time.  The Powershell script is simple.

$url="<%YOUR URL HERE%>"
$listname="<%YOUR LIST NAME HERE%>"
$web = Get-SPWeb $url
$list = $web.Lists[$listname]
$list.EnableThrottling = $false

To switch throttling back on, simply re-run the script, changing the $false to $true.

The beauty of this is that it applies on a list by list basis, so you don’t have to go into Central Administration and make Web Application wide changes to get a single large list to query.  The script is modified from one published by Dina Ayoub.


Using [Me] in CAML Queries

Sometimes you need to put a custom View into a List Definition.  That View could be based on the current user (for example “Show all List Items assigned to me”).  The problem is that the usual GUI method uses [Me] to filter the items appropriately, which breaks CAML.

The trick is twofold.  First, the Value Type is “User” and second, the magic value is <UserID />.  Therefore your code is.

    <FieldRef="AssignedTo" />
      <Value Type="User"><UserID /></Value>

Hat Tip: Macedonian SharePoint Group (which incidentally also demonstrates how to use [Today] in CAML.  Also MSDN.