Support for IN CAML operation in Camlex

Introduction

Camlex.Net is free open source project for Sharepoint developers which simplifies creation of CAML queries (project site is here). You may read my previous articles about it on Codeproject here. In this article I will describe new feature added with version 3.5 (in client object model version - with 1.3): support for IN operation.

MSDN article says the following about IN operation:

"Specifies whether the value of a list item for the field specified by the FieldRef element is equal to one of the values specified by the Values element " 

E.g. if we need to get all list items which have integer Count field in range 0..9 we can use the following query:

<Where>
  <In>
    <FieldRef Name="Count" />
    <Values>
      <Value Type="Integer">0</Value>
      <Value Type="Integer">1</Value>
      <Value Type="Integer">2</Value>
      <Value Type="Integer">3</Value>
      <Value Type="Integer">4</Value>
      <Value Type="Integer">5</Value>
      <Value Type="Integer">6</Value>
      <Value Type="Integer">7</Value>
      <Value Type="Integer">8</Value>
      <Value Type="Integer">9</Value>
    </Values>
  </In>
</Where>

It was possible to combine multiple Eq operations using Or and get the same result. And with Camlex it was quite easy to do it using single line of code (for better reading I put it to 3 lines):

 string caml = Camlex.Query().WhereAny(
    Enumerable.Range(0, 9).Select<int, Expression<Func<SPListItem, bool>>>(
        i => x => (int) x["Count"] == i)).ToString();

But let’s see the resulting CAML of the above code:

<Where>
  <Or>
    <Or>
      <Or>
        <Or>
          <Or>
            <Or>
              <Or>
                <Or>
                  <Eq>
                    <FieldRef Name="Count" />
                    <Value Type="Integer">0</Value>
                  </Eq>
                  <Eq>
                    <FieldRef Name="Count" />
                    <Value Type="Integer">1</Value>
                  </Eq>
                </Or>
                <Eq>
                  <FieldRef Name="Count" />
                  <Value Type="Integer">2</Value>
                </Eq>
              </Or>
              <Eq>
                <FieldRef Name="Count" />
                <Value Type="Integer">3</Value>
              </Eq>
            </Or>
            <Eq>
              <FieldRef Name="Count" />
              <Value Type="Integer">4</Value>
            </Eq>
          </Or>
          <Eq>
            <FieldRef Name="Count" />
            <Value Type="Integer">5</Value>
          </Eq>
        </Or>
        <Eq>
          <FieldRef Name="Count" />
          <Value Type="Integer">6</Value>
        </Eq>
      </Or>
      <Eq>
        <FieldRef Name="Count" />
        <Value Type="Integer">7</Value>
      </Eq>
    </Or>
    <Eq>
      <FieldRef Name="Count" />
      <Value Type="Integer">8</Value>
    </Eq>
  </Or>
</Where>

Comparing with 1st example, it looks not very nice. If it doesn’t matter for you, you still can use OR syntax. However now it is possible to write simpler expression which will produce IN syntax:

string caml = Camlex.Query().Where(x => Enumerable.Range(0, 9).Contains((int)x["Count"])).ToString();

It will produce CAML which we already saw:

<Where>
  <In>
    <FieldRef Name="Count" />
    <Values>
      <Value Type="Integer">0</Value>
      <Value Type="Integer">1</Value>
      <Value Type="Integer">2</Value>
      <Value Type="Integer">3</Value>
      <Value Type="Integer">4</Value>
      <Value Type="Integer">5</Value>
      <Value Type="Integer">6</Value>
      <Value Type="Integer">7</Value>
      <Value Type="Integer">8</Value>
    </Values>
  </In>
</Where>

For those who worked with NHibernate or Linq2Sql this syntax won’t be new: these ORM frameworks use the same syntax for generating of SQL queries with IN operator. I intentionally showed example with dynamically populated array (Enumerable.Range(0, 9)): code supports any expression which produces IEnumerable:

var caml = Camlex.Query().Where(x => getArray().Contains((int)x["Count"])).ToString();
...
List<int> getArray()
{
    var list = new List<int>();
    for (int i = 0; i < 10; i++)
    {
        list.Add(i);
    }
    return list;
}

(Do you see the difference between the last example which function which returns List<int>, and previous example, which uses IEnumerable? Class List<T> has own Contains method, while for IEnumerable<int> from first example Linq Contains extension method was used. As you can see Camlex may work with both of them. Note that if you use Linq you should add “using System.Linq;” to your file).

I.e. basic syntax is the following:

var caml = Camlex.Query().Where(x => enumerable.Contains((Type)x["FieldTitle"])).ToString();

Of course you may write array with constants:

string c = Camlex.Query().Where(x => new[] {0, 1, 2, 3, 4, 5, 6, 7, 8, 9}
    .Contains((int) x["Count"])).ToString();

Also it will work with any type supported for Value element:

string c = Camlex.Query().Where(x => new[]
    {"zero", "one", "two", "three", "four", "five", "six", "seven", "eight", "nine"}
        .Contains((string) x["Title"])).ToString();

It will produce the following result:

<Where>
  <In>
    <FieldRef Name="Title" />
    <Values>
      <Value Type="Text">zero</Value>
      <Value Type="Text">one</Value>
      <Value Type="Text">two</Value>
      <Value Type="Text">three</Value>
      <Value Type="Text">four</Value>
      <Value Type="Text">five</Value>
      <Value Type="Text">six</Value>
      <Value Type="Text">seven</Value>
      <Value Type="Text">eight</Value>
      <Value Type="Text">nine</Value>
    </Values>
  </In>
</Where>

Now it uses Text type in Value elements.

That was only half of the story. If you follow the Camlex, you probably know that starting with version 3.0 it became bidirectional (I wrote about it here: Camlex.NET became bidirectional and goes online. Version 3.0 is released). So all conversions are added with 2 directions: from expression to CAML and from CAML to expression:

var xml =
    "<Query>" +
    "  <Where>" +
    "    <In>" +
    "      <FieldRef Name=\"Title\" />" +
    "      <Values>" +
    "        <Value Type=\"Text\">zero</Value>" +
    "        <Value Type=\"Text\">one</Value>" +
    "        <Value Type=\"Text\">two</Value>" +
    "        <Value Type=\"Text\">three</Value>" +
    "        <Value Type=\"Text\">four</Value>" +
    "        <Value Type=\"Text\">five</Value>" +
    "        <Value Type=\"Text\">six</Value>" +
    "        <Value Type=\"Text\">seven</Value>" +
    "        <Value Type=\"Text\">eight</Value>" +
    "        <Value Type=\"Text\">nine</Value>" +
    "      </Values>" +
    "    </In>" +
    "  </Where>" +
    "</Query>";
 
var expr = Camlex.QueryFromString(xml).ToExpression();

Applications of that feature:

1. on http://camlex-online.org/ add xml shown below to the textarea and click Convert to C#. It will show you how this query will be produced by Camlex:

Camlex.Query().Where(x => new[] {
    "zero", "one", "two", "three", "four", "five", "six", "seven", "eight", "nine" }
        .Contains((string)x["Title"]))

2. it is possible to add more conditions to the existing IN query which is stored as xml, or add IN query to existing query:

var xml =
    "<Query>" +
    "  <Where>" +
    "    <In>" +
    "      <FieldRef Name=\"Title\" />" +
    "      <Values>" +
    "        <Value Type=\"Text\">one</Value>" +
    "        <Value Type=\"Text\">two</Value>" +
    "      </Values>" +
    "    </In>" +
    "  </Where>" +
    "</Query>";
 
string caml = Camlex.Query().WhereAll(xml, x => (int)x["Count"] == 1).ToString();

will produce:

<Where>
  <And>
    <Eq>
      <FieldRef Name="Count" />
      <Value Type="Integer">1</Value>
    </Eq>
    <In>
      <FieldRef Name="Title" />
      <Values>
        <Value Type="Text">one</Value>
        <Value Type="Text">two</Value>
      </Values>
    </In>
  </And>
</Where>

I.e. Camlex may combine several string queries using Or and And, or even mix string query with expression as shown above. This feature was added in previous releases.