Building CAML queries using CAMLBuilder.Expressions
This article demonstrates a solution that can be used to construct complex Sharepoint CAML queries programmatically in a very easy way.
Class diagram of solution
Background
If you have ever had to work with Sharepoint and you also had to use CAML queries, you may have already found that sometimes how painful it is to assemble queries especially from code.
Parameterized fixed queries
If you don’t want to end up concatenating several pieces of strings you can use the popular tool U2U Caml Query Builder (http://www.u2u.net) to first design your query and then you have to just parameterize it, so you can easily use it with any sets of parameters.
Arbitrary queries
However, the previous approach unfortunately works well only with fixed queries.
If the query is dynamically built, which means that the necessary criteria set that build up the query can only be determined runtime, you can’t have a fixed parameterized query because in this case the query itself changes. You can still go with concatenating query fragments (criterias) at runtime, based on whatever conditions you have but this was something we wanted to avoid.
Additionally, the CAML language itself makes building queries even more difficult, as for example an operator can have only two operands, so you need to build the query different ways depending on how many criteria you have.
Imagine a situation where you want to display a filtered Sharepoint list, where the filter conditions are defined by the user. Sometimes you may need just one, sometimes more, or none filters at all.
- If you have one filter you don’t need the <And> operator just one criteria in the query.
- If you have two filters you need the <And> operator and the two criterias under that.
- If you have more than two filters, you need the <And> operator and under that the criterias, but as the operators can only take two operands, you can’t just add all your necessary criterias under the <And>, instead you need to add an inner <And>, which brings you to build a recursion.
See examples below.
Introducing CAMLBuilder Expressions
CAML Builder Expressions provides you a simple solution for the previously described problems above. You can use the Expressions API to build arbitrary expression trees and then simply just get the result CAML query generated.
There are two types of expressions: (see: Class Diagram)
- Criteria
- Operator
Criteria
The Criteria defines a simple condition on a field. It can be either a Simple or a Condition Criteria.
A Simple gets only the FieldName whereas the Condition gets FieldName, FieldType and Value to compare the field value with.
Supported Criteria types:
Simple:
Condition:
- Eq (equals)
- Neq (not equals)
- Gt (greater than)
- Geq (greater than or equals)
- Lt (less than)
- Leq (less than or equals)
- BeginsWith
- Contains
- DateRangesOverlap
Operator
The operator defines a complex expression made up by multiple Criterias or Operators. As the Operator can include mutilple Criterias and Operators as well, this way you can build complex expression trees.
There are two types of operators:
Building Expression Examples
Building Critera
1. Simple Critera
Criteria exp1 = Criteria.IsNull("Status");
Criteria exp2 = Criteria.IsNotNull("Status");
Note: The only SimpleCriteria are the IsNull and IsNotNull. All the rest are ConditionCriteria.
2. Condition Criteria
Criteria exp1 = Criteria.Eq("Status", "Choice", "Completed");
Criteria exp2 = Criteria.Neq("Status", "Choice", "Completed");
Criteria exp3 = Criteria.Geq("Price", "Number", "3");
Where exp1 will generate:
<Eq>
<FieldRef Name='Status' />
<Value Type='Choice'>Completed</Value>
</Eq>
exp2 will generate:
<Neq>
<FieldRef Name='Status' />
<Value Type='Choice'>Completed</Value>
</Neq>
And exp3 will generate:
<Geq>
<FieldRef Name='Price' />
<Value Type='Number'>3</Value>
</Geq>
Build Complex Expressions Examples
1. Adding Criteria under an Operator.
Operator exp = Operator.And(
Criteria.Eq("Status", "Choice", "Completed"),
Criteria.Eq("Priority", "Choice", "High"),
Criteria.Neq("ContactName", "Text", "John")
);
This will build and expression to retrieve all the rows where Status is Completed and Priority is High but the ContactName is anything else than John.
As you can see, you can pass even more than two Criterias under one operator. The Expression engine will take care of generating the correct CAML (nesting the operators).
Note: the Operators must have at least two operands; otherwise exception is thrown during generation.
2. Nesting Operators
You can also add Operators under Operators to build complex expression trees.
Operator exp = Operator.And(
Criteria.Eq("Status", "Choice", "Completed"),
Operator.Or(
Criteria.Eq("Priority", "Choice", "High"),
Criteria.Neq("ContactName", "Text", "John")
)
);
This will build and expression to retrieve all the rows where Status is Completed and either the Priority is High or the ContactName is not John.
3. Adding sub-expressions one by one
You can also use constructs like the following:
Expression exp = ..
IEnumerable<expression /> expressions = ...
And and = new And();
and.Add(exp);
and.AddRange(expressions);
4. Using C# operators to build Expressions If you have your Criterias or complex expressions set up, you can also build complex expressions of them using the standars C# operators.
Criteria exp1 = Criteria.Eq("Status", "Choice", "Completed");
Criteria exp2 = Criteria.Eq("Priority", "Choice", "High");
Criteria exp3 = Criteria.Neq("ContactName", "Text", "John");
Expression exp = Operator.And(
exp1,
Operator.Or(
exp2,
exp3
)
);
Expression exp = exp1 * (exp2 + exp3);
Criteria exp4 = Criteria.Eq("ContactName", "Text", "John");
Expression exp = exp1 * (exp2 + !exp4);
- ‘*’ stands for AND
- ‘+’ for OR
- ‘!’ for Negate
Note: The ‘*’ and the ‘+’ are defined on the Expression level so they can be used with Criteria and Operator as well, while the ‘!’ is on the Criteria level, so it can only be applied on Criteria and only on type of Eq, Neq, IsNull and IsNotNull!5. Adding Custom Attributes on Criteria Any custom attribute can also be specified on the <fieldref>and <value>elements. By default, only the Name attribute of FieldRef element, and Type attribute of Value element are rendered. Sharepoint has many other attributes that these elements can accept. By using the AddXXXAttribute methods, you can pass any custom attributes.
exp1 = Criteria.Eq("Status", "Number", "2").AddFieldRefAttribute("Explicit", "True");
exp2 = Criteria.Eq("Contact", "Lookup", "2").AddFieldRefAttribute("LookupId", "True");
exp3 = Criteria.Eq("Date", "DateTime", "zz").AddValueAttribute("IncludeTimeValue", "False");
Just like the groupby, orderby, these can also be cascaded.
Note: The AddFieldRefAttribute can be used on both types of criteria, whereas the AddValueAttribute can only be called on a ConditionCriteria (as SimpleCriteria does not have Value element). Output of exp2:
<Eq>
<FieldRef Name='Contact' LookupId='True' />
<Value Type='Lookup'>2</Value>
</Eq>
6. Sorting Expressions also support CAML sorting facility. When applied, the standard CAML <orderby>element is added to the result query. This was not used in the previous examples. This will apply a sort on Status field ascending:
Expression exp = ....
exp.OrderBy("Status", true);
You can also add multiple OrderBy on an expression, as the OrderBy() always returns the expression itself so OrderBy() calls can be chained and will be rendered in this order.
exp = exp.OrderBy("Status", true).OrderBy("Title", false);
Note: OrderBy can be applied on different expressions in the expression tree, but always the ones of the expression that GetCAMLQuery() is called on (root) will be added.
7. Grouping Expressions also support CAML grouping facility. When applied, the standard CAML <groupby>element is added to the result query. This will apply a sort on Status field ascending:
Expression exp = ....
exp.GroupBy("Status");
You can also add multiple GroupBy on an expression, as the GroupBy() always returns the expression itself so GroupBy() calls can be chained and will be rendered in this order.
exp = exp.GroupBy("Status", true).GroupBy("Title", false);
Note: GroupBy can be applied on different expressions in the expression tree, but always the ones of the expression that GetCAMLQuery() is called on (root) will be added.
Generating the result CAML query
The CAML query can be generated by simply calling either the GetCAML() or the GetCAMLQuery() method on the Expression. As it is defined on the Expression, you can call it on either the Criteria, or on the Operator as well.
Expression exp = ....
exp.GetCAML();
exp.GetCAMLQuery();
GetCAML() returns just the inner part of the <Where> element. The Where fragment without enclosing <Where></Where>.
GetCAMLQuery() returns a properly built CAML query with <Query>, <Where> elements and also includes <OrderBy> and <GroupBy> if any applied.
<Query>
<Where>CAML</Where>
<OrderBy>ORDERBY</OrderBy>
<GroupBy>GROUPBY</GroupBy>
</Query>
Additional notes
1. The constructors on the Simple and Condition Criteria classes are set to be internal, means that you can only instantiate them by using the static builders defined in the base Criteria class. This ensures that the correct renderer is used for all criteria types.
Criteria c = new ConditionCriteria("Status", "Choice", "Completed", CriteriaType.Eq);
Criteria c = Criteria.Eq("Status", "Chocie", "Completed");
2. The Criteria classes are almost immutable as the FieldName, FieldType, CriteriaType properties are just getters, it is only the Value property that can be set, hence the expressions are also parameterized. Once built, it can be used with different sets of parameters.
3. The Value is always taken as a string and will go into the result CAML as is, so the engine does not do any type conversion or modification to the string, those need to be done outside in the user code.
4. Also note that the FieldType is taken as a string, so user has to ensure that a valid FieldType is passed.
5. Tested with Sharepoint 2007. CAML may change in forthcoming releases.
Conclusion
The shown solution is very lightweight containing only few classes, but powerful enough to save a lot of time when working with CAML queries.
