U2U CAML Query Builder for SharePoint 2010?

U2U CAML query builder is a great tool for SharePoint developer. I’ve used this tool frequently with SharePoint 2007 development. However, I’ve got into trouble with using the tool with SharePoint 2010. The tool is not updated for SharePoint 2010 yet. Though you can use U2U CAML builder with SharePoint 2010 (shown later in this post), you want to use new features of CAML (say join lists) and such new features are not supported in CAML builder. In this post I’ll show how you can generate CAML query for SharePoint 2010 using few tools and Linq query. First let’s discuss on how you can still use CAML builder to generate CAML for SharePoint 2010.

 

Use CAML Builder with SharePoint 2010 (I’ll not recommend)

Till now the CAML builder doesn’t support SharePoint 2010. However, you can make the tool working by using connecting via web service as shown below:

Figure 1: Use CAML builder with SharePoint 2010

 

However, you are not saved. As I mentioned already till the date I’m writing this post, the CAML builder is not updated to support SharePoint 2010. So new CAML features (like list joins) will not work in this old CAML builder.

 

Generate CAML from Linq-to-SharePoint (Better approach)

Though you can use old CAML builder with SharePoint 2010, since the CAML builder is not updated yet, you will not get the CAML new features when you will use CAML builder. Worried? Please don’t. There’s an way out. Let me explain

1. Download CKS Visual Studio Extension: There’s an useful and handy Visual Studio Extension for SharePoint developers known as Community Kit for SharePoint (CKS): Development tool edition. You can download the extension for SharePoint Server or for SharePoint Foundation.
   
   
2. Generate Entity classes from SharePoint site: Once you have installed the Community Kit for SharePoint, you can generate entity classes from Server Explorer. First open a SharePoint project in Visual Studio and then connect to the SharePoint server from Server Explorer ==> SharePoint Connections. Then right click on your web and click “Generate entity classes” as shown below. This will generate the entity classes in the current selected project of Visual Studio.
   
   
   Figure 2: Generate entity class from SharePoint site/web
   
   
   
3. Write Linq using Linq-to-SharePoint against generated entity classes: Once you have generated the entity classes as described on step2, you can use Linq to SharePoint to write your logic that you want to achieve through CAML. Once you write the Linq, you can run the code and log the CAML generated from the Linq. For example I have two lists Orders and Product. I want to join two lists to get order title and product name. The Linq will look like as shown below:
   
   

   using (var dataContext = new MySiteDataContext(siteUrl))
   {
       TextWriter textWriter = new StreamWriter(@"c:\caml.txt",false);
       dataContext.Log = textWriter;
       var result = from o in dataContext.Orders
                       join p in dataContext.Product on o.Product.Id equals p.Id
                       select new {OrderName = o.Title, p.ProductName};
       foreach (var v in result)
       {
           System.Console.WriteLine("{0}----{1}",v.OrderName,v.ProductName);
       }
   }
   Code snippet 1: Linq query using Linq to SharePoint

   
   
   As shown in the code snippet above, I have used the data context generated at step 2 and I have run a Linq query against two lists of the data context. The most import thing to notice in above code snippet is marked with yellow. I have instantiated a text writer (initialize with file stream) and then I had set it as the Log property of the datacontext. This will ensure that any CAML generated from the Linq query will be written in the writer. Once the query gets executed the CAML is dumped in the file (in my case C:\Caml.txt).
   
   So for generating any complex CAML query you can first write its equivalent Linq query and then get the CAML from log.
   
   
   
4. Get the CAML from the Linq query: After running the Linq to SharePoint query, you have got the CAML query in the log file as shown in step 3. However, you need to work a bit to make the CAML usable in SPQuery. The CAML generated from code snippet 1 is shown below:
   
   
   

   <View>
     <Query>
       <Where>
         <And>
           <BeginsWith>
             <FieldRef Name="ContentTypeId" />
             <Value Type="ContentTypeId">0x0100</Value>
           </BeginsWith>
           <BeginsWith>
             <FieldRef Name="ProductContentTypeId" />
             <Value Type="Lookup">0x0100</Value>
           </BeginsWith>
         </And>
       </Where>
       <OrderBy Override="TRUE" />
     </Query>
     <ViewFields>
       <FieldRef Name="Title" />
       <FieldRef Name="ProductProductName" />
     </ViewFields>
     <ProjectedFields>
       <Field Name="ProductProductName" Type="Lookup" List="Product" ShowField="ProductName" />
       <Field Name="ProductContentTypeId" Type="Lookup" List="Product" ShowField="ContentTypeId" />
     </ProjectedFields>
     <Joins>
       <Join Type="INNER" ListAlias="Product">
         <Eq>
           <FieldRef Name="Product" RefType="ID" />
           <FieldRef List="Product" Name="ID" />
         </Eq>
       </Join>
     </Joins>
     <RowLimit Paged="TRUE">2147483647</RowLimit>
   </View>

   Code snippet 2: CAML generated from Linq query of Code Snippet 1
   
   In the above code snippet, the lines marked with yellow can be modified if you want. Specially the content type in where part is put in the CAML to ensure only list items are selected. FYI, Content type 0x0100 means list item type. 
   
   
   
5. Use CAML query in SPQuery: Now you have got the CAML and you want to use the CAML in SPQuery. The following code snippet shows how I’ve used the CAML(from code snippet 2) in SPQuery:
   
   

   SPQuery query = new SPQuery();
   query.Query = @"<Where>
                       <And>
                       <BeginsWith>
                           <FieldRef Name='ContentTypeId' />
                           <Value Type='ContentTypeId'>0x0100</Value>
                       </BeginsWith>
                       <BeginsWith>
                           <FieldRef Name='ProductContentTypeId' />
                           <Value Type='Lookup'>0x0100</Value>
                       </BeginsWith>
                       </And>
                   </Where>
                   <OrderBy Override='TRUE' />";
   
   query.ViewFields = @"<FieldRef Name='Title' />
                       <FieldRef Name='ProductProductName' />";
   query.ProjectedFields = @"<Field Name='ProductProductName' Type='Lookup' 
                                    List='Product' ShowField='ProductName' />
                             <Field Name='ProductContentTypeId' Type='Lookup' 
                                    List='Product' ShowField='ContentTypeId' />";
   query.Joins = @"<Join Type='INNER' ListAlias='Product'>
                   <Eq>
                       <FieldRef Name='Product' RefType='ID' />
                       <FieldRef List='Product' Name='ID' />
                   </Eq>
                   </Join>";
   
   query.RowLimit = 2147483647;
   
   var list = web.Lists["Orders"];
   var items = list.GetItems(query);
   foreach (SPListItem item in items)
   {
       System.Console.WriteLine("{0}--{1}", item["Title"], item["ProductProductName"]);
   }

   Code Snippet 3: Using generated CAML in SPQuery
   
   

Integrating the Test Code in your Visual Studio Soltuion

You may need to generate the CAML from time to time in your development life cycle. My personal preference is to keep a devtest project in the Visual Studio solution to do work like this CAML generation. I keep a dev-test project (used for RnD like task), mainly console app, in the solution. So you can keep the dev-test project in your solution and in that project you can write the Linq to SharePoint query and generate the CAML. Since the project will always be in your Visual Studio solution, if you need to get the CAML anytime you can just write the Linq query in the dev-test project and run the project to get the CAML.

Conclusion

So here’s the summary on how to generate CAML query using the method described in this post:

• Install Visual Studio extension - CKS for SharePoint Server or for SharePoint Foundation.
• Generate entity classes from SharePoint site using the CKS feature
• Write Linq to SharePoint query and log the CAML in a file/Console
• Use the CAML in SPQuery

Though you can use Linq to SharePoint instead of CAML, but sometimes CAML is needed for raw query and this method will help you find out the CAML.