CAML "In" Operator Equivalent For SharePoint Services 3 (SharePoint 2007)

Question

Today I came across the need to write a CAML query that uses WHERE [Field] IN [Values]. I wanted to query a List where the list item's Title was contained by a collection of strings. After receiving several errors running my query, I realized the In operator was new to SharePoint 2010 but I'm still in SharePoint 2007. As a result, the only option is to use multiple Or operators. Additionally an Or operator can only operate on 2 values at a time so this requires nested Or operators. How can you build such a query? See my solution below.

Kathikathiawar

Answer 1

I stumbled around a couple approaches before coming to this solution. I'm not sure of its scalability but it suits my needs so I thought I would share it. This recursive method should return the equivalent of

WHERE Title IN ([Title1], [Title2],...[TitleN]) 

for a List of 1-N string titles.

private string _camlTitleEq = "<Eq>" +
                                 "<FieldRef Name=\"Title\" />" +
                                 "<Value Type=\"Text\">{0}</Value>" +
                              "</Eq>";

private XElement BuildOrClause(List<string> listItemTitles, int index)
{
    //If we've reached the last item in the list, return only an Eq clause
    if (index == listItemTitles.Count - 1)
        return XElement.Parse(String.Format(_camlTitleEq, listItemTitles[index]));
    else
    {
        //If there are more items in the list, create a new nested Or, where
        //the first value is an Eq clause and the second is the result of BuildOrClause
        XElement orClause = new XElement("Or");
        orClause.Add(XElement.Parse(String.Format(_camlTitleEq, listItemTitles[index])));
        orClause.Add(BuildOrClause(listItemTitles, index + 1));
        return orClause;
    }
}

And you could use it like so:

SPQuery query = new SPQuery();
string titleIn = BuildOrClause(listItemTitles, 0).ToString(SaveOptions.DisableFormatting);

query.Query = "<Where>" +
                  titleIn +
              "</Where>";

I hope this helps someone still working in SP 2007. Constructive feedback is welcome! If you're in SharePoint 2010, use the already built in In Operator.

Kathikathiawar

Damn it. Missed this after spending two hours writing mine. – Viminal

Should I update the title or tags to make it easier to find? – Kathikathiawar