Using LINQ to query Sharepoint lists

When I started using Sharepoint 2010 with .NET, I used the Sharepoint 2010 API to query the Sharepoint Lists, but when I come with lookup column with multiple values, it started to be really difficult to use.

So I found that I can use LINQ to query Sharepoint lists and for what I saw on the internet, it was really simple to use. LINQ provide a complete API to query data and to update them. More than that, LINQ is also able to do mapping object-relational, what is really helpful in code lisibility.

In this code, I will take as example a Sharepoint site with two lists :

• departments : only a Title column
• projects : Title, year, nbstudents and department, a lookup column to departments with multiple values

So first of all, you need to create the entities and the data context, but you don't have to do it by hand. There is a tool that make all that for you, SPMetal.

Open a command line and cd to "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\bin", and then you can use the following command :

SPMetal.exe /web:http://localhost/ /code:C:\Projects.cs

You can use the URL of your Sharepoint site and you can change the name of the C# file. This will generate a long file containing the DataContext and the mapping objects. Here is a part of the code I get in this file :

public partial class ProjectsDataContext : Microsoft.SharePoint.Linq.DataContext {

    #region Extensibility Method Definitions
    partial void OnCreated();
    #endregion

    public ProjectsDataContext(string requestUrl) : this(requestUrl, true) {

    }

    public ProjectsDataContext(string requestUrl, Boolean tracking) : base(requestUrl)
    {
        this.OnCreated();
        ObjectTrackingEnabled = tracking;
    }

    [Microsoft.SharePoint.Linq.ListAttribute(Name="departments")]
    public Microsoft.SharePoint.Linq.EntityList<item> Departments {
        get {
            return this.GetList<item>("departments");
        }
    }

        //...

Add this file to your Visual Studio Project. You will have some errors because, you must include the reference to LINQ for Sharepoint. Right click the preferences of your project, choose "Add reference" and add the Microsoft.SharePoint.Linq.dll and now you're ready to use LINQ for Sharepoint in your project.

Query Data

First, you can query data using LINQ, by example, getting all the projects :

using (ProjectsDataContext context = new ProjectsDataContext(SPContext.Current.Web.Url))
{
    var query = from c in context.Projects select c;

    foreach (var project in query)
    {
        console.writeLine("Title : " + project.Title.ToString());
        console.writeLine("Year: " + project.Year.ToString());
        console.writeLine("Nb of students : " + project.Nbstudents.ToString());

        string str = "";

        int index = 0;
        foreach (Item item in project.Department)
        {
            str += index++ > 0 ? ", " + item.Title : item.Title;
        }

        console.WriteLine(str);
    }
}

You can see that it's really simple and a lot easier than the way with Sharepoint API. More than that, the mapping to Object is just great :)

Update Data

You can also insert data, really easily with LINQ :

using (ProjectsDataContext context = new ProjectsDataContext(SPContext.Current.Web.Url))
{
    Item department = new Item();
    department.Title = "New department";

    context.Departments.InsertOnSubmit(department);

    context.SubmitChanges();
}

To delete a Data, you can query it and then use DeleteOnSubmit to delete it. To update a data, it's even more simple, you just have to edit the item and then use SubmitChanges() to commit them.

As you can see, using LINQ allows you to write code simple, readable and all that faster than if you use Sharepoint API.

I hope this can be useful to someone.

Related articles