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>
