Caml Queries to Remember

 

Everytime I start writing CAML I fall back to U2U.

Wanted to keep few queries handy so here we go

No Filter

<View><ViewFields><FieldRef Name="Title" /><FieldRef Name="Column2" /></ViewFields></View>

With Row Limit

<View><RowLimit>10</RowLimit><ViewFields><FieldRef Name="Title" /><FieldRef Name="Column2" /></ViewFields></View>

Above two can  be used with CamlQuery object as view xml.

example

CamlQuery camlquery = new CamlQuery();
               camlquery.ViewXml = "<View><RowLimit>10</RowLimit><ViewFields><FieldRef Name='Title'/><FieldRef Name='FileRef'/><FieldRef Name='BasePath'/></ViewFields></View>";
           

   List sSCConfigList = clientContext.Web.Lists.GetByTitle("SSCConfig");
               clientContext.Load(sSCConfigList);
               clientContext.ExecuteQuery();

               var listItems = sSCConfigList.GetItems(camlquery);

Single Select

Example
<Query><Where><Eq><FieldRef Name="Title" /><Value Type="Text">ABC VALUE</Value></Eq></Where></Query>

Multiple Rows

Example
<Query><Where><Contains><FieldRef Name="Title" /><Value Type="Text">Some Text</Value></Contains></Where></Query>

In case of Rich Text field, you can use <![CDATA[]]> around the value to prevent parsing errors when passing HTML into the query. Or you can replace < with &lt; , > with &gt; and “ with &quot; and so on.

Other operators are <Geq/> for greater than and <Leq/> for less than

LookUps
Here we use LookupId=”true”.  By using this we can specify the id value in the value tag
Look up on Id

example

<Query><Where><Eq><FieldRef Name="SomeLookupcolumn" LookupId="TRUE" /><Value Type="Lookup">4</Value></Eq></Where></Query>

Here It will filter on ‘SomeLookupColumn’ column here based on look up id and not value.  This ensures unique value.

This can be used to get person or group also

example

Filter on Current User

Example
<Query><Where><Eq><FieldRef Name="Author" LookupId="TRUE" /><Value Type="Integer"><UserID /></Value></Eq></Where></Query>

Notice here Valuetype is integer.

Using <UserID /> as the value, the query will filter based on the current user. You can also pass the ID of a specific user in place of <UserID /> (e.g. <Value Type="Integer">283</Value>) if you don’t want to filter by the current user.

Lookup on text

Example
<Query><Where><Eq><FieldRef Name="SomeLookupColumn" /><Value Type="Lookup">GujaratState</Value></Eq></Where></Query>

This will look for items with “GujaratState” in the look up column field. If there are more than one items having same display name, it will return all those items.

Date & Time

Example
<Query><Where><Eq><FieldRef Name="Modified" /><Value Type="DateTime"><Today /></Value></Eq></Where></Query>

A date can also be used instead in Value tag.  We can also use something like this <Today OffsetDays="-4" />