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 developers 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 this 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 FilesCommon FilesMicrosoft SharedWeb Server Extensions14BIN.  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 FilesCommon FilesMicrosoft SharedWeb Server Extensions14ISAPI 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 becuase of LINQ its 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 Colums, its 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!