QueryBuilder

The QueryBuilder allows you to create CAML queries in the source code without having to write long string expressions. Additionally it helps preventing errors by typos or impossible queries (i.e. comparing a lookup value with a value that is not numeric).
QueryBuilder.Where(QueryBuilder.Eq("Title", "My ListItem"));

This will create an instance of SPQuery with the Query property set to
<Where>
  <Eq>
    <FieldRef Name="Title"/>
    <Value Type="Text">My ListItem</Value>
  </Eq>
</Where>

To get you writing these types of queries faster, add the following using line to your code file:
using QB = Sapiens.at.SharePoint.Utils.QueryBuilder;

Now you can write
QB.Where(QB.Eq("Title", "My ListItem"));

QueryBuilder provides extension methods that let you work with it more convenient. All following code examples make use of these extension methods.

Example

Here is a more complex example using the QueryBuilder to generate a CAML query that gets contact listitems

with a text field City that matches Vienna AND
with a date field NextCall that's lower than now AND
with a text field Comments that's not null OR
with a boolean field IgnoreComments that's true.
Additionally the result of the query will be ordered by NextCall and all items in the lists' subfolders are considered.
var query = new SPQuery()
   .Where(
      QB.And(     // The And/Or methods can take a variable amount of arguments and nest the queries accordingly.
         QB.Eq("City", "Vienna"),
         QB.Lt("NextCall", DateTime.Now, true),    // Comparing DateTime values can optionally use time component too.
         QB.Or(
            QB.IsNotNull("Comments"),
            QB.Eq("IgnoreComments", true)   // Translates typed values like Boolean, DateTime or Float to their CAML representations.
         )
      ))
   .OrderBy("NextCall")    // Chain methods to get a SQL/Linq like code.
   .Scope(SPViewScope.RecursiveAll);

Building Queries using the QueryBuilder

Starting point for allmost every query created with the QueryBuilder is the method Where. The following sections show you the different arguments you can use with this method.
This topic contains the following sections.
  • Operators
  • And & Or
  • Nesting expressions
  • Querying lookup fields
  • Querying with DateTime values

Operators

For every operator defined in CAML there is a corresponding method available in the QueryBuilder class. The following methods can be used: Eq<T>, Neq<T>, Gt<T>, Geq<T>, Lt<T>, Leq<T>, BeginsWith, Contains, IsNull, IsNotNull.
var query = new SPQuery().Where(QB.Eq("Title", "My ListItem")); // queries for listitems with the title 'My ListItem'
var query = new SPQuery().Where(QB.IsNotNull("Comments"));      // queries for listitems that have a not empty field comments

One additional method that has no corresponding CAML tag is IsContentType, which adds a condition to find items of a certain content type id.
var ctid = new SPContentTypeId("0x0102");
var query = new SPQuery().Where(QB.IsContentType(ctid));

Although the operators requiring a value argument are generic and require a type parameter, this type usually can be omitted because the compiler figures the type of the parameter given.

And & Or

To combine multiple operators with each other the QueryBuilder provides the methods And and Or. These methods take a variable amount of operators and generate the correct CAML.
var query = new SPQuery().Where(
   QB.And( // capsules multiple operators into an And/Or
      QB.Eq("IsImportant", true),
      QB.IsNull("Comments")
   )
);

Unlike the original And/Or of CAML you are not forced or limited to exactly two operators. If you provide more than two operators, QueryBuilder will create a nested CAML. Ands/Ors with only one operator create just the CAML of the operator and if there is not even one operator (QB.And()) an empty string comes back.
QB.Or(QB.Eq("...", ...), QB.Eq("...", ...), QB.IsNull("..."), QB.IsContentType(...)) // creates a nested CAML
QB.Or(QB.IsNotNull("...")) // creates only the CAML of the IsNotNull
QB.Or() // creates an empty string

Nesting expressions

The And and Or methods take either an operator (like Eq, …) or the result of another And or Or as argument. This allows nesting of queries.
var query = new SPQuery()
   .Where(
      QB.And(
         QB.Or(
            QB.Eq("...", ...),
            QB.Lt("...", ...)
         ),
         QB.Geq("...", ...)
      )
   );

Querying lookup fields

Some special QueryBuilder methods let you create CAML which queries lookup fields. As an argument the id of the lookup item is passed (if necessarry).
QB.EqLookup("Department", 105);

Every basic operator, that can be used with a lookup field, has a special lookup method:

Eq -> EqLookup
Neq -> NeqLookup
IsNull -> IsNullLookup
IsNotNull -> IsNotNullLookup

Querying with DateTime values

Operators that compare field values that are possibly DateTime values have a special method that takes a third argument IncludeTimeValue which allows comparing the field value against the time component too (Eq(String, DateTime, Boolean)).

QueryBuilder Extension Methods

The QueryBuilder class extends the SPQuery object to use its methods directly on the resulting object. This makes the code much more fluent and readable.
This topic contains the following sections.
  • Where
  • OrderBy & GroupBy
  • Select & ViewFields
  • Scope

Where

The most important extension method is Where, which allows you to create SPQuery objects like this:
var query = new SPQuery().Where(QB.Eq("...", "..."));

All the extension methods return the SPQuery object which allows chaining the methods in a way that looks like Linq or SQL
var query = new SPQuery()
   .Select("...", "...", ...)
   .Where(QB.Eq("...", "..."))
   .OrderBy("...");

If you have a SPQuery object with a query already set, you can add your CAML with the overloaded method Where. It takes the existing query and capsules it together with your new query in an And tag.

OrderBy & GroupBy

Just as using Where you can use the extension methods OrderBy and GroupBy with your SPQuery object. Both methods take a various amount of field names.
var query = new SPQuery()
   .Where(QB.EQ(...))
   .OrderBy("Title", "EventDate");

To order the results ascending you can use the method OrderBy which takes a collection of QueryBuilderOrderByOperator. This object can be instantiated in a way telling the OrderBy to sort its field ascending.

Select & ViewFields

In some situations you would need to specify the property SPQueryViewFields(). In that case you can either use the method Select or its alias ViewFields to do the task in a QueryBuilder like way.
Both methods do exactly the same. It’s on you, if you prefer a SQL like statement or the SPQuery way.

Scope

Similar to Select & ViewFields the method Scope helps when you want to set the scope of the query inside the SPQueryViewAttributes().
var query = new SPQuery()
   .Where(QB.Eq(...))
   .Scope(SPViewScope.RecursiveAll);

If there is already a scope defined, it will be replaced. Otherwise the string will be added to the ViewAttributes property.















Last edited Jul 8, 2011 at 9:01 AM by florianwachter, version 8

Comments

No comments yet.