How to Use LINQ in SharePoint 2010 Projects

Microsoft SharePoint 2010 brings support for Language-Integrated Query (LINQ) to the platform, which can improve the productivity and efficiency of developers reading and writing to SharePoint lists. However, many SharePoint developers don’t have a firm grasp of how LINQ works. So, I’ll provide a brief LINQ primer, then jump into how to use LINQ in SharePoint 2010 projects. I’ll then explain a limitation with the SharePoint-generated entity models and provide a solution for how to get around it.

A Brief LINQ Primer

Like all other platforms, SharePoint provides different ways for developers to read and write data in SharePoint sites. Developers use lists to store data in SharePoint sites, similar to how developers use tables to store data in Microsoft SQL Server databases. In the past, developers had to use different data access technologies to access the data on the various platforms. For example, developers had to use T-SQL to access SQL Server data, Collaborative Application Markup Language (CAML) to access SharePoint data, and XPath to access XML data. These data access technologies are very different and challenging to switch between.

To make it easier for developers, Microsoft introduced LINQ as part of the Microsoft .NET Framework 3.5. LINQ defines standard query operators that developers can use to create standard queries. Out of the box, LINQ supports querying against any object collection that implements the IEnumerable interface. This is because the compiler can interpret the query by examining the data source, the collection of objects, when projects are compiled.

However, in the case of data sources such as SQL Server, SharePoint, and XML, the compiler doesn’t know what the data source structure looks like. To address this, LINQ supports a provider model. Developers can create a provider that tells LINQ how to translate a LINQ query into something the data source understands. For example, the LINQ to SQL provider knows how to translate a LINQ query into a T-SQL query. For this to work, the compiler needs to understand the structure of the data source. This is done by using a special utility to create an entity model. An entity model is a code representation of the data source.

When working with SQL Server, developers use SQLMetal.exe to generate the entity model. This utility is run against the database and generates a code file that is added to the project. Developers then write queries against the entity model in the project. The entity model also enables Visual Studio (VS) to provide IntelliSense when writing queries.

LINQ for SharePoint

Although Microsoft Office SharePoint Server 2007 (MOSS 2007) SP1 added support for the .NET Framework 3.5 SP1, it didn’t add support for LINQ. So, there was no native support for writing LINQ queries against SharePoint lists and libraries.

The SharePoint 2010 product team changed this by including the LINQ to SharePoint provider and the SPMetal.exe utility. SharePoint 2010’s new support for LINQ can dramatically improve SharePoint developers’ productivity. For example, instead of having to write the cumbersome CAML queries, SharePoint developers can write LINQ queries. At compile time, the LINQ queries will be translated to CAML queries.

To use LINQ queries in SharePoint projects, developers must first create an entity model with SPMetal.exe. This utility creates an entity model based on a specific SharePoint site. By default, it creates models for all of the lists in the specified site. However, developers can pass in an XML file that specifies which lists to generate models for.

The generated entity model includes an object for each type of list as well as a collection to store items. For example, the entity model for a Customers list that’s based on the Contacts template will have the CustomersContact object as well as the Customers collection. The CustomersContact object will contain properties representing the fields in the Customers list. Developers can use the object and its properties to create, update, delete and select items from the Customers list. However, they might find that some fields don’t have matching properties because of a limitation in SharePoint-generated entity models.

The Limitation

Unfortunately, SharePoint-generated entity models have a limitation: They include properties for only those fields based on the field types in SharePoint Foundation 2010. The entity model doesn’t include properties for fields based on the field types in SharePoint Server 2010 or fields based on custom field types created by developers.

For example, if a developer has a list that contains a field based on the Managed Metadata field type in SharePoint Server 2010, a property representing that field won’t appear in the entity model. I’ve seen this catch many developers off guard. They’re left wondering where the field went or why it’s missing.

The reason for this limitation is that most of Microsoft’s developer tools are targeted for SharePoint Foundation 2010. This includes the SharePoint Developer Tools for Visual Studio 2010 as well as SPMetal.exe.

The Workaround

Thankfully Microsoft has provided a way to work around the entity model limitation. The entity models generated by SPMetal.exe contain partial classes. This means it’s easy to extend the entity models without having to inherit the model’s entities or make direct changes (which would be overwritten when the model is regenerated). To extend the entity model, you need to tell SharePoint four things about the additional field:

To tell SharePoint this information, you need to create a new partial class that mirrors the name and signature of the object you want to extend. You can find the full signature, including any objects it inherits, by looking at the entity model’s source code. For example, the aforementioned Customers list and CustomersContact object inherits from the Contact class. This class contains a few methods and a property. The property is what you use to read and write to the field. The methods address the latter three bullet points previously mentioned. Extending the model is done by implementing the interface Microsoft.SharePoint.Linq.ICustomMapping as shown:

public partial class CustomersContact: Contact, ICustomMapping \{…\}

Let’s say that you need to add a property representing a custom field named Location Tag, which is in the Customers list. First, you need to create the property that will be used in the LINQ queries with the code in Figure 1. Next, you need to implement the three methods defined in the ICustomMapping interface. The methods are as follows:

To add these three methods to the class, you’d use the code in Figure 2. After you recompile the project, you’d see the LocationTag property for the CustomersContact object in IntelliSense when writing LINQ queries or when creating new objects based on the CustomersContact object.

A Firm Grasp

If you didn’t have a firm grasp of LINQ previously, you should now know what it is and how to use it in SharePoint 2010 projects. To recap, to write LINQ queries, you need to use the LINQ to SharePoint provider and the SPMetal.exe utility. SPMetal.exe will create an entity model for you, but it creates properties for only those fields based on field types in SharePoint Foundation 2010. This means that it won’t create properties for fields based on field types in SharePoint Server 2010 (e.g., the Managed Metadata field type) or custom field types. However, you can extend the generated entity model through the use of partial classes and interfaces to add support for the omitted fields.

Figure 1: Code to create the property

public partial class CustomersContact : Contact, ICustomMapping \{private const string LOCATION_TAG_FIELD = "Location_x0020_Tag";
  private TaxonomyField _taxonomyFields;
  public TaxonomyField LocationTag \{
    get \{ return _taxonomyFields; \}
    set \{
      if (value != _taxonomyFields) \{
        this.OnPropertyChanging("LocationTag", _taxonomyFields);
        _taxonomyFields = value;
        this.OnPropertyChanged("LocationTag");
      \}
    \}
  \}
\}

Figure 2: Code to add the three methods

\[CustomMapping(Columns = new string\[\] \{ LOCATION_TAG_FIELD \})\]
public void MapFrom(object listItem) \{
  SPListItem item = listItem as SPListItem;
  this.LocationTag = item\[LOCATION_TAG_FIELD\] as TaxonomyField;
\}
public void MapTo(object listItem) \{
  SPListItem item = listItem as SPListItem;
  item\[LOCATION_TAG_FIELD\] = this.LocationTag;
\}
public void Resolve(RefreshMode mode, object originalListItem, object databaseListItem) \{
  SPListItem originalItem = originalListItem as SPListItem;
  SPListItem databaseItem = databaseListItem as SPListItem;
  TaxonomyField originalValue = originalItem\[LOCATION_TAG_FIELD\] as TaxonomyField;
  TaxonomyField databaseValue = databaseItem\[LOCATION_TAG_FIELD\] as TaxonomyField;
  switch (mode) \{
    case RefreshMode.OverwriteCurrentValues:
      this.LocationTag = databaseValue;
      break;
    case RefreshMode.KeepCurrentValues:
      databaseItem\[LOCATION_TAG_FIELD\] = this.LocationTag;
      break;
    case RefreshMode.KeepChanges:
      if (this.LocationTag != originalValue)
        databaseItem\[LOCATION_TAG_FIELD\] = this.LocationTag;
      else if (this.LocationTag == originalValue && this.LocationTag != databaseValue)
        this.LocationTag = databaseValue;
      break;
  \}
\}