New in SharePoint 2010 CAML Query <IN>,<INCLUDES> &< NOT INCLUDES>
Even though Microsoft encourages using LINQ in SharePoint 2010, they introduced some new CAML Query schema elements. They are
· NOT INCLUDES
This is something work as same as what we see in SQL Query. We can provide a collection of Items to filter. Previously in SharePoint 2007 we have to use OR operator to do this.. But using OR for multiple times for multiple values will lead to lack in code clarity. So IN operator is considering as good.
Here I have following SharePoint List.
In the above list if want to select the items having designation like Engineer and Architect I have to write like this in SharePoint 2007.
<Where> <Or> <Eq> <FieldRef Name='Designation' /> <Value Type='Text'>Engineer</Value> </Eq> <Eq> <FieldRef Name='Designation' /> <Value Type='Text'>Architect</Value> </Eq> </Or> </Where>
The above can be written in SharePoint 2010 as
<Where> <In> <FieldRef Name='Designation' /> <Values> <Value Type='Text'>Engineer</Value> <Value Type='Text'>Architect</Value> </Values> </In> </Where>
Both will yield the same output as shown below.
MSDN Def: If the specified field is a Lookup field that allows multiple values, specifies that the Value element is included in the list item for the field that is specified by the FieldRef element.
If you did not understand the above definition from MSDN, check the following example.
Here is a sample Employee list.
Here Specialization is a MultiSelect Lookup column.
I need to select People who are specialized in XML first and then specialized .Net also. This can be done using Includes.
SPQuery needs to framed like this
<Where> <And> <Eq> <FieldRef Name=""Specialization""/> <Value Type=""Lookup"">XML</Value> </Eq> <Includes> <FieldRef Name='Specialization'/> <Value Type='Lookup'>.Net</Value> </Includes> </And> </Where>
Then the specified output will be
It will do opposite to what Includes will do.
In the above list I like to have all the people who are specialized in SQL Server but not specialized in Crystal Report.
Here my SPQuery
<Where> <And> <Eq> <FieldRef Name=""Specialization""/> <Value Type=""Lookup"">SQL Server</Value> </Eq> <NotIncludes> <FieldRef Name='Specialization'/> <Value Type='Lookup'>Crystal Report</Value> </NotIncludes> </And> </Where>
Note: As you see in <IN> operation you have <Values> as the child. Here we can specify multiple items. In the MSDN they specify it is apply to <Includes> and <NotIncludes>. But it is not working. I don’t know. Why?