• Download TK.SPTools.CAMLBuilder.Expressions.zip - 5.08 KB

• Download TK.SPTools.CAMLBuilder.Expressions_-_Source.zip - 11 KB

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:

• IsNull
• IsNotNull

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:

• And
• Or

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")
                          )
                        );

3. Adding sub-expressions one by one

You can also use constructs like the following:

	
 Expression exp = ..
 IEnumerable expressions = ...

 And and = new And();
 and.Add(exp);       //add one expression
 and.AddRange(expressions);  //add multiple expressions at a time

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
                     )
                 );


//can be rewritten as:
Expression exp = exp1 * (exp2 + exp3);


//or using negate on Eq
Criteria exp4 = Criteria.Eq("ContactName", "Text", "John");
   
Expression exp = exp1 * (exp2 + !exp4);

• ‘*’ stands for AND
• ‘+’ for OR
• ‘!’ for Negate

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");

Expression exp = .... //exp can either be Criteria or Operator 

exp.OrderBy("Status", true);

exp = exp.OrderBy("Status", true).OrderBy("Title", false);

Expression exp = .... //exp can either be Criteria or Operator

exp.GroupBy("Status");

exp = exp.GroupBy("Status", true).GroupBy("Title", false);

            
            Expression exp = ....

            exp.GetCAML();
            exp.GetCAMLQuery();

//generates compile time error
Criteria c = new ConditionCriteria("Status", "Choice", "Completed", CriteriaType.Eq); 

//correct
Criteria c = Criteria.Eq("Status", "Chocie", "Completed");