Dynamic Caml expression - Contains clause

Jan 15, 2013 at 1:13 PM
Edited Jan 15, 2013 at 6:27 PM

Hi

First of all thanks for building such a great tool and making life easy. I need some help in building a dynamic Contains query.

I'm building a dynamic caml expression for searching a list on multiple columns. User would type a text and select one or more columns from the list. Columns can be of type text, note, Choice, lookup, User etc. Entered text should be searched on all these columns.

I see that there's no way to specify column type while building a Contains expression.

for e.g. the below code treats all columns to be of type text

 

List<Expression<Func<SPListItem, bool>>> searchConditions = null;
if (!string.IsNullOrEmpty(searchText) && searchFields != null)
{
    searchConditions = new List<Expression<Func<SPListItem, bool>>>();
    foreach (SearchField searchField in searchFields)
    {
         string fieldName = searchField.FieldName;
         searchConditions.Add(x => ((string)x[fieldName]).Contains(searchText));
    }
}

Suppose I am searching on 3 columns - Title(Text), Brand(Lookup) and Created By(User), using a Or query I'll get this caml query generated

<Where>
    <Or>
       <Contains>
          <FieldRef Name="Title" />
          <Value Type="Text">Some text</Value>
       </Contains>
       <Or>
          <Contains>
             <FieldRef Name="Brand" />
             <Value Type="Text">Some text</Value>
          </Contains>
          <Contains>
             <FieldRef Name="Author" />
             <Value Type="Text">Some text</Value>
          </Contains>
      </Or>
    </Or>
</Where>

In the query above, everywhere column type is "Text". Is it possible to get the actual types as below? Is there any downside if I go with the above?

<Where>
    <Or>
       <Contains>
          <FieldRef Name="Title" />
          <Value Type="Text">Some text</Value>
       </Contains>
       <Or>
          <Contains>
             <FieldRef Name="Brand" />
             <Value Type="Choice">Some text</Value>
          </Contains>
          <Contains>
             <FieldRef Name="Author" />
             <Value Type="User">Some text</Value>
          </Contains>
      </Or>
    </Or>
</Where>

And finally

a. Is there a way to append where clause to dynamically generated where query?

b. I'm using Camlex 3.2. Does it support "In" clause?

Any help is much appreciated.

Regards

Rajesh

 

 

 

 

 

Coordinator
Jan 15, 2013 at 7:37 PM
Edited Jan 15, 2013 at 7:38 PM

hi Rajesh,

example with Contains can't be done exactly like you want. There are several reasons. First reason comes from C#: you can't cast object to type like this:

Type t = typeof(Foo);
object o = ...;
((t)o).MethodFromFooClass();

But this problem can be avoided: you need to separate list of expressions for each type like this:

public struct SearchField<T> where T : StringBasedFieldType
{
    public string FieldName { get; set; }
}

...

string searchText = "test";
var searchFields1 = new List<SearchField<DataTypes.Text>>
    {
        new SearchField<DataTypes.Text>{FieldName = "Title1"},
        new SearchField<DataTypes.Text>{FieldName = "Title2"},
    };
var searchFields2 = new List<SearchField<DataTypes.Note>>
    {
        new SearchField<DataTypes.Note>{FieldName = "Note1"},
        new SearchField<DataTypes.Note>{FieldName = "Note2"},
    };
var searchConditions = getExpressions(searchFields1, searchText);
searchConditions.AddRange(getExpressions(searchFields2, searchText));
Console.WriteLine(Camlex.Query().WhereAny(searchConditions).ToString());

...

public static List<Expression<Func<SPListItem, bool>>> getExpressions<T>(List<SearchField<T>> searchFields, string searchText)
    where T : StringBasedFieldType
{
    var result = new List<Expression<Func<SPListItem, bool>>>();
    foreach (var searchField in searchFields)
    {
        string fieldName = searchField.FieldName;
        result.Add(x => ((T)x[fieldName]).Contains(searchText));
    }
    return result;
}

As you can see in this example we have 2 lists with search fields: one for DataTypes.Text and another for DataTypes.Note. Then we create search conditions from each list and merge results. It produces the following Caml:

<Where>
  <Or>
    <Or>
      <Or>
        <Contains>
          <FieldRef Name="Title1" />
          <Value Type="Text">test</Value>
        </Contains>
        <Contains>
          <FieldRef Name="Title2" />
          <Value Type="Text">test</Value>
        </Contains>
      </Or>
      <Contains>
        <FieldRef Name="Note1" />
        <Value Type="Note">test</Value>
      </Contains>
    </Or>
    <Contains>
      <FieldRef Name="Note2" />
      <Value Type="Note">test</Value>
    </Contains>
  </Or>
</Where>

But then we have another problem: currently in Camlex method Contains is defined only for types which inherit DataTypes.StringBasedFieldType. There are only 2 such types: DataTypes.Text and DataTypes.Note. And it is quite logical, because Contains and BeginWith methods are used with strings. I don't think we want to change it. However you may get source code and change it by yourself: inherit DataTypes.Choice and DataTypes.User from DataTypes.StringBasedFieldType and then use my example.

Another way is to convert Caml query to xml using ToCaml() method and change type attribute using linq2xml.

About another your questions:

a. If I understood your question correctly yes it is possible starting from 3.2 version where we implemented support of adding dynamic conditions to existing string queries. You can find examples here: http://sadomovalex.blogspot.fi/2012/06/camlexnet-32-add-expressions-to.html. However if you just have list of conditions and want to add more conditions to it you can do that by the same way as in the example above.

b. No, In clause is not supported yet (Camlex was created for Sharepoint 2007, while In clause was introduced in Sharepoint 2010. But we still want to have compatibility. For implementing it I most probably will create separate branch for Sharepoint 2007, while branch for Sharepoint 2010 will be used as base), but it will be next feature which I'm going to implement.

Jan 16, 2013 at 9:08 PM

Hi Sadomovalex

Thanks for the reply and your approach. I guess I'll go with the one supported by Camlex. I don't think there's any downside to using all types as string(first one in the example) as opposed to SharePoint types(second one in the example). 

Regards

Rajesh