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.
  

SP 2010 - Using LINQ to SharePoint to Find List Items with Specific Managed Metadata Terms

Now that I can effectively use LINQ to access my Managed Metadata Columns, I'd like to only pull back those columns that contain values I need. For single valued Managed Metadata Columns, this is very straightforward:

var examples = from d in context.Examples
      where d.Specialty is TaxonomyFieldValue && ((TaxonomyFieldValue)d.Specialty).Label == "Value One"
               select d;

For multi valued Managed Metadata Columns, my first attempt was a bust. I tried the following expression, but received a compiler error, "An expression tree may not contain an anonymous method expression".

var examples = from d in context.Examples
               where ((TaxonomyFieldValueCollection)d.Specialty).Exists(delegate(TaxonomyFieldValue tfv){
             return tfv.Label == "Value One";
               }) != null
      select d;

The hint here from the compiler is that I can do this as long as I don't use an anonymous method. So I created a method to test for the term I wanted and changed my expression to call this method.

private static bool ContainsMetadataTerm(object o, string termLabel)
{
 bool exists = false;

 if (o is TaxonomyFieldValueCollection)
 {
  TaxonomyFieldValueCollection tfvc = (TaxonomyFieldValueCollection)o;
  exists = tfvc.Exists(delegate(TaxonomyFieldValue tfv)
  {
   return tfv.Label == termLabel;
  });
 }

 return exists;
}

var examples = from d in context.Examples
      where d.Specialty is TaxonomyFieldValueCollection && ContainsMetadataTerm(d.Specialty, "Value One")
      select d;

Now I can query specifically for list items that use specific terms. Keep in mind that this filtering does not happen until after the list items have been pulled down, so you may want to do some additional filtering to narrow the results so you don't run afoul of the new Throttling feature of SharePoint 2010.

Here was the CAML generated by the last LINQ query, which shows that the additional filtering for Term was done after the records were pulled.

  
    
      
        
        0x0100
      
    
  
  
    
    
    
    
    
    
    
    
    2147483647

SP 2010 - Managed Metadata Columns ARE Supported in LINQ to SharePoint

I apparently spoke too soon! You can get to Managed Metadata Columns in LINQ to SharePoint with SPMetal, just not directly off the command line. You need to supply a parameters option.

First you'll want to create your parameters file. I've found two different ways to get the Managed Metadata Column to show up. The first attempt I used this XML in my parameter file.

I saved this file to metaloptions.xml and then ran the following command.

SPMetal.exe /web:http://mysite /code:SPMySite.cs /namespace:SPMySite /parameters:metaloptions.xml

The output of that command will include a warning, but it seems to have no impact on LINQ working. I think it's purely informational and not relevant to what we are working with.

Warning: All content types for list Form Templates were excluded.

Now when I query with LINQ I see the hidden fields that power my Managed Metadata Column, which look familiar.

On my typed list item object, I now had three new fields:

• Specialty_0 - String
• TaxonomyCatchAllColumnCatchAllData - IList
• TaxonomyCatchAllColumnId - IList

Those fields hold values that look like this, respectively. Unfortunately, this isn't terribly useful.

• Value One|e203149a-6852-46fb-9d8e-9c21d350068d
• z4KDDWMtIUSjerk4bQvlyA==|0c7eej633Ee2lYK7KOL0jw==|mhQD4lJo+0adjpwh01AGjQ==
• 4

I tried another pass on my parameters file. This time I used the following XML.

On my typed list item object, I now have the one extra Column that I specified in my parameters file. LINQ will create a property for this field that is just an object. However, if you access this property, you can cast it to a TaxonomyFieldValueCollection or TaxonomyFieldValue, as appropriate. This provides exactly the information we wanted:

using (SPMySiteDataContext context = new SPMySiteDataContext("http://mysite"))
{
 var examples = from d in context.examples
      select d;

 foreach (var example in examples)
 {
  if (example.Specialty is TaxonomyFieldValueCollection)
  {
   foreach (TaxonomyFieldValue tfv in (TaxonomyFieldValueCollection)example.Specialty)
   {
    Console.WriteLine(tfv.Label);
   }
  }
  else if (example.Specialty is TaxonomyFieldValue)
  {
   TaxonomyFieldValue tfv = (TaxonomyFieldValue)example.Specialty;
   Console.WriteLine(tfv.Label);
  }
 }
}

SP 2010 - Managed Metadata Columns Not Supported in LINQ to SharePoint?

Today I am playing with LINQ to SharePoint. I created a simple list and added a Managed Metadata Column to my list. I then used SPMetal to generate a DataContext class. My Managed Metadata Column is nowhere to be found. I looked through options for SPMetal to see if maybe it just needed a switch to capture those Managed Metadata Columns, but I don't see one.

So it appears that Managed Metadata Columns have no support in LINQ to SharePoint. They are also not eligible to be Projected Fields. It would seem Microsoft didn't really flesh out all the ways the new Managed Metadata Service might be used.

Update: I figured out how to do this.