Click here to Skip to main content
15,867,453 members
Articles / Programming Languages / C#

CAMLBuilder Expressions

Rate me:
Please Sign up or sign in to vote.
4.70/5 (13 votes)
25 Nov 2009CPOL7 min read 65.9K   1.7K   26   21
Building CAML queries programmatically using Expressions

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.



cd.JPG

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.

CD2.JPG


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

C#
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

C#
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.

C#
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.

C#
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:

C#
Expression exp = ..
IEnumerable<expression /> expressions = ...

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

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.

C#
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


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.
 
C#
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:
 
C#
Expression exp = .... //exp can either be Criteria or Operator 

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.
 
C#
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:
 
C#
Expression exp = .... //exp can either be Criteria or Operator

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.
 
C#
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.

C#
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.

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

//correct
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
Ireland Ireland
.NET Developer.

Comments and Discussions

 
QuestionIssue including in project Pin
Member 1136019324-Apr-17 1:07
Member 1136019324-Apr-17 1:07 
QuestionVery very useful and works as expected. Very impressed! Pin
Member 365544511-Sep-15 5:55
Member 365544511-Sep-15 5:55 
QuestionVery Nice !!! Pin
Ashutosh Phoujdar13-Jul-14 6:25
Ashutosh Phoujdar13-Jul-14 6:25 
QuestionDateRangesOverlap Pin
thylocene22-Aug-13 13:33
thylocene22-Aug-13 13:33 
GeneralVery Usefull Pin
rodrigo diniz30-Apr-13 7:45
rodrigo diniz30-Apr-13 7:45 
GeneralRe: Very Usefull Pin
Tamas Koszoru30-Apr-13 8:31
Tamas Koszoru30-Apr-13 8:31 
QuestionWhen expression contains parenthesis Pin
sohelm11-Dec-12 11:40
sohelm11-Dec-12 11:40 
GeneralEasy to implement and use. Pin
MarkHazleton21-Nov-12 7:49
professionalMarkHazleton21-Nov-12 7:49 
QuestionIt hard to make it Simple Pin
g4fun10-Nov-12 5:01
g4fun10-Nov-12 5:01 
Questionreally useful thanks! Pin
Member 837952513-Oct-12 0:21
Member 837952513-Oct-12 0:21 
QuestionNice Code Pin
Eric Lebetsamer16-Aug-12 17:34
Eric Lebetsamer16-Aug-12 17:34 
GeneralGreat tool! Pin
Oslec27-Apr-11 21:32
Oslec27-Apr-11 21:32 
Thanks!
GeneralRe: Great tool! Pin
Tamas Koszoru27-Apr-11 23:04
Tamas Koszoru27-Apr-11 23:04 
GeneralThanks Pin
User 31295918-Feb-10 8:02
User 31295918-Feb-10 8:02 
GeneralRe: Thanks Pin
Tamas Koszoru19-Feb-10 2:35
Tamas Koszoru19-Feb-10 2:35 
GeneralThanks alot! Pin
X_N00B_X9-Jan-10 10:26
X_N00B_X9-Jan-10 10:26 
GeneralRe: Thanks alot! Pin
Tamas Koszoru10-Jan-10 23:32
Tamas Koszoru10-Jan-10 23:32 
GeneralAwesome Pin
tprick19-Nov-09 23:20
tprick19-Nov-09 23:20 
GeneralRe: Awesome Pin
Tamas Koszoru20-Nov-09 1:56
Tamas Koszoru20-Nov-09 1:56 
GeneralLooks interesting. Pin
Marc Leger15-Nov-09 13:06
Marc Leger15-Nov-09 13:06 
GeneralRe: Looks interesting. Pin
Tamas Koszoru16-Nov-09 1:42
Tamas Koszoru16-Nov-09 1:42 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.