Considerations When Using View in CAML Search

I recently worked on a web part which processes test scores for students. Test scores are stored in a list, organized by course and by user. The web part should retrieve the test scores for the logged in user, then the user’s score for each course. A previous implementation used a view, My Scores, which showed test scores for the logged in user by defining a filter, “Username is equal to [Me]”.

It occurred to me to retrieve a user’s test score for a course by performing a CAML search on the My Scores view. Typically, when searching a list using a CAML query, you can do something like this:

SPQuery query = new SPQuery();
query.Query = <query for course>;
results = list.GetItems(query);

There is an overloaded version of the SPQuery constructor that takes an SPView. Additionally, SPList.GetItems has an overloaded version that also takes an SPView. Given the availability of the SPView parameter, I was tempted to perform the CAML search on the My Scores view by doing the following:

SPQuery query = new SPQuery(myScoresView);
query.Query =  <query for course>;
results = list.GetItems(query);

However, I got back test scores for all students, not just test scores in the My Scores view. It turns out that you can specify a CAML query manually in SPQuery.Query or use a view, but not both. In fact, if you define a filter on a view, the view will have its own CAML query, in addition to other attributes such as view fields and row limits. You can access the view’s CAML query in the SPView.Query property. If both the view’s CAML query and SPQuery.Query are set, they appear to override each other so that the search is performed based on the one which takes effect last.

To illustrate how this works, let’s look at the scenarios for a different search. I created a simple list with the following objects:

Title Color Shape
Red Square Red Square
Blue Square Blue Square
Red Circle Red Circle

Then I defined a view, Red Only, which contains only Red objects:

Title Color Shape
Red Square Red Square
Red Circle Red Circle

I specified a query for Blue objects by manually setting SPQuery.Query. Below are the results of different ways to invoke the search:

Code Results
1. Use default All Items view and no manual query
SPQuery query = new SPQuery();
results = list.GetItems(query);
Red Square
Blue Square
Red Circle
2. Use Red Only view
SPQuery query = new SPQuery(view);
results = list.GetItems(query);
Red Square
Red Circle
3. Override Red Only view with manual query
SPQuery query = new SPQuery(view);
query.Query = @"
<Where>
  <Eq>
    <FieldRef Name='Color' />
    <Value Type='Text'>Blue</Value>
  </Eq>
</Where>";
results = list.GetItems(query);
Blue Square
4. Over manual query with Red Only view
SPQuery query = new SPQuery();
query.Query = @"
<Where>
  <Eq>
    <FieldRef Name='Color' />
    <Value Type='Text'>Blue</Value>
  </Eq>
</Where>";
results = list.GetItems(query, view.ID.ToString("B").ToUpper());
Red Square
Red Circle

In case 3, the view had no effect at all. The same was true for SPQuery.Query in case 4. So it appears that the CAML query which took effect last was the one actually used.

So you cannot perform a CAML search on a view. For example, if you want to search for all Red Square objects, specifying a CAML query for Square objects in conjunction with the Red Only view will not work. An alternative is to incorporate the view’s query and the desired query into a single query; i.e., you can search for all Red objects and all Square objects:

SPQuery query = new SPQuery();
query.Query = @"
<Where>
  <And>
    <Eq>
      <FieldRef Name='Color' />
      <Value Type='Text'>Red</Value>
    </Eq>
    <Eq>
      <FieldRef Name='Shape' />
      <Value Type='Text'>Square</Value>
    </Eq>
  </And>
</Where>";
results = list.GetItems(query);

This would return a Red Square, which is the desired result.

Above we only deal with the CAML query of the search. The same consideration applies if we also manually specify other attributes such as view fields and row limit which clash with those of the view.

Related posts: