SharePoint: Dynamically Building Complex CAML Queries


Building CAML queries dynamically can become a gnarly problem, especially when the definition of the query contains some non determinate elements, i.e. you know the combinatorial form of the expression(s) but the expression(s) conditions are 0..n.

Building an expression such as this becomes a tricky programming problem, given the way in which <And>, <Or> elements are combined according to the CAML query schema, Waldek Mastykarz has 1 solution to the problem, however that solution, or a derivative doesn’t quite address the problem.

Consider the screen below which shows 3 selection list boxes;

Filter Controls
Filter Controls

The psuedo query behind these controls is as follows;

DocumentType IN (‘Audit Record’,’Calculator’) AND FunctionalArea In (‘Brewing’,’Capital Projects’,’Engineering’,’Asset Care and Maintenance’) AND SubArea IN (‘Auditing’,’Barley Handling and Storage’)

And the resultant CAML query for this is;

<Where>
  <And>
    <And>
      <Or>
        <Eq>
          <FieldRef Name="ContentTypeId" />
          <Value Type="Text">Audit Record</Value>
        </Eq>
        <Eq>
          <FieldRef Name="ContentTypeId" />
          <Value Type="Text">Calculator</Value>
        </Eq>
      </Or>
      <Or>
        <Or>
          <Or>
            <Eq>
              <FieldRef Name="Column2" />
              <Value Type="Text">Brewing</Value>
            </Eq>
            <Eq>
              <FieldRef Name="Column2" />
              <Value Type="Text">Capital Projects</Value>
            </Eq>
          </Or>
          <Eq>
            <FieldRef Name="Column2" />
            <Value Type="Text">Engineering</Value>
          </Eq>
        </Or>
        <Eq>
          <FieldRef Name="Column2" />
          <Value Type="Text">Asset Care and Maintenance</Value>
        </Eq>
      </Or>
    </And>
    <Or>
      <Eq>
        <FieldRef Name="Column3" />
        <Value Type="Text">Auditing</Value>
      </Eq>
      <Eq>
        <FieldRef Name="Column3" />
        <Value Type="Text">Barley Handling and Storage</Value>
      </Eq>
    </Or>
  </And>
</Where>

No doubt with effort you could arrive at a programmatic solution to this, but doing this could well descend into a project in it’s own right. I sought an alternative solution and found Camlex.NET by Alexey Sadomov on CodePlex.

This is a library, which simply put, allows you to build complex CAML queries using LINQ notation and to render the resultant query as an XElement or string.

I won’t describe how to use the Camlex.NET  library, as his blog and the documentation on CodePlex more than adequately do that job (his blog and the codeplex documentation are chock full of samples), but, as a taster, here’s my code sample for creating the CAML query for the filter controls show above;

/* document type */
var doctypeConditions = _cblDocumentType.Items.Cast<ListItem>()
	.Where(l => l.Selected)
	.Select(item => item.Value)
		.Select(doctype => (Expression<Func<SPListItem, bool>>) (x => (string)x["ContentTypeId"]) == doctype ))
	.ToList()

/* functional area */
var funcareaConditions = _cblFunctionalArea.Items.Cast<ListItem>()
	.Where(l => l.Selected)
	.Select(item => item.Value)
		.Select(funcarea => (Expression<Func<SPListItem, bool>>)(x => (string)x["Column2"] == funcarea ))
	.ToList();

/* sub area */
var subareaConditions = _cblSubArea.Items.Cast<ListItem>()
	.Where(l => l.Selected)
	.Select(item => item.Value)
		.Select(subarea => (Expression<Func<SPListItem, bool>>)(x => (string)x["Column3"] == subarea ))
	.ToList();

var dtExpr = doctypeConditions.Count > 0
	? ExpressionsHelper.CombineOr(doctypeConditions)
	: null;
var faExpr = funcareaConditions.Count > 0
	? ExpressionsHelper.CombineOr(funcareaConditions)
	: null;
var saExpr = subareaConditions.Count > 0
	? ExpressionsHelper.CombineOr(subareaConditions)
	: null;

var expressions = new List<Expression<Func<SPListItem, bool>>>();
if (dtExpr != null)
	expressions.Add(dtExpr);
if (faExpr != null)
	expressions.Add(faExpr);
if (saExpr != null)
	expressions.Add(saExpr);

var query = expressions.Count > 0
	? Camlex.Query().WhereAll(expressions).ToCaml(false)
			.FirstOrDefault()
			.ToString(SaveOptions.DisableFormatting)
	: string.Empty;

Published by

Phil Harding

SharePoint Consultant, Developer, Father, Husband and Climber.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s