In SharePoint you can define lookup columns in your lists. The definition of a lookup field allows you to pick the target list and the target column to display in your lookup column. If I have a Product Group list and a Product list, I define a lookup column in the Product list to reference the Product Group list and I would use the Title as my display value. So when I browse the Product list I therefore see the Product Group Title as value of my lookup column.

What happens under the hood?

Under the hood – the lookup value is actually stored in an integer column in the SharePoint content database. When you display the Product list including the lookup column, a SQL Statement will be executed that joins the AllUserData column by the lookup id column and additionally selects the Title.

JOINing AllUserData via ID and SELECTing display value
JOINing AllUserData via ID and SELECTing display value

What if you want to get more from the lookup object than the display value?

If I want to show more than just the Title then I have to create my own Web Part for it. Unfortunately there is no option right now that allows you to display additional columns from the lookup object. Technically it would be easy as the exectuted SQL Statement from above could just be extended by adding the additional columns to the SELECT clause. As this is not possible we have to do it “manually”.

How to access lookup objects?

Basically you have to access the lookup object by it’s id. When you iterate through your main list you retrieve the lookup value which turns out to be not an ID but a string in the form of “#ID;Lookup Value”. You can use SPFieldLookupValue to parse this string and access the ID and the Value separately. The SPField object holds the information about the target SharePoint list. Following is a sample code to retrieve the SPListItem from a lookup value:

public SPListItem LookupValue(SPFieldLookup lookupField, SPFieldLookupValue lookupValue)
{
  Guid listGuid = new Guid(lookupField.LookupList);
  SPWeb currentWeb = SPContext.Current.Site.OpenWeb();
  SPList lookupList = currentWeb.Lists[listGuid];
  SPListItem lookupItem = lookupList.GetItemById(lookupValue.LookupId);
  // need to dispose SPWeb
  currentWeb.Dispose();
  return lookupItem;
}

Performance Considerations

If you just have to lookup one value to display in your web part than the above approach is the way to go. If you however have to display a large list and you will soon run into a performance problem because every lookup needs to make a roundtrip to the database. Displaying 100 Products and looking up the 100 Product Groups leads to 101 roundtrips to the database as the following PurePath and Database View shows:

N+1 Queries necessary to retrieve N items including lookup objects
N+1 Queries necessary to retrieve N items including lookup objects

Depending on the use case scenario you have two options:

  • Cache already retrieved lookup items: This makes sense if you display items that reference the same lookup object
  • Use a SPQuery to query all items at once: This will only require one roundtrip to the database

I guess I don’t need to explain caching. Just use a Hashtable to store the already retrieved SPListItem’s of the lookup values and only request those that you haven’t already in the map.

The SPQuery approach to batch request is a bit trickier but it is the most efficient one. The idea is to gather all the individual lookup id’s when iterating through the Product table. Once you are done with it, you can create a CAML query that uses nested ORs in the WHERE clause to retrieve all items at once. Here is a code snippet that creates and executes the CAML query:

public SPListItemCollection LookupValuesInBatch(Guid listGuid, ICollection lookupIds)
{
  SPQuery query = new SPQuery();
  StringBuilder sb = new StringBuilder();
  // we need to nest Or statements as an Or can only Or two values at a time
  // otherwise we would get the error "Cannot complete this action. Please try again"
  // see blog: http://www.sharepointblogs.com/jason/archive/2007/11/27/caml-query-lt-and-gt-lt-or-gt-can-only-compare-a-pair-at-a-time.aspx
  sb.Append("<Where>");
  for (int i = 1; i < lookupIds.Count; i++) sb.Append("<Or>");
  bool closeOr = false;
  foreach(int id in lookupIds)
  {
    sb.AppendFormat("<Eq><FieldRef Name=\"ID\"/><Value Type=\"Number\">{0}</Value></Eq>", id);
    if (closeOr) sb.Append("</Or>");
    else closeOr = true;
  }
  sb.Append("</Where>");
  query.Query = sb.ToString();
  SPList lookupList = SPContext.Current.Web.Lists[listGuid];
  SPListItemCollection items  = lookupList.GetItems(query);
  return items;
}

When GetItems is executed with a CAML query that contains nested ORs for every ID, only one SQL Statement is executed that uses the passed IDs in the WHERE clause:

Nested CAML ORs result in ORs in SQL WHERE
Nested CAML ORs result in ORs in SQL WHERE

Conclusion

If you need to display more than just the lookup value of your lookup field you need to manually query the lookup object. Depending on your use case you should consider either caching already retrieved items or requesting items in one batch. Otherwise, the performance of your web part degrades with the number of objects you have to lookup.

As SharePoint is built on the .NET Platform you might also be interested to learn more about .NET monitoring and SharePoint monitoring.