This post demonstrates how to use the JOIN syntax in CAML.

A customer pinged me asking about how to write a JOIN query in CAML.  Honestly, I haven’t done this since in a long time, so it took awhile to shake off the cobwebs.  I tried to make things explicit and easy to reproduce in your environment, please let me know in the comments of this post if something is unclear.

SharePoint 2010 adds the ability to create relational lists.  To use the JOIN operator in CAML, your lists must have a defined relation.  Let’s start by creating the lists and their relationships.

Creating the Lists

I first add a standard Contacts list named “DSE” and populate it with some data.

image

Go to the list settings for your new contact list and hover over the column names.  Many SharePoint developers forget this, the name you see in the web UI is the .Title property, which may be different than the internal name that SharePoint uses (the .InternalName property of the SPField object).  For a quick way to see what the actual field name is, hover over the column name and look in the address bar in your browser, and we see that the “Last Name” column is actually the “Title” field.

image

Next, I create a custom list named “Projects” and add a column named “Manager”.  The type is a lookup column.  Note the name “Manager”, we’ll refer to this name in our code.

image

In the additional column settings section for the “Manager” column, I set the lookup to the ID column for the DSE list, and additionally show the First Name and Last Name. 

image

When I add a new item to the Projects list, I get a drop-down that lets me select a value from the lookup column.

image

The result looks like this:

image

Now that we have the lists, a lookup field, and some data, let’s query it.

Querying Using JOINS and SPQuery

Nothing speaks louder than a code sample.  At the top of my code, I add a few using directives.

using System;
using System.Linq;
using Microsoft.SharePoint;

Next, the code to query the list. 

The first thing to notice is the SPQuery.Joins property which lets me provide 1 or more joins for the list.  The list must already have a lookup column and a relation defined to the list being joined to.  Notice in the Joins property that we refer to the name that we provided when we created the lookup column, “Manager”. 

The second thing to notice is the ProjectedFields property.  This is where we tell SharePoint how to project the lookup columns into the result.  In a contacts list, the “First Name” column has an internal name of “FirstName”.  The “Last Name” column is actually the Title column (see above for how to determine the .InternalName of a field). 

Finally, the ViewFields property lets us define which fields are included in the result.  We use the same name that we used in the ProjectedFields property.  This name could be anything, so long as the name in ProjectedFields and the name in ViewFields match.

class Program
{
static void Main(string[] args)
{
using (SPSite site = new SPSite("http://spstc.sharepoint.com"))
{
    SPWeb web = site.RootWeb;

    SPQuery query = new SPQuery();

    query.Joins = "<Join Type='INNER' ListAlias='DSE'>" +
                    "<Eq>" +
                        "<FieldRef Name='Manager' RefType='Id'/>" +
                        "<FieldRef List='DSE' Name='ID'/>" +
                    "</Eq>" +
                    "</Join>";
    query.ProjectedFields =
        "<Field Name='DSEFirstName' Type='Lookup' " +
                "List='DSE' ShowField='FirstName'/>" +
        "<Field Name='DSELastName' Type='Lookup' " +
                "List='DSE' ShowField='Title'/>";

    query.ViewFields = "<FieldRef Name='Title'/>" +
                        "<FieldRef Name='DSEFirstName'/>" +
                        "<FieldRef Name='DSELastName'/>";
    SPList customerList = web.Lists["Projects"];
    SPListItemCollection items = customerList.GetItems(query);
    foreach (SPListItem item in items)
    {
        SPFieldLookupValue dseLastName =
            new SPFieldLookupValue(item["DSELastName"].ToString());
        SPFieldLookupValue dseFirstName =
            new SPFieldLookupValue(item["DSEFirstName"].ToString());

        Console.WriteLine("{0}  {1}   {2}",
                item.Title,
                dseLastName.LookupValue,
                dseFirstName.LookupValue);
    }
}
}
}
}

The results are pretty unimpressive, but it proves that our CAML query works.

image

And there you have it, your first CAML JOIN query. 

Querying Using LINQ

Just for kicks, I will throw in a LINQ sample, too.  LINQ doesn’t support the JOIN operator, nor does it support a projection to a referenced entity.  What you can do, though, is just reference the entity in the results. 

I opened the Visual Studio 2010 Tools command window and ran the following command:

 

spmetal /web:http://spstc.sharepoint.com /namespace:Microsoft.PFE.DSE.Samples /code:PFE.cs
This generates the strongly-typed entities for me behind the scenes with metadata that tells LINQ how to query the results.  I include the result, PFE.cs, in my Visual Studio project and run the following code.  Notice I add the ctx.Log setting to log output to the Console window.  That lets me include a picture of a WHOLE BUNCH of CAML in the screen shot below.

 

using (PFEDataContext ctx = new PFEDataContext("http://spstc.sharepoint.com"))
{
    ctx.Log = Console.Out;
    var results = from c in ctx.Customers
                  select c;
    foreach (var item in results)
    {
        Console.WriteLine("{0}  {1}   {2}",
        item.Title,
        item.DSE.LastName,
        item.DSE.FirstName);
    }
}

Just to show the results…

image

Using your own JOIN operation can be more efficient, but test the results to be sure.  Often times LINQ will generate more efficient queries, but not always.