UruIT Blog

LINQ to SharePoint is a great tool to perform queries against a SharePoint server since the 2010 version. Unlike the classical CAML queries, it allows to use a strongly-typed entity model and LINQ query syntax to query list data.

The SPMetal command

The first step to use LINQ to SharePoint is to run the SPMetal tool in order to create the entity model from an existent SharePoint site. This tool is located at 14\bin. Here’s a sample on how to use it:

SPMetal /web:http://mysharepointsite:9999 /code:Model.cs

This command will create a C# code file containing the entity model, in 14\bin\Model.cs. After adding this file to our project, we can perform queries using LINQ to SharePoint. For example, this server-side code, outputs the titles for all the items in “MyList” where the title length is at least 10 characters long:

StringBuilder output = new StringBuilder();
using (ModelDataContext model = new ModelDataContext(SPContext.Current.Site.Url))
{
    foreach (MyListItem itemWithoutTitle in model.MyList.Where(x => x.Title.Length >= 10))
    {
        output.AppendLine(itemWithoutTitle.Title);
    }
}

The missing fields

By default, the Created, CreatedBy, Modified and ModifiedBy fields are not created by SPMetal. However, the framework offers a way of extending the object-relational mapping system of the LINQ to SharePoint provider. In other words, we can easily use those fields after telling LINQ to SharePoint how to retrieve and update them from the content database.

We will extend the base entity class of our model (“Item” class) in a new code file (we can call it “ModelExtensions.cs” for example):

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint.Linq;
using Microsoft.SharePoint;

public partial class Item : ICustomMapping
{
    [CustomMapping(Columns = new String[] { "Modified", "Created", "Editor", "Author" })]
   public void MapFrom(object listItem)
    {
        SPListItem item = (SPListItem)listItem;
        this.Modified = (DateTime)item["Modified"];
        this.Created = (DateTime)item["Created"];
        this.CreatedBy = (string)item["Author"];
        this.ModifiedBy = (string)item["Editor"];
    }

    public void MapTo(object listItem)
    {
        SPListItem item = (SPListItem)listItem;
        item["Modified"] = this.Modified;
        item["Created"] = this.Created;
        item["Author"] = this.CreatedBy;
        item["Editor"] = this.ModifiedBy;
    }

    public void Resolve(RefreshMode mode, object originalListItem, object databaseObject)
    {
        SPListItem originalItem = (SPListItem)originalListItem;
        SPListItem databaseItem = (SPListItem)databaseObject;

        DateTime originalModifiedValue = (DateTime)originalItem["Modified"];
        DateTime dbModifiedValue = (DateTime)databaseItem["Modified"];

        DateTime originalCreatedValue = (DateTime)originalItem["Created"];
        DateTime dbCreatedValue = (DateTime)databaseItem["Created"];

        string originalCreatedByValue = (string)originalItem["Author"];
        string dbCreatedByValue = (string)databaseItem["Author"];

        string originalModifiedByValue = (string)originalItem["Editor"];
        string dbModifiedByValue = (string)databaseItem["Editor"];

        if (mode == RefreshMode.OverwriteCurrentValues)
        {
            this.Modified = dbModifiedValue;
            this.Created = dbCreatedValue;
            this.CreatedBy = dbCreatedByValue;
            this.ModifiedBy = dbModifiedByValue;
        }
        else if (mode == RefreshMode.KeepCurrentValues)
        {
            databaseItem["Modified"] = this.Modified;
            databaseItem["Created"] = this.Created;
            databaseItem["Author"] = this.CreatedBy;
            databaseItem["Editor"] = this.ModifiedBy;
        }
        else if (mode == RefreshMode.KeepChanges)
        {
            if (this.Modified != originalModifiedValue)
            {
                databaseItem["Modified"] = this.Modified;
            }
            else if (this.Modified == originalModifiedValue && this.Modified != dbModifiedValue)
            {
                this.Modified = dbModifiedValue;
            }

            if (this.Created != originalCreatedValue)
            {
                databaseItem["Created"] = this.Created;
            }
            else if (this.Created == originalCreatedValue && this.Created != dbCreatedValue)
            {
                this.Created = dbCreatedValue;
            }

            if (this.CreatedBy != originalCreatedByValue)
            {
                databaseItem["Author"] = this.CreatedBy;
            }
            else if (this.CreatedBy == originalCreatedByValue && this.CreatedBy != dbCreatedByValue)
            {
                this.CreatedBy = dbCreatedByValue;
            }

            if (this.ModifiedBy != originalModifiedByValue)
            {
                databaseItem["Editor"] = this.ModifiedBy;
            }
            else if (this.ModifiedBy == originalModifiedByValue && this.ModifiedBy != dbModifiedByValue)
            {
                this.ModifiedBy = dbModifiedByValue;
            }
        }
    }

    public DateTime Modified { get; set; }
    public DateTime Created { get; set; }
    public string CreatedBy { get; set; }
    public string ModifiedBy { get; set; }
}

For extended information of how the ICustomMapping interface works, you can check these MSDN articles: ICustomMapping Members and RefreshMode Enumeration.

After adding this file to our project, we can use Modified, Created, CreatedBy and ModifiedBy in our queries:

StringBuilder output = new StringBuilder();
using (ModelDataContext model = new ModelDataContext(SPContext.Current.Site.Url))
{
    DateTime date = DateTime.Parse(“Thu, 05 May 2011 12:46:00 GMT”);
    foreach (MyListItem itemCreatedAfterDate in model.MyList.Where(x => x.Created > date))
    {
        output.AppendLine(itemCreatedAfterDate.Title);
    }
}

Keep in mind that the Author and Editor fields identify users. These strings may have more information than what you need. An easy way of parsing this string to extract the information you need is to create a new SPFieldUserValue with the current SPWeb and the string. Then you can extract the actual SPUser from SPFieldUserValue.User.

I hope you find this code useful as I do. Enjoy!

Alfonso Cora.