This post concerns yet another odd “feature” exhibited when using CAML to query List or Document Library content.

When you create a new Document Library you get a set of default columns, “Name” and “Title” amongst them. By default when you upload a document to a document library the “Name” column metadata is populated with the document filename and the “Title” column is left empty. On the default All Documents view the “Title” column is not displayed.

As shown above I’ve got a document library with folders and files in, what I want to do is query this doc lib (and it’s folders and subfolders) for documents where the document name contains some string value. So consider the following CAML query:

<Query>
   <Where>
      <Contains>
         <FieldRef Name='LinkFilename' />
         <Value Type='Text'>token</Value>
      </Contains>
   </Where>
   <OrderBy><FieldRef Name='FileDirRef' /></OrderBy>
</Query>
<ViewFields>
   <FieldRef Name='ID' />
   <FieldRef Name='LinkFilename' />
   <FieldRef Name='FileDirRef' />
</ViewFields>
<QueryOptions>
   <ViewAttributes Scope='Recursive' />
   <OptimizeFor>FolderUrls</OptimizeFor>
</QueryOptions>

It looks ok, but when you submit this query to SharePoint you get an error message:

“Invalid column name c2”

In fact the c2 part could be any number, but it was c2 in my case. LinkFilename in case you’re wondering is the internal column name of the “Name” column. After much head scratching and teeth gnashing, it turns out that you can’t use the LinkFilename column in a Where clause, you can use it in the OrderBy clause just fine, for no apparent reason I can fathom.

Ok so if you can’t use LinkFilename in a Where clause, you can alternatively use the FileLeafRef column which contains the document name part of the document URL prefixed with it’s list ID as shown below:

So my final query which gives me the results I want is:

<Query>
   <Where>
      <Contains>
         <FieldRef Name='FileLeafRef' />
         <Value Type='Text'>token</Value>
      </Contains>
   </Where>
   <OrderBy><FieldRef Name='FileDirRef' /></OrderBy>
</Query>
<ViewFields>
   <FieldRef Name='ID' />
   <FieldRef Name='LinkFilename' />
   <FieldRef Name='FileDirRef' />
   <FieldRef Name='FileLeafRef' />
</ViewFields>
<QueryOptions>
   <ViewAttributes Scope='Recursive' />
   <OptimizeFor>FolderUrls</OptimizeFor>
</QueryOptions>

This gives me all the documents within a document library, its folders and subfolders, where the document name contains a specific string. The cool part of that statement (querying across folders and subfolders) is accomplished by setting the Scope option of the ViewAttributes property of the SPQuery class. The Scope option relates to the Scope property of the SPView class and it’s permissable values are:

As  you may have noticed I was using U2U’s excellent CAML Query Builder to create and submit CAML queries.