Use lambdas to build CAML requests in SharePoint

Article from colleagues, SharePoint-developers.
This article is written for those SharePoint -based product developers who occasionally encounter the need to make selections from one and / or several SharePoint lists — which, in principle, happens almost always :-). In this article I will discuss several ways to implement such samples with their advantages and disadvantages. At the end of the article, I will give an example of using the recently published Camlex.NET project ( http://camlex.codeplex.com/ ), which seems to me the most convenient for this case. So…

First approach

The first, which is the easiest way, is to go through the list, select the necessary records and, possibly, perform some operations with them, for example, sort:

SPWeb web = null ; SPList list = web.Lists[ "SomeList" ]; List foundItems = new List (); for ( int i=0; i<list.Items.Count; i++) { SPListItem item = list.Items[i]; if (( string ) item[ "TextField" ] == "Test" && (( int ) item[ "IntField" ] != 1 || ( DateTime )item[ "DateField" ] < DateTime .Today)) { foundItems.Add(item); } } foundItems.Sort( (x, y) => (( int )x[ "SortField" ]).CompareTo(( int )y[ "SortField" ])); * This source code was highlighted with Source Code Highlighter .

1. SPWeb web = null ; SPList list = web.Lists[ "SomeList" ]; List foundItems = new List (); for ( int i=0; i<list.Items.Count; i++) { SPListItem item = list.Items[i]; if (( string ) item[ "TextField" ] == "Test" && (( int ) item[ "IntField" ] != 1 || ( DateTime )item[ "DateField" ] < DateTime .Today)) { foundItems.Add(item); } } foundItems.Sort( (x, y) => (( int )x[ "SortField" ]).CompareTo(( int )y[ "SortField" ])); * This source code was highlighted with Source Code Highlighter .
2. SPWeb web = null ; SPList list = web.Lists[ "SomeList" ]; List foundItems = new List (); for ( int i=0; i<list.Items.Count; i++) { SPListItem item = list.Items[i]; if (( string ) item[ "TextField" ] == "Test" && (( int ) item[ "IntField" ] != 1 || ( DateTime )item[ "DateField" ] < DateTime .Today)) { foundItems.Add(item); } } foundItems.Sort( (x, y) => (( int )x[ "SortField" ]).CompareTo(( int )y[ "SortField" ])); * This source code was highlighted with Source Code Highlighter .
4. SPWeb web = null ; SPList list = web.Lists[ "SomeList" ]; List foundItems = new List (); for ( int i=0; i<list.Items.Count; i++) { SPListItem item = list.Items[i]; if (( string ) item[ "TextField" ] == "Test" && (( int ) item[ "IntField" ] != 1 || ( DateTime )item[ "DateField" ] < DateTime .Today)) { foundItems.Add(item); } } foundItems.Sort( (x, y) => (( int )x[ "SortField" ]).CompareTo(( int )y[ "SortField" ])); * This source code was highlighted with Source Code Highlighter .
5. SPWeb web = null ; SPList list = web.Lists[ "SomeList" ]; List foundItems = new List (); for ( int i=0; i<list.Items.Count; i++) { SPListItem item = list.Items[i]; if (( string ) item[ "TextField" ] == "Test" && (( int ) item[ "IntField" ] != 1 || ( DateTime )item[ "DateField" ] < DateTime .Today)) { foundItems.Add(item); } } foundItems.Sort( (x, y) => (( int )x[ "SortField" ]).CompareTo(( int )y[ "SortField" ])); * This source code was highlighted with Source Code Highlighter .
6. SPWeb web = null ; SPList list = web.Lists[ "SomeList" ]; List foundItems = new List (); for ( int i=0; i<list.Items.Count; i++) { SPListItem item = list.Items[i]; if (( string ) item[ "TextField" ] == "Test" && (( int ) item[ "IntField" ] != 1 || ( DateTime )item[ "DateField" ] < DateTime .Today)) { foundItems.Add(item); } } foundItems.Sort( (x, y) => (( int )x[ "SortField" ]).CompareTo(( int )y[ "SortField" ])); * This source code was highlighted with Source Code Highlighter .
7. SPWeb web = null ; SPList list = web.Lists[ "SomeList" ]; List foundItems = new List (); for ( int i=0; i<list.Items.Count; i++) { SPListItem item = list.Items[i]; if (( string ) item[ "TextField" ] == "Test" && (( int ) item[ "IntField" ] != 1 || ( DateTime )item[ "DateField" ] < DateTime .Today)) { foundItems.Add(item); } } foundItems.Sort( (x, y) => (( int )x[ "SortField" ]).CompareTo(( int )y[ "SortField" ])); * This source code was highlighted with Source Code Highlighter .
8. SPWeb web = null ; SPList list = web.Lists[ "SomeList" ]; List foundItems = new List (); for ( int i=0; i<list.Items.Count; i++) { SPListItem item = list.Items[i]; if (( string ) item[ "TextField" ] == "Test" && (( int ) item[ "IntField" ] != 1 || ( DateTime )item[ "DateField" ] < DateTime .Today)) { foundItems.Add(item); } } foundItems.Sort( (x, y) => (( int )x[ "SortField" ]).CompareTo(( int )y[ "SortField" ])); * This source code was highlighted with Source Code Highlighter .
9. SPWeb web = null ; SPList list = web.Lists[ "SomeList" ]; List foundItems = new List (); for ( int i=0; i<list.Items.Count; i++) { SPListItem item = list.Items[i]; if (( string ) item[ "TextField" ] == "Test" && (( int ) item[ "IntField" ] != 1 || ( DateTime )item[ "DateField" ] < DateTime .Today)) { foundItems.Add(item); } } foundItems.Sort( (x, y) => (( int )x[ "SortField" ]).CompareTo(( int )y[ "SortField" ])); * This source code was highlighted with Source Code Highlighter .
10. SPWeb web = null ; SPList list = web.Lists[ "SomeList" ]; List foundItems = new List (); for ( int i=0; i<list.Items.Count; i++) { SPListItem item = list.Items[i]; if (( string ) item[ "TextField" ] == "Test" && (( int ) item[ "IntField" ] != 1 || ( DateTime )item[ "DateField" ] < DateTime .Today)) { foundItems.Add(item); } } foundItems.Sort( (x, y) => (( int )x[ "SortField" ]).CompareTo(( int )y[ "SortField" ])); * This source code was highlighted with Source Code Highlighter .
11. SPWeb web = null ; SPList list = web.Lists[ "SomeList" ]; List foundItems = new List (); for ( int i=0; i<list.Items.Count; i++) { SPListItem item = list.Items[i]; if (( string ) item[ "TextField" ] == "Test" && (( int ) item[ "IntField" ] != 1 || ( DateTime )item[ "DateField" ] < DateTime .Today)) { foundItems.Add(item); } } foundItems.Sort( (x, y) => (( int )x[ "SortField" ]).CompareTo(( int )y[ "SortField" ])); * This source code was highlighted with Source Code Highlighter .
12. SPWeb web = null ; SPList list = web.Lists[ "SomeList" ]; List foundItems = new List (); for ( int i=0; i<list.Items.Count; i++) { SPListItem item = list.Items[i]; if (( string ) item[ "TextField" ] == "Test" && (( int ) item[ "IntField" ] != 1 || ( DateTime )item[ "DateField" ] < DateTime .Today)) { foundItems.Add(item); } } foundItems.Sort( (x, y) => (( int )x[ "SortField" ]).CompareTo(( int )y[ "SortField" ])); * This source code was highlighted with Source Code Highlighter .
13. SPWeb web = null ; SPList list = web.Lists[ "SomeList" ]; List foundItems = new List (); for ( int i=0; i<list.Items.Count; i++) { SPListItem item = list.Items[i]; if (( string ) item[ "TextField" ] == "Test" && (( int ) item[ "IntField" ] != 1 || ( DateTime )item[ "DateField" ] < DateTime .Today)) { foundItems.Add(item); } } foundItems.Sort( (x, y) => (( int )x[ "SortField" ]).CompareTo(( int )y[ "SortField" ])); * This source code was highlighted with Source Code Highlighter .
14. SPWeb web = null ; SPList list = web.Lists[ "SomeList" ]; List foundItems = new List (); for ( int i=0; i<list.Items.Count; i++) { SPListItem item = list.Items[i]; if (( string ) item[ "TextField" ] == "Test" && (( int ) item[ "IntField" ] != 1 || ( DateTime )item[ "DateField" ] < DateTime .Today)) { foundItems.Add(item); } } foundItems.Sort( (x, y) => (( int )x[ "SortField" ]).CompareTo(( int )y[ "SortField" ])); * This source code was highlighted with Source Code Highlighter .
15. SPWeb web = null ; SPList list = web.Lists[ "SomeList" ]; List foundItems = new List (); for ( int i=0; i<list.Items.Count; i++) { SPListItem item = list.Items[i]; if (( string ) item[ "TextField" ] == "Test" && (( int ) item[ "IntField" ] != 1 || ( DateTime )item[ "DateField" ] < DateTime .Today)) { foundItems.Add(item); } } foundItems.Sort( (x, y) => (( int )x[ "SortField" ]).CompareTo(( int )y[ "SortField" ])); * This source code was highlighted with Source Code Highlighter .

SPWeb web = null ; SPList list = web.Lists[ "SomeList" ]; List foundItems = new List (); for ( int i=0; i<list.Items.Count; i++) { SPListItem item = list.Items[i]; if (( string ) item[ "TextField" ] == "Test" && (( int ) item[ "IntField" ] != 1 || ( DateTime )item[ "DateField" ] < DateTime .Today)) { foundItems.Add(item); } } foundItems.Sort( (x, y) => (( int )x[ "SortField" ]).CompareTo(( int )y[ "SortField" ])); * This source code was highlighted with Source Code Highlighter .

Although the example is rather heavy, it presents almost all widely used things - fields of different types (string, int, DateTime), different comparison operations (==,! =, <), Different logical operations (AND, OR) and, in appendage, there is also sorting. The filter / sort semantics in our case is not important, so we will pay attention only to the syntax.
')
So - consider this example. It is in the old-school style - simple and intuitive. However, the performance of such a code is satisfactory only on very small lists containing literally several entries. There is more (let's say, 1,000 entries) on the lists - this code is terribly slow. The problem is that each record is drawn from the list (and this means from the content database) by a separate SQL request, while the filtering / sorting logic is located in the C # code.

To solve this performance problem, Microsoft has developed the SPQuery and SPSiteDataQuery classes . These classes accept SQL-like queries formatted in XML, and with their help, you can get the final result - respectively, filters and sorting / grouping are executed inside the SQL engine, and not in C # code. So:

Second approach

This approach is optimal in performance. The following example does the same as in the first example, but already uses the SPQuery class:

1. SPQuery query = new SPQuery ();
2. query.Query =
3. "<Where>" +
4. "<And>" +
5. "<Eq>" +
6. "<FieldRef Name = \" TextField \ "/>" +
7. "<Value Type = \" Text \ "> Test </ Value>" +
8. "</ Eq>" +
9. "<Or>" +
10. "<Neq>" +
11. "<FieldRef Name = \" IntField \ "/>" +
12. "<Value Type = \" Integer \ "> 1 </ Value>" +
13. "</ Neq>" +
14. "<Lt>" +
15. "<FieldRef Name = \" DateField \ "/>" +
16. "<Value Type = \" DateTime \ ">" +
17. "<Today />" +
18. "</ Value>" +
19. "</ Lt>" +
20. "</ Or>" +
21. "</ And>" +
22. "</ Where>" +
23. "<OrderBy>" +
24. "<FieldRef Name = \" SortField \ "/>" +
25. "</ OrderBy>" ;
26. SPListItemCollection resultItems = list.GetItems (query);

* This source code was highlighted with Source Code Highlighter .

Everything works and works quickly. Fine!
But the problems are also evident:

• Any syntax error when formatting XML causes exception.
• The unreadability of the code totally increases - not only does the size of the sample increase by 2 times, it becomes much harder to read compared to the first approach.
• When changing the filter, you need to rebuild the tree of XML nodes - for example, the node of each logical operation takes only 2 operands, and in case you need to add another condition to the OR, you will have to split the corresponding subassembly into 2 parts, and in our case , the overall depth of the tree is becoming frightening.
• Transition from the usual C # code to the option of presenting code as XML - and, as a result, loss of compile-time checks, Intellisense, etc. etc. Given the global tendency to support the code-first approach, this drawback becomes simply critical.

Consequence - general developer dissatisfaction :-)

Third approach

In the following example, we will use the open-source project CAML.NET - camldotnet.codeplex.com - (I really had to suffer a bit here, since the examples from the project documentation do not work - the API uses the missing CAML.Query () method ).
Again, the following example performs exactly the same as the previous examples (I left the arrangement of parentheses authentic - just as used in the examples for the project):

1. SPQuery query2 = new SPQuery ();
2. query2.Query =
3. CAML.Where (
4. CAML.And (
5. CAML.Eq (
6. CAML.FieldRef ( "TextField" ),
7. CAML.Value ( "Test" )),
8. CAML.Or (
9. CAML.Neq (
10. CAML.FieldRef ( "IntField" ),
11. CAML.Value (1)),
12. CAML.Lt (
13. CAML.FieldRef ( "Datefield" ),
14. CAML.Value ( DateTime .Today))))) +
15. CAML.OrderBy (
16. CAML.FieldRef ( "SortField" ));
17. SPListItemCollection resultItems2 = list.GetItems (query2);

* This source code was highlighted with Source Code Highlighter .

Well, that's better! Making mistakes when printing XML has already become significantly more difficult, and performance has remained at its best. The latter, however, is not surprising, since CAML.NET does not affect performance in any way, it’s just a thin wrapper over the standard CAML syntax — CAML.NET simply helps to prepare XML, nothing more. Which, however, is not at all bad!

If you take a closer look at the resulting code, it becomes clear that this is just an attempt to mimic the standard CAML syntax. As a consequence, this approach inherits many of the original CAML syntax problems. Of the problems that I mentioned above (in the description of the second approach), in fact, only one problem is solved - the problem of correct XML formatting, while all other problems remain. If you go to the CAML.NET project forum, you can see that users just complain about these problems.

Is there anything more convenient for the developer? I would not write this article if my answer was “no” :-)

Fourth approach

Just over a week ago, we posted a new project called Camlex.NET on CodePlex - the project aims to solve the mentioned problems with developing samples from SharePoint lists - to make this process as close and understandable as possible to a C # developer. The project's address is camlex.codeplex.com .

So, more to the point - let's reproduce the functionality of the previous examples, but already on the basis of the Camlex.NET project:

1. SPQuery query3 = new SPQuery ();
2. query3.Query = Camlex.Query ()
3. .Where (x => ( string ) x [ "TextField" ] == "Test" &&
4. (( int ) x [ "IntField" ]! = -1 || ( DateTime ) x [ "DateField" ] < DateTime .Now))
5. .OrderBy (x => x [ "SortField" ]). ToString ();
6. SPListItemCollection resultItems3 = list.GetItems (query3);

* This source code was highlighted with Source Code Highlighter .

And it's all! :-)

As you can see, this approach uses expression trees and lambda to its fullest, giving developers the opportunity to write C # code without scoring their head with the problems of using CAML syntax .

Advantages of this approach:

• The size of the code was even smaller than in the very first example, and the filters remained the same as there, simple and clear.
• Performance - at the level of the net CAML request.
• The ability to use when sampling from a variety of lists (instead of SPQuery, you must use SPSiteDataQuery ).
• Absence of XML syntax as such :-) Any filter changes are natural for C # developers, and will not require rebuilding the XML tree.
• The developer remains to the C # context. And that means Intellisense, compile-time checks, code-first approach, etc. etc.

In this article, I did not go into details of the Camlex.NET project - the format of the article will not allow me to do this, but if you go to the project site, you can see many other examples of use. In the same article, I just wanted to make comparisons of different sampling techniques from the SharePoint lists - and the decision on what to use is yours, dear readers!

Links

Lastly, once again the address of our project:
Camlex.NET on CodePlex 's - http://camlex.codeplex.com/ .

Developer Blogs:
Alexey Sadomov (my colleague) - http://sadomovalex.blogspot.com/
Vladimir Timashkov (your humble servant) - http://vtimashkov.wordpress.com/

I hope our project will be useful to you in your daily struggle with SharePoint :-)