SP 2010 - LINQ versus CAML Joins and the Nuances of Projected Fields

When working with relational lists in SharePoint 2010, you have the option to use LINQ to SharePoint or CAML to join those lists to pull data out. While LINQ is easier to use and will leverage CAML under the covers, it is not always capable of performing queries that CAML can directly.

For instance. Let's say you have a list, called Parent. This list has a single valued lookup column, PrimaryChild, that refers to the Children list. LINQ can very easily perform a query with a where clause based on PrimaryChild:

var parents = from p in context.Parents
       where p.PrimaryChild.Title == "My First Child"
       select p;

However, if you were to create a second lookup column, OtherChildren, that allowed multiple values, LINQ would run into difficulties because the lookup is now represented as an EntitySet. With a single valued lookup field, you would instead have just a strongly typed object, with direct access to the fields within that list item.

var parents = from p in context.Parents
       where p.OtherChildren.Any(c => c.Title == "My Other Child")
       select p;

Running this query will throw an exception that semi-efficient queries are not allowed. If you recall, this is because LINQ leverages Two-Stage Queries. So, you can continue with LINQ and perform the query in two stages, or you can change the query to use CAML.

Here is the same query in CAML, but this time, the query will not cause an exception.

using (SPSite site = new SPSite(SPContext.Current.Site))
{
 SPWeb web = site.RootWeb;
 if (web != null)
 {
  SPList list = web.Lists["Parents"];
  if (list != null)
  {
   SPQuery query = new SPQuery();
   StringBuilder sbQuery = new StringBuilder();
   sbQuery.Append("");
   sbQuery.Append("");
   sbQuery.Append("My Other Child");
   sbQuery.Append("");
   query.Query = sbQuery.ToString();

   StringBuilder sbJoins = new StringBuilder();
   sbJoins.Append("");
   sbJoins.Append("");
   sbJoins.Append("");
   sbJoins.Append("");
   sbJoins.Append(" ");
   sbJoins.Append(" ");
   query.Joins = sbJoins.ToString();

   StringBuilder sbProj = new StringBuilder();
   sbProj.Append("");
   query.ProjectedFields = sbProj.ToString();

   StringBuilder sbView = new StringBuilder();
   sbView.Append("");
   sbView.Append("");
   query.ViewFields = sbView.ToString();

   if (!string.IsNullOrEmpty(query.Query))
   {
    SPListItemCollection matches = list.GetItems(query);
    foreach (SPListItem match in matches)
    {
     Console.WriteLine(match["Title"]);

     string rawNickname = (string)match["OtherChildrenNickname"];
     if (!string.IsNullOrEmpty(rawNickname))
     {
      SPFieldLookupValue nickname = new SPFieldLookupValue(rawNickname);
      Console.WriteLine(nickname.LookupValue);
     }
    }
   }
  }
 }
}

You'll notice that in the example above I'm performing a CAML join and also leveraging Projected Fields. Here are some guidelines/rules to keep in mind:

• The CAML has several attributes that ask for the list name or list alias. This is NOT the actual name of the list. Rather, it is the internal name of the lookup field within your list. So in our example, the list was named Children, and the field was OtherChildren. We used OtherChildren to build the join and projected fields.
• Projected Fields used in your query do not have to match up to the Projected Fields you have specified in the Child list. Those are a UI convenience and not used by your CAML.
• If you want to display a value from the Child list, you need to make sure you have a Projected Field in your CAML. Only those fields which are projected are eligible to be View fields.
• All Projected Fields will become SPFieldLookupValue objects (or perhaps SPFieldLookupValueCollection, though I haven't yet had one of my lists do this). Those lookups will always contain the ID of the child list item, but the value of the selected field within that child item.