Create complex dynamic CAML queries with Camlex.Net

In one of my previous posts I showed how to build dynamic CAML queries based on query string parameters with Camlex.Net – open source tool for creating CAML queries via C#. I used WhereAll() and WhereAny() methods of the IQuery interface which allows to combine several conditions using AND (&&) and OR (||) logical operations. But what if you need to build your query using more complex rules and it should contain both AND and OR operations? Of course you may combine expressions using different logical operations by yourself, but it will require more work from you. For such scenarios Camlex contains special class which you can use for building complex dynamic queries: ExpressionsHelper. It contains 2 useful methods CombineOr() and CombineAnd():

   1: public static class ExpressionsHelper

   2: {

   3:     public static Expression<Func<SPListItem, bool>> CombineAnd(

   4:         IEnumerable<Expression<Func<SPListItem, bool>>> expressions)

   5:     {

   6:         ...

   7:     }

   8:  

   9:     public static Expression<Func<SPListItem, bool>> CombineOr(

  10:         IEnumerable<Expression<Func<SPListItem, bool>>> expressions)

  11:     {

  12:         ...

  13:     }

  14: }

They are very similar to WhereAll() and WhereAny() methods of IQuery. The difference is that IQuery methods return instance of IQuery (as we use fluent interfaces in Camlex), while methods of ExpressionsHelper return expression which is result of combining specified in parameter expressions using AND or OR. Actually WhereAll() and WhereAny() are implemented using CombineOr() and CombineAnd():

   1: public IQuery Where(Expression<Func<SPListItem, bool>> expr)

   2: {

   3:     ...

   4: }

   5:  

   6: public IQuery WhereAll(IEnumerable<Expression<Func<SPListItem, bool>>> expressions)

   7: {

   8:     var combinedExpression = ExpressionsHelper.CombineAnd(expressions);

   9:     return this.Where(combinedExpression);

  10: }

  11:  

  12: public IQuery WhereAny(IEnumerable<Expression<Func<SPListItem, bool>>> expressions)

  13: {

  14:     var combinedExpression = ExpressionsHelper.CombineOr(expressions);

  15:     return this.Where(combinedExpression);

  16: }

As you can see we use ExpressionsHelper in order to combine list of expressions passed to the method and then just call regular Where() method which receives single combined expression.

Ok, after we checked how it is implemented let’s see how it works in real life scenarios. Suppose that we need to retrieve all documents which with Russian or English languages (metadata field Language is equal to English or Russian), and of appropriate type which is specified dynamically – Word, Excel, etc. I.e. we don’t know what types will be specified at compile time – they will be passed in run-time and we need to build dynamic query based on them.

So we can write our condition using pseudo code by the following way:

(Language = Russian or Language = English) and (FileLeafRef contains “.docx” or FileLeafRef contains “.xlsx” or …)

And here is the code which is needed in order to build CAML query for it:

   1: // Language = Russian or Language = English

   2: var languageConditions = new List<Expression<Func<SPListItem, bool>>>();

   3: languageConditions.Add(x => (string)x["Language"] == "Russian");

   4: languageConditions.Add(x => (string)x["Language"] == "English");

   5: var langExpr = ExpressionsHelper.CombineOr(languageConditions);

   6:  

   7: // FileLeafRef contains “.docx” or FileLeafRef contains “.xlsx” or ...

   8: var extenssionsConditions = new List<Expression<Func<SPListItem, bool>>>();

   9: var extensions = new[] {".docx", ".xlsx", ".pptx"};

  10: foreach (string e in extensions)

  11: {

  12:     string ext = e;

  13:     extenssionsConditions.Add(x => ((string)x["FileLeafRef"]).Contains(ext));

  14: }

  15: var extExpr = ExpressionsHelper.CombineOr(extenssionsConditions);

  16:  

  17: // (Language = Russian or Language = English) and

  18: // (FileLeafRef contains “.docx” or FileLeafRef contains “.xlsx” or ...)

  19: var expressions = new List<Expression<Func<SPListItem, bool>>>();

  20: expressions.Add(langExpr);

  21: expressions.Add(extExpr);

  22:  

  23: Console.WriteLine(CamlexNET.Camlex.Query().WhereAll(expressions));

At lines 1-5 we create condition: Language = Russian or Language = English). Then on lines 7-15 condition for extensions. As you can see extensions are specified in the list, so they can be retrieved e.g. from settings storage and size of this list may change. In all cases Camlex will build correct CAML (the only case which you need to handle is when list will no contain any elements, but this exercise is out of scope of current post). And then we combine both expressions using AND operation – lines 17-21. As result we get the following CAML:

   1: <Where>

   2:   <And>

   3:     <Or>

   4:       <Eq>

   5:         <FieldRef Name="Language" />

   6:         <Value Type="Text">Russian</Value>

   7:       </Eq>

   8:       <Eq>

   9:         <FieldRef Name="Language" />

  10:         <Value Type="Text">English</Value>

  11:       </Eq>

  12:     </Or>

  13:     <Or>

  14:       <Or>

  15:         <Contains>

  16:           <FieldRef Name="FileLeafRef" />

  17:           <Value Type="Text">.docx</Value>

  18:         </Contains>

  19:         <Contains>

  20:           <FieldRef Name="FileLeafRef" />

  21:           <Value Type="Text">.xlsx</Value>

  22:         </Contains>

  23:       </Or>

  24:       <Contains>

  25:         <FieldRef Name="FileLeafRef" />

  26:         <Value Type="Text">.pptx</Value>

  27:       </Contains>

  28:     </Or>

  29:   </And>

  30: </Where>

If you add new extension (e.g. pdf) Camlex will rebuild query automatically:

   1: <Where>

   2:   <And>

   3:     <Or>

   4:       <Eq>

   5:         <FieldRef Name="Language" />

   6:         <Value Type="Text">Russian</Value>

   7:       </Eq>

   8:       <Eq>

   9:         <FieldRef Name="Language" />

  10:         <Value Type="Text">English</Value>

  11:       </Eq>

  12:     </Or>

  13:     <Or>

  14:       <Or>

  15:         <Or>

  16:           <Contains>

  17:             <FieldRef Name="FileLeafRef" />

  18:             <Value Type="Text">.docx</Value>

  19:           </Contains>

  20:           <Contains>

  21:             <FieldRef Name="FileLeafRef" />

  22:             <Value Type="Text">.xlsx</Value>

  23:           </Contains>

  24:         </Or>

  25:         <Contains>

  26:           <FieldRef Name="FileLeafRef" />

  27:           <Value Type="Text">.pptx</Value>

  28:         </Contains>

  29:       </Or>

  30:       <Contains>

  31:         <FieldRef Name="FileLeafRef" />

  32:         <Value Type="Text">.pdf</Value>

  33:       </Contains>

  34:     </Or>

  35:   </And>

  36: </Where>

This is how ExpressionsHelper helps you build complex dynamic CAML queries. Recently I used it by myself in one of the project in order to create very complex query – and it really simplified the task. So we use our project also very intensively in the every day development.

Cache and security trimming in Sharepoint

If you use cache (I’m talking about not only standard ASP.Net cache, but cache in general) in the Sharepoint web application, you should keep in mind that users with different permissions may see different content on the site. E.g. if you crawl site and all its sub sites for the documents which are tagged by particular managed metadata term in the custom web part and want to speed up the process by adding caching, you should wonder does current user have access to the particular document before to show the link to this document even if it comes from the cache. Often developers forget about it.

Consider the following example: user A with administrative permissions comes on site – web part crawls the sub sites, stores result in the cache and display them in UI. Then user B with reader permissions comes on the same site. If web part will use cache which was filled for user A, then user B may see documents which he can’t access. The issue can be solved by adding SPContext.Current.Web.CurrentUser.ID to the cache key, so different users will have own cache. If you wonder about amount of users, you may use more common identifier – e.g. group id for he users with similar permissions. In this case users within one group will use the same cache. For those users which belong to several groups – you should rather create cache for each possible combination of the groups or use cache of the most powerful group.

Use Telerik Rad Editor Lite without features activation

In one my previous post I wrote how to use Telerik Rad Editor Lite for Firefox (and other than IE browsers). Here we will extend the control which we created in order to use it in all sites without additional actions from your side. In order to use Telerik Rad Editor you need to activate 2 features:

• Use RadEditor to edit List Items (located in RadEditorFeature)
• Use RadEditor to edit List Items in Internet Explorer as well (located in RadEditorFeatureIE)

First feature during activation copies new rendering template for RichTextField with Telerik control which replaces default Sharepoint editor for html fields (copying is done in feature receiver). Second feature doesn’t have feature receiver – instead it has activation dependency on 1st feature and used as a flag (see below).

Control checks that these features are activated in the OnLoad() method:

   1: protected override void OnLoad(EventArgs e)

   2: {

   3:     base.ToolsFile = base.SetWebSpecificConfiguration(base.RadControlsDir + "Editor/ListToolsFile.xml");

   4:     base.ConfigFile = base.SetWebSpecificConfiguration(base.RadControlsDir + "Editor/ListConfigFile.xml");

   5:     base.OnLoad(e);

   6:     if (this.ShowEditorOnPage())

   7:     {

   8:         this.SetSPField();

   9:         ((HtmlControl) this.htmlBox.Parent).Style.Add("display", "none");

  10:         if (this.baseField.NumberOfLines > 6)

  11:         {

  12:             this.Height = (this.baseField.NumberOfLines * this.FontSizeCoef) * 2;

  13:         }

  14:         if (this.rtField.DisplaySize > 0x4b)

  15:         {

  16:             this.Width = this.rtField.DisplaySize * this.FontSizeCoef;

  17:         }

  18:         if (this.baseField.RichTextMode == SPRichTextMode.Compatible)

  19:         {

  20:             base.Toolbars.Remove(base.Toolbars["EnhancedToolbar"]);

  21:             base.ShowHtmlMode = false;

  22:         }

  23:         this.RegisterSubmitScript();

  24:         this.Page.PreRenderComplete += new EventHandler(this.Page_PreRenderComplete);

  25:     }

  26:     else

  27:     {

  28:         this.Visible = false;

  29:     }

  30: }

  31:  

  32: private bool ShowEditorOnPage()

  33: {

  34:     SPFeature feature = this.GetFeature("F374A3CA-F4A7-11DB-827C-8DD056D89593");

  35:     SPFeature feature2 = this.GetFeature("747755CD-D060-4663-961C-9B0CC43724E9");

  36:     if ((feature2 == null) || (feature2.Definition == null))

  37:     {

  38:         return false;

  39:     }

  40:     if (((feature == null) || (feature.Definition == null)) &&

  41:         (this.Page.Request.Browser.IsBrowser("IE") && (this.Page.Request.Browser.MajorVersion >= 6)))

  42:     {

  43:         return false;

  44:     }

  45:     return true;

  46: }

If we want to use Telerik editor on all sites without activating the features, we need to override ShowEditorOnPage() method and always return true.

In the previous post we already created custom control which inherits RadHtmlListField. Now we need extend it – override OnLoad() method. As it access private variables and methods we need to reuse reflection in order to simulate work of the RadHtmlListField. Mostly all places where it access private variables can be solved with basic reflection, but not all. See that it calls base.OnLoad() method (line 5), i.e. it calls OnLoad() method of the base class MOSSRadEditor. As we have our own class which inherits RadHtmlListField, then inheritance chain looks like this:

AllBrowsersHtmlListField –> RadHtmlListField –> MOSSRadEditor

In our AllBrowsersHtmlListField we want to call MOSSRadEditor.OnLoad(), but not RadHtmlListField.OnLoad() – it means that we can’t call base.OnLoad(). I.e. we need to skip one tier in the inheritance chain and call virtual method OnLoad(), but not from base class, but from base of base class. And this is not trivial task because it means that we need to avoid the rules of the underlying programming language.

Investigation of this problem showed that problem is really not simple. But in this forum thread I found interesting mention that DynamicMethod can be used for this. The idea is that we will build new method in runtime using IL instructions and then call it. In our case it will look like this:

   1: private void NewOnLoad(EventArgs eventArgs)

   2: {

   3:     BindingFlags bf = 0 | BindingFlags.Instance | BindingFlags.Static |

   4:         BindingFlags.Public | BindingFlags.NonPublic;

   5:     MethodInfo mi = this.GetType().BaseType.BaseType.FindMembers(MemberTypes.Method,

   6:         bf, Type.FilterName, "OnLoad")[0] as MethodInfo;

   7:     DynamicMethod dm = new DynamicMethod("BaseBaseOnLoad", null,

   8:         new Type[] { this.GetType(), typeof(EventArgs) }, this.GetType());

   9:     ILGenerator gen = dm.GetILGenerator();

  10:     gen.Emit(OpCodes.Ldarg_0);

  11:     gen.Emit(OpCodes.Ldarg_1);

  12:     gen.Emit(OpCodes.Call, mi);

  13:     gen.Emit(OpCodes.Ret);

  14:  

  15:     var BaseBaseOnLoad = (Action<AllBrowsersHtmlListField, EventArgs>)

  16:         dm.CreateDelegate(typeof(Action<AllBrowsersHtmlListField, EventArgs>));

  17:     BaseBaseOnLoad(this, eventArgs);

  18: }

At first we get a reference to the MOSSRadEditor.OnLoad() method (lines 3-6). Then we create new DynamicMethod (BaseBaseOnLoad) and specify in parameters type of AllBrowsersHtmlListField (type of object which is used for calling) and EventArgs which is passed to the OnLoad() method. Then using IL generator we create method’s body (lines 10-13). Note that on line 9 we construct call to the method which actually makes trick – because this call will go to the MOSSRadEditor type. Then we create delegate and call it with parameters which we specified earlier (lines 15-17).

It was complex part. The rest of things can be done with basic reflection. We will need extended ReflectionHelper class:

   1: public static class ReflectionHelper

   2: {

   3:     public static object CallMethod(object obj, string name, params object[] argv)

   4:     {

   5:         BindingFlags bf = 0 | BindingFlags.Instance | BindingFlags.Static |

   6:             BindingFlags.Public | BindingFlags.NonPublic;

   7:         MethodInfo mi = obj.GetType().BaseType.FindMembers(MemberTypes.Method,

   8:             bf, Type.FilterName, name)[0] as MethodInfo;

   9:         return mi.Invoke(obj, argv);

  10:     }

  11:  

  12:     public static object GetFieldValue(object obj, string name)

  13:     {

  14:         BindingFlags bf = 0 | BindingFlags.Instance | BindingFlags.Static |

  15:             BindingFlags.Public | BindingFlags.NonPublic;

  16:         FieldInfo fi = obj.GetType().BaseType.FindMembers(MemberTypes.Field,

  17:             bf, Type.FilterName, name)[0] as FieldInfo;

  18:         return fi.GetValue(obj);

  19:     }

  20:  

  21:     public static void SetFieldValue(object obj, string name, object value)

  22:     {

  23:         BindingFlags bf = 0 | BindingFlags.Instance | BindingFlags.Static |

  24:             BindingFlags.Public | BindingFlags.NonPublic;

  25:         FieldInfo fi = obj.GetType().BaseType.BaseType.BaseType.FindMembers(MemberTypes.Field,

  26:             bf, Type.FilterName, name)[0] as FieldInfo;

  27:         fi.SetValue(obj, value);

  28:     }

  29: }

With this helper class we can change all calls to the private members and perform them by reflection. Final solution will look like this:

   1: public class AllBrowsersHtmlListField : RadHtmlListField

   2: {

   3:     private void checkForFirefox()

   4:     {

   5:         HttpRequest request = HttpContext.Current.Request;

   6:         HttpBrowserCapabilities browser = request.Browser;

   7:         if ((browser.Browser.ToLower() == "firefox"))

   8:         {

   9:             ReflectionHelper.SetFieldValue(this, "_browserCapabilitiesRetrieved", true);

  10:             ReflectionHelper.SetFieldValue(this, "_isSupportedBrowser", true);

  11:         }

  12:     }

  13:  

  14:     protected override void OnLoad(EventArgs e)

  15:     {

  16:         this.checkForFirefox();

  17:  

  18:         base.ToolsFile = base.SetWebSpecificConfiguration(base.RadControlsDir +

  19:             "Editor/ListToolsFile.xml");

  20:         base.ConfigFile = base.SetWebSpecificConfiguration(base.RadControlsDir +

  21:             "Editor/ListConfigFile.xml");

  22:         //base.OnLoad(e);

  23:         this.NewOnLoad(e);

  24:         //if (this.ShowEditorOnPage())

  25:         if (this.NewShowEditorOnPage())

  26:         {

  27:             //this.SetSPField();

  28:             ReflectionHelper.CallMethod(this, "SetSPField");

  29:  

  30:             var htmlBox = ReflectionHelper.GetFieldValue(this, "htmlBox")

  31:                 as TextBox;

  32:             ((HtmlControl)htmlBox.Parent).Style.Add("display", "none");

  33:  

  34:             var baseField = ReflectionHelper.GetFieldValue(this, "baseField")

  35:                 as SPFieldMultiLineText;

  36:             if (baseField.NumberOfLines > 6)

  37:             {

  38:                 this.Height = (baseField.NumberOfLines * this.FontSizeCoef) * 2;

  39:             }

  40:  

  41:             var rtField = ReflectionHelper.GetFieldValue(this, "rtField")

  42:                 as RichTextField;

  43:             if (rtField.DisplaySize > 0x4b)

  44:             {

  45:                 this.Width = rtField.DisplaySize * this.FontSizeCoef;

  46:             }

  47:             if (baseField.RichTextMode == SPRichTextMode.Compatible)

  48:             {

  49:                 base.Toolbars.Remove(base.Toolbars["EnhancedToolbar"]);

  50:                 base.ShowHtmlMode = false;

  51:             }

  52:             //this.RegisterSubmitScript();

  53:             ReflectionHelper.CallMethod(this, "RegisterSubmitScript");

  54:             this.Page.PreRenderComplete += NewPreRenderComplete;

  55:         }

  56:         else

  57:         {

  58:             this.Visible = false;

  59:         }

  60:     }

  61:  

  62:     private void NewOnLoad(EventArgs eventArgs)

  63:     {

  64:         BindingFlags bf = 0 | BindingFlags.Instance | BindingFlags.Static |

  65:             BindingFlags.Public | BindingFlags.NonPublic;

  66:         MethodInfo mi = this.GetType().BaseType.BaseType.FindMembers(MemberTypes.Method,

  67:             bf, Type.FilterName, "OnLoad")[0] as MethodInfo;

  68:         DynamicMethod dm = new DynamicMethod("BaseBaseOnLoad", null,

  69:             new Type[] { this.GetType(), typeof(EventArgs) }, this.GetType());

  70:         ILGenerator gen = dm.GetILGenerator();

  71:         gen.Emit(OpCodes.Ldarg_0);

  72:         gen.Emit(OpCodes.Ldarg_1);

  73:         gen.Emit(OpCodes.Call, mi);

  74:         gen.Emit(OpCodes.Ret);

  75:  

  76:         var BaseBaseOnLoad = (Action<AllBrowsersHtmlListField, EventArgs>)

  77:             dm.CreateDelegate(typeof(Action<AllBrowsersHtmlListField, EventArgs>));

  78:         BaseBaseOnLoad(this, eventArgs);

  79:     }

  80:  

  81:     private bool NewShowEditorOnPage()

  82:     {

  83:         return true;

  84:     }

  85:  

  86:     protected void NewPreRenderComplete(object sender, EventArgs e)

  87:     {

  88:         ReflectionHelper.CallMethod(this, "Page_PreRenderComplete", sender, e);

  89:     }

  90: }

It works in FF (and you can extend it also for other browsers) and doesn’t require features activation. Note that it will be used on all sites in your farm because rendering template is used globally by Sharepoint. However you can easily to change this behavior – check url of the current web application in the NewShowEditorOnPage() method and decide whether or not you need to use Telerik control.

This post showed that how knowledge of advanced language features may help in applied Sharepoint development. Hope it will help you in your work.