How to use LINQ to Sharepoint

LINQ or Language Integrated Query is a very useful language which reduces the complexity of accessing and integrating information that is not natively defined using OO technologies, with LINQ it adds native data querying capabilities to .NET languages which makes a developer's life really easy. Since it was launched, I started using it as it saves me a lot of time in coding my applications.

Now a part of what I am doing is developing custom web parts for Sharepoint and sometimes you need to access data to present that information to your users and it would be good if LINQ can be used to query what you want to make life easier. Well that is possible and can be implemented really easily. All you have to do is reference LINQ library from your project and generate entity classes, which provide an object oriented interface to the Microsoft SharePoint Foundation content databases which can be done by using spmetal.

Now, I will give you a step by step example on how to achieve this.

First, definitely you need to have your list to play with, so in this example, I will have Employees, Department and Position. I guess it’s logical how you would relate these lists together.

For the Employees list, we have the following columns shown in the image below, take note that the Last Name column is the Title column which was renamed. Position is a lookup in the Positions list.

For Position, same thing we renamed title to Position Title and Department is a Lookup in: Department list.

For Department, again same thing, we renamed title to Department Name.

Now you have the list we need, and all we need to do next is to generate the Entity reference code using SPMetal. You can find it in the installation of your Sharepoint in the folder C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN. Now to generate the Entities you need, you can run this command:

spmetal.exe /web:http://YourSharepointURL/YourTeamSite 
/namespace:YourProjectNamespace /code:FileNameYouWant.cs

Once it’s finished, you will see the file generated in the bin folder of Sharepoint. And now, you can add that to your project.

Now, you also need to add a reference to Microsoft.SharePoint.Linq.dll which is in C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\ISAPI folder.

Now you are ready to code. For this sample, we will use a GridView to show the data you need, so here is a sample.

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
    EnableModelValidation="True">
    <Columns>
        <asp:BoundField DataField="Title" HeaderText="Last Name"/>
        <asp:BoundField DataField="FirstName" HeaderText="First Name"/>
        <asp:BoundField DataField="Position" HeaderText="Position" />
        <asp:BoundField DataField="PositionDescription"
            HeaderText="Position Description" />
        <asp:BoundField DataField="Department" HeaderText="Department" />
    </Columns>
</asp:GridView>

And for the code behind, we use this:

using System;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Linq;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Linq;

namespace Demo.VisualWebPart1
{
    public partial class VisualWebPart1UserControl : UserControl
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                BindGrid();
            }
        }

        private void BindGrid()
        {
            SPLinqDataContext dc = new SPLinqDataContext(SPContext.Current.Web.Url);
            EntityList<EmployeesItem> Employees = dc.GetList<EmployeesItem>("Employees");

            var EmployeeQuery = from e in Employees.ToList()
                                select new
                                {
                                    e.Title,
                                    e.FirstName,
                                    Position = e.Position.Title,
                                    PositionDescription = e.Position.Description,
                                    Department = e.Position.Department.Title
                                };
            GridView1.DataSource = EmployeeQuery;
            GridView1.DataBind();
        }
    }
}

If you notice, we use .Title a lot, this is because when we reused and renamed the Title column that SharePoint defaulted to, Sharepoint still retains the name internally it is still referred to as Title instead of the renamed column name. And if you notice because of LINQ it's easy to drill down to the sub lists that the primary list is related to, so rather than creating a join to the other list, we just use this for example:

e.Position.Description

also you notice there is a lot of Title Columns, it's easy to create an alias for it by doing this:

Department = e.Position.Department.Title

and finally if you have been using LINQ for some time, you might ask why we used:

Employees.ToList()

rather than just using:

Employees

Then, here is a really good explanation about it -> http://blog.sharepointsite.co.uk/2010/06/inefficient-queries-spmetal-join-issue.html.

Happy coding!