How to use LINQ in SharePoint 2010

What is LINQ and why it’s useful in LINQ

LINQ is Language integrated Query Language. It’s part of .Net framework, you can easily integrate this with SharePoint 2010. It’s used to Query on Data sources such as Object, Datasets, SQL, XML and other entities. LINQ provide access to sharepoint in TYPED Manner, you can easily access List columns name as property. As syntax is same as it is for .net development.

var items = from s in DataContext.Students

           select s;

SPMetal

SPMetal is tool provided by sharepoint , used to created entity model for sharepoint site objects. You can write these classes manually as well, but its very time consuming task. It will create partial class at specified location, you can simply add these classes in your project.

Simple CRUD operation using LINQ for SharePoint List

Step 1: Create List

Create a simple list in sharepoint site .i have created Custom List with the name of students(Title, firstname,lastname, Country).

Step 2:  Generate Entity model.

Open command line -> start -> type cmd in search box -> open Command Line.

Type CD C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN

This is path for SPMetal.exe

Now in command line type   SPMetal.exe /web: http://sp2010:1000 /code: C:/MyEntities.cs

Note : just put your web url instead of  http://sp2010:1000 

It will create entities classes for you. Go to given path in command to verify.

Step 3: Create Visual WebPart.

Open Visual studio 2010 and Create new sharepoint 2010 visual webpart project,

File -> New -> Project -> Sharepoint - >2010  -> Visual web part . Give its appropriate name and location and press OK.

 Visual webpart work as form solution so next you have to verify your site url and press ok.

 Visual studio load its project structure.

Now you have to add reference for LINQ.

Right Click on Project (Solution Explorer)  -> Add Reference -> Select Microsoft.Sharepoint.LINQ

In code Behind of Webpart, add following Namespaces

using System.Linq;

using Microsoft.SharePoint.Linq;

using Microsoft.SharePoint;

In visual web part  you can simple trag and drop control to perform your operation.

After creating form design you have to move in code behind . (.CS file of webpart)

I have  written these four methods to perform CRUD Operation

        #region CRUD Operation using LINQ

        /// <summary>

        /// Delete the student

        /// </summary>

        /// <param name="objTitle"></param>

        public void DeleteStudent(string objTitle)

        {

            try

            {

                using (MyEntitiesDataContext dc = new MyEntitiesDataContext(SPContext.Current.Site.Url))

                {

                    EntityList<StudentsItem> listItems = dc.GetList<StudentsItem>("Students");

                    var updateobj = (from c in listItems

                                     where c.Firstname == objTitle

                                     select c).First();

                        dc.Students.DeleteOnSubmit(updateobj);

                    //Submit the changes

                    dc.SubmitChanges();

                }

            }

            catch (Exception ex)

            {

                Response.Write(ex.Message);

            }

        }

        /// <summary>

        /// Insert Student

        /// </summary>

        public void InsertStudent()

        {

            try

            {

                using (MyEntitiesDataContext dc = new MyEntitiesDataContext("http://sp2010:1000/"))

                {

                    EntityList<StudentsItem> listItems = dc.GetList<StudentsItem>("Students");

                    StudentsItem objnew = new StudentsItem()

                    {

                        Firstname = txtFname.Text.Trim(),

                        Lastname = txtLname.Text.Trim(),

                        Country = Country.USA,

                        Title = txtTitle.Text.Trim()

                    };// Insert the new list item to the list

      dc.Students.InsertOnSubmit(objnew);

                    dc.SubmitChanges();

                }

            }

           

            catch (Exception ex)

            {

               

                throw;

            }

        }

        /// <summary>

        /// Update items

        /// </summary>

        /// <param name="objTitle"> title for the Item</param>

        public void UpdateStudent(string objTitle)

        {

            try

            {

                using (MyEntitiesDataContext dc = new MyEntitiesDataContext("http://sp2010:1000/"))

                {

                    EntityList<StudentsItem> listItems = dc.GetList<StudentsItem>("Students");

                    StudentsItem updateobj = (from c in listItems

                                    where c.Id == 4

                                    select c).First();

                                  updateobj.Lastname = "Item2";

                    updateobj.Firstname = "Item2";

                    txtLname.Text = updateobj.Id.ToString();

                

                    dc.Students.InsertOnSubmit(updateobj);

                    dc.SubmitChanges();

}

            }

            catch (Exception ex)

            {

                Response.Write(ex.Message + "</br>");

            }

        }

        #endregion

I have Created one Grid View to Show all the students. So in in Page Load I have just added data source to Gridview.  You can see one more method name as Register Events() , I have used it to register all events (such as I am using 2 buttons (btnsave and btndelete))

  protected void Page_Load(object sender, EventArgs e)

        {

            using (MyEntitiesDataContext dc = new MyEntitiesDataContext(SPContext.Current.Site.Url))

            {

                var items = from s in dc.Students

                            select s;

                GridView1.DataSource = items;

                GridView1.DataBind();

               

            }

            RegisterEvents();

        }

        

        public void RegisterEvents()

        {

            btnSave.Click += new EventHandler(btnSave_Click);

            btnDelete.Click += new EventHandler(btnDelete_Click);

        }

     

Visual Web part Designer Code,

<%@ Assembly Name="$SharePoint.Project.AssemblyFullName$" %>

<%@ Assembly Name="Microsoft.Web.CommandUI, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

<%@ Register Tagprefix="SharePoint" Namespace="Microsoft.SharePoint.WebControls" Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

<%@ Register Tagprefix="Utilities" Namespace="Microsoft.SharePoint.Utilities" Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

<%@ Register Tagprefix="asp" Namespace="System.Web.UI" Assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" %>

<%@ Import Namespace="Microsoft.SharePoint" %>

<%@ Register Tagprefix="WebPartPages" Namespace="Microsoft.SharePoint.WebPartPages" Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

<%@ Control Language="C#" AutoEventWireup="true" CodeBehind="StudentdropDownUserControl.ascx.cs" Inherits="LINQ_Visual_Web_Part.StudentdropDown.StudentdropDownUserControl" %>

<table class="style1">

    <tr>

        <td>

            Title</td>

        <td>

            <asp:TextBox ID="txtTitle" runat="server"></asp:TextBox>

        </td>

    </tr>

    <tr>

        <td>

            first Name</td>

        <td>

            <asp:TextBox ID="txtFname" runat="server" ></asp:TextBox>

        </td>

    </tr>

    <tr>

        <td>

            Last Name</td>

        <td>

            <asp:TextBox ID="txtLname" runat="server"></asp:TextBox>

        </td>

    </tr>

    <tr>

        <td>

            Country</td>

        <td>

            <asp:DropDownList ID="DropDownList1" runat="server">

            </asp:DropDownList>

        </td>

    </tr>

    <tr>

        <td>

            &nbsp;</td>

        <td>

            <asp:Button ID="btnSave" runat="server" Text="Save" Width="123px" />

        </td>

    </tr>

    <tr>

        <td>

            &nbsp;</td>

        <td>

            <asp:Button ID="btnDelete" runat="server" Text="Delete" />

        </td>

    </tr>

</table>

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"

    BackColor="White" BorderColor="#999999" BorderStyle="Solid" BorderWidth="1px"

    CellPadding="3" EnableModelValidation="True" ForeColor="Black"

    GridLines="Vertical" >

    <AlternatingRowStyle BackColor="#CCCCCC"  CssClass=”alternateItemStyle”/>

    <Columns>

        <asp:BoundField DataField="Title" HeaderText="Title" />

        <asp:BoundField DataField="firstname" HeaderText="First name" />

        <asp:BoundField DataField="Lastname" HeaderText="Last name" />

    </Columns>

    <FooterStyle BackColor="#CCCCCC" />

    <HeaderStyle BackColor="Black" CssClass=”headerStyle” Font-Bold="True" ForeColor="White" />

    <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />

    <SelectedRowStyle BackColor="#000099" Font-Bold="True" ForeColor="White" />

</asp:GridView>

Deploy to sharepoint.

Build the project to verify that you don’t have any syntax error.

If you have sharepoint on your Visual studio machine then you can simply

Right Click on Project -> Deploy. Otherwise select package option it will create .wsp solution file ,

Adding webpart in sharepoint

Go to any page (must have webpart zone option) -> Add Webpart -> Choose Custom Category -> your newly born webpart will be there, select this webpart. Now you can test functionality.y