Wednesday, January 19, 2011

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:

image

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.

    image
    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.

13 comments:

  1. Thanks for providing nice information about sharepoint 2010 bugs. Every sharepoint developers should know this bug

    ReplyDelete
  2. I've tried executing SPMetal commands directly for generating my LINQ-to-SharePoint entity classes, as well as the CKS add-on (which I think just executes the SPMetal command under the hood). While this works against some of my SharePoint sites, I have one site that I'm currently working on where no class is generated. I instead get an error message that says:
    The following error has occured while executing SPMetal:Error: Cannot convert to a valid identifier.

    My solution deploys successfully via Visual Studio (and functions just as I would wish in the browser), but I'm guessing there must be a list Schema.xml or some other Elements.xml file that has syntax which is making CKS (SPMetal) unhappy.

    Has anyone else encountered this error?

    ReplyDelete
  3. @bkwdesign, In this case I can suggest you to try to find out the list which is the culprit and fix the list issue.

    ReplyDelete
  4. Hi.. The Article is great but what if projected fields are of type choice. It is easy to filter the records based on child table if the type is text but with choice fields are making me work hard... If you do not understand then here is an example...
    List1 - "Employee Course"
    Fields - 1."Account - Text" 2."Course - Lookup".

    List 2 - "Course"
    Fields - 1."Name - text",2."Type - Choice(A,B,C..)" ...

    how can i get all the courses of type a or b ...

    ReplyDelete
  5. @Prathmesh, Have you generated entity classes from SharePoint site as described in step 2? Once you have generated entity classes, you can write a linq to get what you want. And then finally you can get the caml from the linq.

    ReplyDelete
  6. change
    TextWriter textWriter = new StreamWriter(@"c:\caml.txt",false);

    to

    TextWriter textWriter = new StreamWriter(@"c:\caml.xml",false);

    ReplyDelete
  7. Hi
    I tried your console app to generate the CAML code, it does not work for me.The CAML code that generated does not have the join and projected fields section. I also need to modify the code slightly to make it work.(change from o in dataContext.Orders to from o in dataContext.Orders.toList(),otherwise I will get run-time exception).Any help on this would be much appropriated.

    ReplyDelete
  8. Make sure your linq is properly written. If you use join and projection properly then those should be translated into caml. Also please make sure you are not overwriting CAML in file with new one.

    ReplyDelete
  9. Thanks for your reply , I double checked my linq query,I have to add the toList() method in order to make the query run.The exception is explained http://blog.sharepointsite.co.uk/2010/06/inefficient-queries-spmetal-join-issue.html and not luck of getting the CAML correctly.

    ReplyDelete
  10. As you can't run the query in a single statement (ToList() is unique and execute immediately), it seems you can use a single camel to get the join. Since the join is not supported without using ToList, your linq query is actually executing twice, once for ToList and another for other part. So you need to use two caml queries I think.

    ReplyDelete
  11. We performed three inner joins on the SharePoint lists. We got the CAML query generated. But the query does not have the Joins tags in the CAML.
    We donot have any Lookup columns in the list. Is that the reason we are not getting Joins tag?

    ReplyDelete
  12. U2U is great but I am having an issue connecting to a list that is linked to a SQL database. U2U sees the list but not the columns. How do I get it to recognize the columns so I can get my query to work?

    ReplyDelete
  13. Did you know that there is a new Caml Designer tool out? it will generate even the code snippets for you

    http://karinebosch.wordpress.com/

    The new one is just released yesterday and it's for SharePoint 2013.

    Kind regards
    andy

    ReplyDelete