Hands-On Lab

Lab 05: LINQ to SharePoint

Lab version: 1.0.0

Last updated:1/10/2019

Contents

Overview

Exercise 1: Creating List Data

Exercise 2: Creating Entities using the SPMetal Utility

Exercise 3: Creating a Web Part that uses LINQ

Overview

Lab Time: 45 minutes

Lab Folder: C:\Student\Labs\05_LINQ

Lab Overview: LINQ to SharePoint is a technology for querying SharePoint lists that relieves the developer from having to write CAML queries. In this lab, you will be making use of the new support for LINQ in SharePoint. In the first exercise, you will be creating lists for use with LINQ.In the second exercise you will use the SPMETAL utility to create Entities. In the final exercise you will create a web part for accessing the list data using LINQ.

Note:Lab Setup Requirements

Before you begin this lab, you must run the batch file named SetupLab05.bat. This batch file creates a new SharePoint site collection at the location

Exercise 1: Creating List Data

In this exercise you will create a feature to provision lists. Because LINQ code is tied to specific list schemas, your solutions will often contain a list-provisioning component.

  1. If you haven’t already done so, run the batch file named SetupLab05.bat, found in the c:\Student\Labs\05_LINQ\ folder, to create the new site collection that will be used to test and debug the code you will be writing in this lab. This batch file creates a new site collection at an URL of . (Be sure to check the output of the batch file to verify the site URL)
  2. Launch Internet Explorer and navigate to the top-level site at . Take a moment to inspect the site and make sure it behaves as expected. Note that the setup script creates a new site collection with a Team site as its top-level site.
  3. Launch Visual Studio 2010 and create a new project by selecting File » New » Project.
  4. Expand nodes to SharePoint » 2010 and select Empty Project.
  5. Name the new project LINQLists and click the OK button to create the new project.
  6. In the SharePoint Customization Wizard, enter as the target debugging site.
  7. Select the option to Deploy as farmsolution and click the Finish button.
  8. When the new project is created, right click the Features node and choose Add Feature.
  9. Right-click the new feature and select Add Event Receiver to add a Feature Receiver.
  10. Open the Feature1EventReceiver.cs/vb file in Visual Studio for editing.
    (Note: in VB.NET you may have to click on the Show All Filesbutton in the Solution Explorer. This file is located underneath the Features/Feature1/Feature1.feature location).
  11. Add the following code to the feature receiver class to help with the creation of fields in the lists

C#

using System;

using System.Runtime.InteropServices;

using System.Security.Permissions;

using Microsoft.SharePoint;

using Microsoft.SharePoint.Security;

namespace LINQLists.Features.Feature1

{

[Guid("a5bcd625-e0b9-4126-b544-89b478334be0")]

public class Feature1EventReceiver : SPFeatureReceiver

{

private void FixupField(SPListspList, string fieldInternalName)

{

FixupField(spList.Fields.GetFieldByInternalName(fieldInternalName));

}

private void FixupField(SPFieldspField)

{

// This method takes an InternalName of a field in a spList

// and process a few things we want all fields to have by default

// for example setting them to show into the default view

spField.ShowInDisplayForm = true;

spField.ShowInEditForm = true;

spField.ShowInNewForm = true;

spField.ShowInListSettings = true;

spField.ShowInVersionHistory = true;

spField.ShowInViewForms = true;

// Add field to default view

SPViewdefaultView = spField.ParentList.DefaultView;

defaultView.ViewFields.Add(spField);

defaultView.Update();

spField.Update();

}

VB.NET

GuidAttribute("4c02d9ec-6e77-4922-a3bf-594fd4b70bc9")> _

Public Class Feature1EventReceiver

Inherits SPFeatureReceiver

Private Sub FixupField(ByValspList As SPList,ByValfieldInternalName As String)

FixupField(spList.Fields.GetFieldByInternalName(fieldInternalName))

End Sub

Private Sub FixupField(ByValspField As SPField)

' This method takes an InternalName of a field in a spList

' and process a few things we want all fields to have by default

' for example setting them to show into the default view

spField.ShowInDisplayForm = True

spField.ShowInEditForm = True

spField.ShowInNewForm = True

spField.ShowInListSettings = True

spField.ShowInVersionHistory = True

spField.ShowInViewForms = True

' Add field to default view

Dim defaultView As SPView = spField.ParentList.DefaultView

defaultView.ViewFields.Add(spField)

defaultView.Update()

spField.Update()

End Sub

' Commented out area removed for brevity sake in this display.

End Class

  1. Locate and uncomment the FeatureActivated method. This is the method that will run when your feature is activated in a SharePoint site.

Add the following code to the FeatureActivated method to build a number of lists.

C#

public override void FeatureActivated(SPFeatureReceiverProperties properties)

{

using (SPWebspWeb = (SPWeb)properties.Feature.Parent)

{

//Projects List

GuidpListGuid = spWeb.Lists.Add("Projects", "Company Projects",SPListTemplateType.GenericList);

spWeb.Update();

//Projects List columns

SPListpList = spWeb.Lists[pListGuid];

pList.OnQuickLaunch = true;

SPFieldpTitleIDField = pList.Fields["Title"];

FixupField(pList, pList.Fields.Add("Description", SPFieldType.Text, false));

FixupField(pList, pList.Fields.Add("Due Date", SPFieldType.DateTime, false));

SPFieldDateTimedueDateField =(SPFieldDateTime)pList.Fields["Due Date"];

dueDateField.DisplayFormat = SPDateTimeFieldFormatType.DateOnly;

dueDateField.Update();

pList.Update();

// Employees List

GuideListGuid = spWeb.Lists.Add("Employees", "Employees",SPListTemplateType.GenericList);

spWeb.Update();

// Employees List columns

SPListeList = spWeb.Lists[eListGuid];

eList.OnQuickLaunch = true;

SPFieldtitleIDField = eList.Fields["Title"];

titleIDField.Title = "Fullname";

titleIDField.Update();

FixupField(eList, eList.Fields.Add("JobTitle", SPFieldType.Text, false));

FixupField(eList, eList.Fields.Add("Team", SPFieldType.Text, false));

FixupField(eList, eList.Fields.Add("Contribution (in Milestones)", SPFieldType.Number, false));

string projectFieldInternalName = eList.Fields.AddLookup("Project",pListGuid, false);

SPFieldLookupprojectField =

(SPFieldLookup)eList.Fields.GetFieldByInternalName(projectFieldInternalName);

projectField.LookupField = pTitleIDField.InternalName;

FixupField(projectField);

eList.Update();

// Project Manager field (Project to Employee lookup)

string employeeFieldInternalName = pList.Fields.AddLookup("Primary Contact", eListGuid, false);

SPFieldLookupmanagerField =

(SPFieldLookup)pList.Fields.GetFieldByInternalName(employeeFieldInternalName);

managerField.LookupField = titleIDField.InternalName;

FixupField(managerField);

pList.Update();

}

}

VB.NET

Public Overrides Sub FeatureActivated(ByVal properties As SPFeatureReceiverProperties)

Using spWeb As SPWeb = DirectCast(properties.Feature.Parent, SPWeb)

'Projects List

Dim pListGuid As Guid = spWeb.Lists.Add("Projects", "Company Projects", SPListTemplateType.GenericList)

spWeb.Update()

'Projects List columns

Dim pList As SPList = spWeb.Lists(pListGuid)

pList.OnQuickLaunch = True

Dim pTitleIDField As SPField = pList.Fields("Title")

FixupField(pList, pList.Fields.Add("Description",SPFieldType.Text, False))

FixupField(pList, pList.Fields.Add("Due Date",SPFieldType.DateTime, False))

Dim dueDateField As SPFieldDateTime = DirectCast(pList.Fields("Due Date"), SPFieldDateTime)

dueDateField.DisplayFormat = SPDateTimeFieldFormatType.DateOnly

dueDateField.Update()

pList.Update()

' Employees List

Dim eListGuid As Guid = spWeb.Lists.Add("Employees", "Employees",SPListTemplateType.GenericList)

spWeb.Update()

' Employees List columns

Dim eList As SPList = spWeb.Lists(eListGuid)

eList.OnQuickLaunch = True

Dim titleIDField As SPField = eList.Fields("Title")

titleIDField.Title = "Fullname"

titleIDField.Update()

FixupField(eList, eList.Fields.Add("JobTitle",SPFieldType.Text, False))

FixupField(eList, eList.Fields.Add("Team", SPFieldType.Text, False))

FixupField(eList, eList.Fields.Add("Contribution (in Milestones)",SPFieldType.Number, False))

Dim projectFieldInternalName As String =eList.Fields.AddLookup("Project", pListGuid, False)

Dim projectField As SPFieldLookup = DirectCast(eList.Fields.GetFieldByInternalName(projectFieldInternalName),SPFieldLookup)

projectField.LookupField = pTitleIDField.InternalName

FixupField(projectField)

eList.Update()

' Project Manager field (Project to Employee lookup)

Dim employeeFieldInternalName As String = pList.Fields.AddLookup("Primary Contact", eListGuid, False)

Dim managerField As SPFieldLookup = DirectCast(pList.Fields.GetFieldByInternalName(employeeFieldInternalName),

SPFieldLookup)

managerField.LookupField = titleIDField.InternalName

FixupField(managerField)

pList.Update()

End Using

End Sub

  1. Locate and uncomment the FeatureDeactivating method. This is the method that will run when your feature is being deactivated in a SharePoint site.
  2. Add the following code to the FeatureDectivating method to tear down the lists.

C#

public override void FeatureDeactivating(SPFeatureReceiverProperties properties)

{

using (SPWebspWeb = (SPWeb)properties.Feature.Parent)

{

SPListempList = spWeb.Lists["Employees"];

empList.Delete();

spWeb.Update();

SPListprojList = spWeb.Lists["Projects"];

projList.Delete();

spWeb.Update();

}

}

Visual Basic

Public Overrides Sub FeatureDeactivating(ByVal properties As _

SPFeatureReceiverProperties)

Using spWeb As SPWeb = DirectCast(properties.Feature.Parent, SPWeb)

Dim empList As SPList = spWeb.Lists("Employees")

empList.Delete()

spWeb.Update()

Dim projList As SPList = spWeb.Lists("Projects")

projList.Delete()

spWeb.Update()

End Using

End Sub

  1. Build the project and verify that the code is correct; fixing any errors that may be reported by the compiler.
  2. Once the project is completed, select Debug » Start Without Debugging from the Visual Studio main menu or use the shortcut key combination of or by pressing [CTRL]+[F5]. Note that when you run the project from Visual Studio, the project is built, packaged, deployed, and features are activated automatically. After the project runs, your browser will open to the test site specified at the beginning of the exercise.
  3. When the test site opens, select Site Actions » Site Settings.
  4. On the Site Settings page, under the Site Actions section select Manage site features.
  5. On the Site Features page, verify that the LINQLists Feature1 is activated.

Figure 1

The Site Features page

  1. After the feature is activated, you will see two new lists on the Quick Launch bar: Projects and Employees. Add some names to the Employees list and then add some items to the Projects list. The Projects list and Employees list have mutual lookups, so you’ll want to flip back and forth between the lists to add items.

Note:In this exercise you created two lists and added some data to them for future exercises.

Exercise 2: Creating Entities using the SPMetal Utility

In this exercise you will create entities for use with LINQ. Entity creation is done by using the command line utility SPMetal.

  1. Open a command window and navigate to the following path.

C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\bin\

  1. At the command prompt, execute the following command to build a set of entity classes.

C#

For C# use:

SPMetal /web: /code:Entities.cs /language:csharp

Visual Basic

For VB.NET use:

SPMetal /web: /code:Entities.vb /language:vb

  1. Locate the file named Entities.cs that was created in the previous step. By default the file will be placed in the same location where SPMetal was executed (in this case, the SharePoint ’14’ bin folder. )
  2. Move this file from this location to the lab location: c:\Student\Labs\Lab05_LINQ for use in the next Exercise.
  3. Open the file using Notepad or Visual Studio and examine the code inside that was automatically generated by the SPMetal utility. This file will be used in the next exercise to leverage the new SharePoint LINQ capabilities.

Note:In this exercise you used the SPMetal utility to create a class that is used as the underlying source for SharePoint LINQ queries.

Exercise 3: Creating a Web Part that uses LINQ

In this exercise you will create a web part that queries the lists you created earlier. The web part will create a view of the lists.

  1. In the Visual Studio 2010, create a new Visual Web Part project named LINQListsPart.

Figure 2

Create a Visual Web Part project. VB.NET shown, C# similar.

  1. After creating the project, the SharePoint Customization Wizard will appear. Enter as the test URL and click the Finish button.
  2. Add the Entities.cs/vb file you created in the previous exercise to the project by right-clicking the project and selecting Add » Existing Item.
  3. Open the Entities.cs/vb file and scroll through the classes. You’ll notice that a DataContext has been defined along with classes for all of the list content types in your site. Ignore all the code errors in the file… these are appearing because the project is missing a needed reference.
  4. To work with LINQ in SharePoint, you need to add a reference to a new assembly. Do this with the following steps:
  5. Select Project » Add Reference… from the Visual Studio main menu.
  6. In the Add Reference dialog, add Microsoft.SharePoint.Linq from the .NET tab.
    (Note: if you have any trouble finding it in the .NET tab you may also use the Browse tab to find this atc:\Program Files\Common Files\Microsoft Shared\web server extensions\14\ISAPI and select Microsoft.SharePoint.Linq.dll. Then click the OK button.)
  7. Add a Literal control named display to the design surface of VisualWebPart1UserControl.ascx.

XAML

asp:Literal ID=”Display” runat=”server” />

  1. Add the following statement to the top of the VisualWebPart1UserControl.ascx.cs/vb file to reference the necessary assemblies:
    (Note: in VB.NET you may have to click on the Show All Filesbutton in Solution Explorer.)

C#

using System;

using System.Linq;

using System.Text;

using Microsoft.SharePoint;

using Microsoft.SharePoint.Linq;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

Visual Basic

Imports System

Imports System.Linq

Imports System.Text

Imports Microsoft.SharePoint

Imports Microsoft.SharePoint.Linq

Imports System.Web.UI

Imports System.Web.UI.WebControls

Imports System.Web.UI.WebControls.WebParts

  1. Add the following code to the Page_Load method of the Web Part that will query the SharePoint site using LINQ thanks to the Entities.cs/vb code file you generated using SPMetal:

C#

protected void Page_Load(object sender, EventArgs e)

{

StringBuilder writer = new StringBuilder();

try

{

using (EntitiesDataContext dc = new EntitiesDataContext("

{

//Query Expressions

var q = from emp in dc.Employees

where emp.Project.DueDateDateTime.Now.AddYears(5)

orderbyemp.Project.DueDate

select new { emp.Title,Contact = emp.Project.PrimaryContact.Title };

writer.Append("<table border=\"1\" cellpadding=\"3\" cellspacing=\"3\">");

foreach (var employee in q)

{

writer.Append("<tr<td>");

writer.Append(employee.Title);

writer.Append("</td<td>");

writer.Append(employee.Contact);

writer.Append("</td</tr>");

}

}

}

catch (Exception x)

{

writer.Append("<tr<td>");

writer.Append(x.Message);

writer.Append("</td</tr>");

}

finally

{

writer.Append("</table>");

display.Text = writer.ToString();

}

}

VB.NET

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) _

Handles Me.Load

Dim writer As New StringBuilder()

Try

Using dc As New EntitiesDataContext( _

"

'Query Expressions

Dim q = From emp In dc.Employees _

Where emp.Project.DueDateDateTime.Now.AddYears(5) _

Order By emp.Project.DueDate _

Select New With { _

emp.Title, _

.Contact = emp.Project.PrimaryContact.Title _

}

writer.Append("<table border=""1"" cellpadding=""3"" cellspacing=""3"">")

For Each employee In q

writer.Append("<tr<td>")

writer.Append(employee.Title)

writer.Append("</td<td>")

writer.Append(employee.Contact)

writer.Append("</td</tr>")

Next

End Using

Catch x As Exception

writer.Append("<tr<td>")

writer.Append(x.Message)

writer.Append("</td</tr>")

Finally

writer.Append("</table>")

Display.Text = writer.ToString()

End Try

End Sub

  1. Build the project and verify that the code compiles.
  2. Set a breakpoint in the Page_Load method and select Debug » Start Debugging.
  3. The Visual Studio debugger should launch a browser and navigate to the site.
  4. Put the home page in edit mode by selecting Site Actions » Edit Page.
  5. Select the Rich Contentarea (Left web part zone, welcome text at the top). Insert the new Web Part by clicking the Insert tab in the Page Tools tab group on the ribbon and then clicking the Web Part button.
  6. Your new Web Part will be in the Custom category. Locate the Web Part VisualWebPart1, select it and then click the Add button.
  7. When the Web Part is added to the page, your breakpoint should be hit. Step through the code (F11) and verify that it is working correctly.

Note:In this exercise you created a visual Web Part that used SharePoint LINQ to query a list and display the results.