Sunday, March 27, 2011

How to build a dynamic query for SPQuery with multiple OR conditions programatically?

Scenario:
How to build a dynamic query for SPQuery object with multiple OR conditions programatically?

Explanation:
In this post, I will try to explain how to build a query for SPQuery object with multiple OR conditions dynamically.

For example: If a programmer attempts to get list items from a list with IDs 1,2,5, 9 and 13 dynamically, he would need to build a query for SPQuery with multiple OR conditions.

The below query seems to work at first glance but it does not work:
<Query>
  <Where>
    <Or>
      <Eq>
        <FieldRef Name="ID" />
        <Value Type="Counter">1</Value>
      </Eq>
      <Eq>
        <FieldRef Name="ID" />
        <Value Type="Counter">2</Value>
      </Eq>
      <Eq>
        <FieldRef Name="ID" />
        <Value Type="Counter">5</Value>
      </Eq>
      <Eq>
        <FieldRef Name="ID" />
        <Value Type="Counter">9</Value>
      </Eq>
      <Eq>
        <FieldRef Name="ID" />
        <Value Type="Counter">13</Value>
      </Eq>
    </Or>
  </Where>
</Query>

The reason why the above query does not work is because there is a hard limit set by SharePoint's SPQuery object to 2 items within an OR condition. The above code would only work for maximum of 2 items:
<Query>
  <Where>
    <Or>
      <Eq>
        <FieldRef Name="ID" />
        <Value Type="Counter">1</Value>
      </Eq>
      <Eq>
        <FieldRef Name="ID" />
        <Value Type="Counter">2</Value>
      </Eq>
    </Or>
  </Where>
</Query>

The correct query for SPQuery should look like the below:
<Query>
  <Where>
    <Or>
      <Eq>
        <FieldRef Name="ID" />
        <Value Type="Counter">1</Value>
      </Eq>
      <Or>
        <Eq>
          <FieldRef Name="ID" />
          <Value Type="Counter">2</Value>
        </Eq>
        <Or>
          <Eq>
            <FieldRef Name="ID" />
            <Value Type="Counter">5</Value>
          </Eq>
          <Or>
            <Eq>
              <FieldRef Name="ID" />
              <Value Type="Counter">9</Value>
            </Eq>
            <Eq>
              <FieldRef Name="ID" />
              <Value Type="Counter">13</Value>
            </Eq>
          </Or>
        </Or>
      </Or>
    </Or>
  </Where>
</Query>

Solution:
The below code could be used to build a dynamic query for SPQuery object in SharePoint that works with multiple OR conditions:

First we need a data structure that holds all the list item ids.
For this demo, I will create a custom object called "CustomSPItem" and use a generic list collection of my custom objects as data source.

My custom class:
class CustomSPItem
{
    /// <summary>
    /// ID from the SP List
    /// </summary>
    public int Id
    {
        get;
        set;
    }

    //Other Properties here

    public CustomSPItem()
    {
    }

    public CustomSPItem(int id)
    {
        Id = id;
        //Other properties here..
    }
}

Now create a collection of CustomSPItem objects:
List lstCustomSPItems = new List();
lstCustomSPItem.Add(new CustomSPItem(1));
lstCustomSPItem.Add(new CustomSPItem(2));
lstCustomSPItem.Add(new CustomSPItem(5));
lstCustomSPItem.Add(new CustomSPItem(9));
lstCustomSPItem.Add(new CustomSPItem(13));

Time to build dynamic query for SPQuery.
String query = BuildDynamicSPQueryWithMultipleOrConditions(lstCustomSPItems);

Required methods:
#region Build Dynamic SP Query with Multiple Or Conditions
/// <summary>
/// Builds SPQuery with multiple Or conditions
/// TODO: Describe this in more detail
/// </summary>
/// <param name="lstCustomSPItems"></param>
/// <returns></returns>
public static String BuildDynamicSPQueryWithMultipleOrConditions(List<CustomSPItem> lstCustomSPItems)
{
    String query = String.Empty;

    try
    {
        XmlDocument xmlDoc = new XmlDocument();
        XmlElement nodeWhere;

        //Create root node SPListItems
        nodeWhere = xmlDoc.CreateElement("Where");
        xmlDoc.AppendChild(nodeWhere);

        XmlElement nodeOr = null;
        int locCtr = 0;

        if (lstCustomSPItems.Count == 1)
        {
            var customSPItem = lstCustomSPItems[0];

            XmlElement nodeEq = BuildEqNodeForSPQuery(ref xmlDoc, ref nodeWhere);

            BuildEqNodeInnerXmlForSPQuery(ref xmlDoc, ref nodeEq, customSPItem.Id.ToString());
        }
        else
        {
            foreach (var customSPItem in lstCustomSPItems)
            {
                //Increment counter. We will need it to find the last item
                locCtr++;

                if (locCtr == 1)
                {
                    nodeOr = BuildDynamicOrEqCombination(ref xmlDoc, ref nodeWhere, customSPItem.Id.ToString());
                }
                else if (locCtr == lstCustomSPItems.Count)
                {
                    //We will need to include the last 2 nodes in the Or node. Is this the last record?
                    UpdateOrNode(ref xmlDoc, ref nodeOr, customSPItem.Id.ToString());
                }
                else
                {
                    nodeOr = BuildDynamicOrEqCombination(ref xmlDoc, ref nodeOr, customSPItem.Id.ToString());
                }
            }
        }

        query = xmlDoc.InnerXml;
    }
    catch (Exception ex)
    { }

    return query;
}

/// <summary>
/// Update Or node with a new Eq node
/// </summary>
/// <param name="xmlDoc"></param>
/// <param name="nodeParent"></param>
/// <param name="id"></param>
private static void UpdateOrNode(ref XmlDocument xmlDoc, ref XmlElement nodeParent, String id)
{
    XmlElement nodeEq = BuildEqNodeForSPQuery(ref xmlDoc, ref nodeParent);
    nodeParent.AppendChild(nodeEq);

    BuildEqNodeInnerXmlForSPQuery(ref xmlDoc, ref nodeEq, id.ToString());
}

/// <summary>
/// Build Xml node with a combination of Or and Eq
/// </summary>
/// <param name="xmlDoc"></param>
/// <param name="nodeParent"></param>
/// <param name="id"></param>
/// <returns></returns>
private static XmlElement BuildDynamicOrEqCombination(ref XmlDocument xmlDoc, ref XmlElement nodeParent, String id)
{
    XmlElement nodeOr = BuildOrNodeForSPQuery(ref xmlDoc, ref nodeParent);

    XmlElement nodeEq = BuildEqNodeForSPQuery(ref xmlDoc, ref nodeOr);

    nodeOr.AppendChild(nodeEq);

    BuildEqNodeInnerXmlForSPQuery(ref xmlDoc, ref nodeEq, id.ToString());

    return nodeOr;
}

/// <summary>
/// Build Xml node for "Or"
/// </summary>
/// <param name="xmlDoc"></param>
/// <param name="nodeListItem"></param>
private static XmlElement BuildOrNodeForSPQuery(ref XmlDocument xmlDoc, ref XmlElement nodeParent)
{
    XmlElement nodeOr = null;
    try
    {
        nodeOr = xmlDoc.CreateElement("Or");
        nodeParent.AppendChild(nodeOr);
    }
    catch (Exception ex)
    { }
    return nodeOr;
}

/// <summary>
/// Build Xml node for "Eq"
/// </summary>
/// <param name="xmlDoc"></param>
/// <param name="nodeListItem"></param>
private static XmlElement BuildEqNodeForSPQuery(ref XmlDocument xmlDoc, ref XmlElement nodeParent)
{
    XmlElement nodeEq = null;
    try
    {
        nodeEq = xmlDoc.CreateElement("Eq");
        nodeParent.AppendChild(nodeEq);
    }
    catch (Exception ex)
    { }
    return nodeEq;
}

/// <summary>
/// Build Xml node for "Eq"
/// </summary>
/// <param name="xmlDoc"></param>
/// <param name="nodeParent"></param>
private static void BuildEqNodeInnerXmlForSPQuery(ref XmlDocument xmlDoc, ref XmlElement nodeParent, String id)
{
    try
    {
        XmlElement nodeFieldRef = xmlDoc.CreateElement("FieldRef");
        nodeFieldRef.SetAttribute("Name", "ID");

        XmlElement nodeValue = xmlDoc.CreateElement("Value");
        nodeValue.SetAttribute("Type", "Counter");
        nodeValue.InnerText = id;

        nodeParent.AppendChild(nodeFieldRef);
        nodeParent.AppendChild(nodeValue);
    }
    catch (Exception ex)
    {
    }
}
#endregion

The final query that is generated would look like the below:
<Query>
  <Where>
    <Or>
      <Eq>
        <FieldRef Name="ID" />
        <Value Type="Counter">1</Value>
      </Eq>
      <Or>
        <Eq>
          <FieldRef Name="ID" />
          <Value Type="Counter">2</Value>
        </Eq>
        <Or>
          <Eq>
            <FieldRef Name="ID" />
            <Value Type="Counter">5</Value>
          </Eq>
          <Or>
            <Eq>
              <FieldRef Name="ID" />
              <Value Type="Counter">9</Value>
            </Eq>
            <Eq>
              <FieldRef Name="ID" />
              <Value Type="Counter">13</Value>
            </Eq>
          </Or>
        </Or>
      </Or>
    </Or>
  </Where>
</Query>

2 comments:

  1. Hi,

    You approach of building this query works, but I think its a time consuming process. I guess using SPmetal And Linq to SharePoint should have made this a bit easy.
    It would have reduced the amount of code.

    ReplyDelete
  2. You can also use new features in CAML 2010 as well - http://rmanimaran.wordpress.com/2011/03/11/new-in-sharepoint-2010-caml-query/

    ReplyDelete