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.
You know, I have been wondering about this tool in my article at http://sharepointdragons.com/2012/04/06/camlex-net/ Do you still feel it has a role since spmetal has been introduced? What are the specific advantages it has over the entities generated by spmetal. I'd love to see a blog post about that.
ReplyDeletehi nmbruggeman,
ReplyDeletethere are several things. First of all SPMetal won't help if you need to fetch data from several lists (e.g. using SPSiteDataQuery). Second, Camlex.Net is built in open source and is evolved by community. It mean that it is much easier to add new feature to it which you don't find in Linq2SP. Camlex have syntax sugar which makes development more enjoyable. Third, there are cases when using SPMetal is overengineering, e.g. when you develop standalone web part. Forth, we have online converter http://camlex-online.org which will convert existing CAML to Camlex. And of course we will continue development by adding new value to the Camlex.
Спасибо! Кучу времени сэкономил.
ReplyDeleteArtem,
ReplyDeleteExpressionsHelper - моя любимая часть camlex-а)
Hi Alexey, Not sure, I have started using it for dynamic queries for dynamic pagination and filtering, i see quite low performance with whereAll and whereAny
ReplyDeleteNikhil,
ReplyDeleteif structure (conditions) of the query is the same, but values are different, you may do the following: when create SPQuery first time instead of real values specify placeholders "{val1}", "{val2}", etc. After that cache created string query and replace placeholders using regular string.Replace() method.