Wednesday, February 3, 2010

Build dynamic expressions for CAML queries based on list of values

Recently we had an interesting feedback related with Camlex.NET project – see http://habrahabr.ru/blogs/sharepoint/82787 (on Russian language). Community member with nick Olegas asked:

“I had an array with integer values. I need to create query which will return all items which have ID which presents in this array of integers. I.e. I need to join several <FieldRef> with different <Value> via Or operation”.

It is interesting feature and we didn’t address it in the 1st release of Camlex. Nevertheless there is a workaround for this problem which requires additional coding and is not very convenient. But it works.

The general idea is to build expression dynamically and pass it into Camlex. So we have an array of integers:

   1: var ids = new[] { 1, 2, 3 };

At first we need to create single expression which will be translated into <Eq> operation in CAML for each id in list, i.e. we need the following CAML for each element of array:

   1: <Eq>
   2:   <FieldRef Name="ID" />
   3:   <Value Type="Integer">1</Value>
   4: </Eq>

The following code shows how it can be done:

   1: var exprs = new List<Expression>();
   2: ids.ToList().ForEach(i =>
   3:                          {
   4: var expr =
   5:     Expression.Equal(
   6:         Expression.Convert(
   7:             Expression.Call(
   8:                 Expression.Parameter(typeof (SPItem), "x"),
   9:                 typeof (SPItem).GetMethod("get_Item", new[] {typeof (string)}),
  10:                 new[] {Expression.Constant("ID")}),
  11:             typeof (int)),
  12:         Expression.Constant(i));
  13: exprs.Add(expr);
  14: });

Here we construct expression “(int)x[“ID”] == i” for each i in array of integers. Now we need to combine the list of these expressions using || (OrElse) operation:

   1: var result = generateResultOrElseExpression(1, exprs, exprs[0]);
   2: var lambda = Expression.Lambda<Func<SPItem, bool>>(result, Expression.Parameter(typeof(SPItem), "x"));
   3: var caml = Camlex.Query().Where(lambda).ToString();

Function generateResultOrElseExpression() has the following code:

   1: static BinaryExpression generateResultOrElseExpression(
   2: int currentExpressionToAdd, List<Expression> allExprs, Expression prevExpr)
   3: {
   4:     if (currentExpressionToAdd >= allExprs.Count)
   5:     {
   6:         return (BinaryExpression)prevExpr;
   7:     }
   8:     var resultExpr =
   9:         Expression.OrElse(prevExpr, allExprs[currentExpressionToAdd]);
  10:     return generateResultOrElseExpression(currentExpressionToAdd + 1, allExprs, resultExpr);
  11: }

I.e. it accumulates expressions into prevExpr parameter and recursively calls itself. After that you will have the following CAML query:

   1: <Where>
   2:   <Or>
   3:     <Or>
   4:       <Eq>
   5:         <FieldRef Name="ID" />
   6:         <Value Type="Integer">1</Value>
   7:       </Eq>
   8:       <Eq>
   9:         <FieldRef Name="ID" />
  10:         <Value Type="Integer">2</Value>
  11:       </Eq>
  12:     </Or>
  13:     <Eq>
  14:       <FieldRef Name="ID" />
  15:       <Value Type="Integer">3</Value>
  16:     </Eq>
  17:   </Or>
  18: </Where>

As you see it is not so simple to achieve the result. We will try to help developers to solve such problems with less efforts in the next releases of Camlex. I’m not sure about exact view of solution, may be something like this:

   1: var caml =
   2:     Camlex.Query()
   3:         .Where(x => Camlex.ForEach(ids, i => (int)x["ID"] == i)
   4:             .JoinWithOr());

What do you think?

And thanks to Olegas for feedback.

2 comments:

  1. when i trying this code my result is (Convert(x.get_Item("ID")) == 1) insted of (int)x[“ID”] == 1 why this issue is oucerring???
    please tell

    ReplyDelete