Accessing Multiple SharePoint 2010 Lists by Using LINQ
Getting Started with Data Access in SharePoint 2010: Learn to create SharePoint 2010 Web Parts that query multiple lists by using LINQ.
Applies to: Office 2010 | SharePoint Foundation 2010 | SharePoint Server 2010 | Visual Studio 2010
In this article
Create a Visual Web Part Project
Generate LINQ to SharePoint Proxy Code
Add Code to Create the SharePoint Controls
Add the LINQ Queries to the Project
Test the Solution
Next Steps
Published: June 2010
Provided by: Frank Rice, Microsoft Corporation
Watch the video: Access Multiple SharePoint 2010 Lists by Using LINQ
In this exercise, you develop and deploy a visual Web Part that reads data from three lists by using LINQ queries and displays the results in a grid view control. The Web Part also contains a drop-down control that enables you to select which query is executed. To complete this task, you must do the following:
Create a Visual Web Part Project
Generate LINQ to SharePoint Proxy Code
Add Code to Create the SharePoint Controls
Add the LINQ Queries to the Project
Test the Solution
Create a Visual Web Part Project
In this task, you create a Visual Web Part project in Microsoft Visual Studio 2010.
Note
The following tasks assume that there are lists titled Employees, Projects, and Bugs on the default SharePoint website. Find instructions about how to create the Employees and Projects lists in Create Linked Lists in SharePoint 2010. Find instructions about how to create the Bugs list in Create Custom List Definitions in SharePoint 2010.
To create the Visual Web Part
Start Visual Studio 2010, click File, point to New, and then click Project.
Navigate to the Visual C# node in the Installed Templates section, click SharePoint, and then click 2010.
Select the Visual Web Part project template, provide a name (such as AccessMultipleListswithLINQ) and a location for your project, and then click OK.
In the What local site do you want to use for debugging drop-down list, select the site to use (such as https://localhost/sites/MySampleWebSite). Also select the Deploy as a farm solution option, and then click Finish.
Generate LINQ to SharePoint Proxy Code
In this task, you use the spmetal.exe code-generation utility to generate the LINQ to SharePoint proxy code. The tool generates entity class code that Visual Studio 2010 uses as an interface to obtain IntelliSense and to enable LINQ-based queries to be performed on SharePoint Server 2010 lists.
To generate the LINQ proxy code
In Solution Explorer, right-click AccessMultipleListswithLINQ, and then click Open Folder in Windows Explorer.
To open a command window in the current project directory, press and hold the SHIFT key, right-click the Explorer window, and then click Open command windows Here.
To set the path of the SharePoint Server 2010 folder, at the command prompt, type the following command, and then press ENTER:
set path=%path%;c:\program files\common files\microsoft shared\web server extensions\14\bin
To generate the LINQ to SharePoint proxy code, at the command prompt, type the following command, and then press ENTER..
Note
Replace the SharePoint site URL with your site’s URL
spmetal.exe /web:https://localhost/sites/MySampleWebSite /namespace:AccessMultipleListswithLINQ.VisualWebPart1 /code:SPLinq.cs
This command creates the SPLinq.cs file in the AccessMultipleListswithLINQ project folder.
Close the Command Prompt window.
Now add the file to the project. Switch back to the Visual Studio project screen.
In Solution Explorer, right-click AccessMultipleListswithLINQ, point to Add, and then click Existing Item.
From the Add Existing Item dialog window, select SPLinq.cs and then click Add.
In Solution Explorer, right-click References, and then click Add Reference.
Click the Browse tab and type C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\ISAPI in the File Name box.
Select Microsoft.SharePoint.Linq.dll, and then click OK.
Add Code to Create the SharePoint Controls
In this task, you add code to your solution that creates the visual Web Part controls.
To add controls to the project
In Solution Explorer, right-click VisualWebPart1.webpart and then click Open.
Change the value of the Title property to SPLinqDemoWebPart.
In Solution Explorer, expand VisualWebPart1, and then double-click VisualWebPart1UserControl.ascx. Visual Studio displays the HTML for the visual Web Part user control.
Add the following code after the existing code to construct the drop-down control and grid view control.
<asp:DropDownList runat="server" ID="dropDownList" AutoPostBack="true" OnSelectedIndexChanged="ChangeQuery"> <asp:ListItem Text="Employees" Value="Employees" /> <asp:ListItem Text="Projects" Value="Projects" /> <asp:ListItem Text="Bugs" Value="Bugs" /> </asp:DropDownList> <SharePoint:SPGridView ID="spGridView" runat="server" AutoGenerateColumns="false"> <HeaderStyle HorizontalAlign="Left" ForeColor="Navy" Font-Bold="true" /> <Columns> <SharePoint:SPBoundField DataField="Title" HeaderText=""> </SharePoint:SPBoundField> <SharePoint:SPBoundField DataField="JobTitle" HeaderText=""> </SharePoint:SPBoundField> <SharePoint:SPBoundField DataField="ProjectTitle" HeaderText=""> </SharePoint:SPBoundField> </Columns> </SharePoint:SPGridView>
In Solution Explorer, right-click VisualWebPart1UserControl.ascx, and then click View Code.
Add the following statements at the top of the code screen.
using Microsoft.SharePoint.Linq; using Microsoft.SharePoint; using System.Linq;
Add the following variables after the VisualWebPartUserControl class.
EntityList<ProjectsItem> Projects; EntityList<EmployeesItem> Employees; EntityList<BugsItem> Bugs;
Insert the following code inside the Page_Load method.
string spWebUrl = SPContext.Current.Web.Url; SPLinqDataContext dc = new SPLinqDataContext(spWebUrl); Projects = dc.GetList<ProjectsItem>("Projects"); Employees = dc.GetList<EmployeesItem>("Employees"); Bugs = dc.GetList<BugsItem>("Bugs"); dc.Dispose();
Add the LINQ Queries to the Project
In this task, you create LINQ queries that query the three SharePoint Server 2010 lists: Projects, Employees, and Bugs. To do this, you insert four methods into the project:
ChangeQuery
GetProjects
GetEmployees
GetBugs
To create the LINQ queries
Insert the following code after the Page_Load method.
public void ChangeQuery(object o, EventArgs e) { var selected = dropDownList.SelectedValue; switch (selected) { case "Projects": GetProjects(); break; case "Employees": GetEmployees(); break; case "Bugs": GetBugs(); break; default: break; } } private void GetBugs() { var bugsQuery = from bug in Bugs select new { Title = bug.Project, JobTitle = bug.Title, ProjectTitle = bug.AssignedTo }; spGridView.DataSource = bugsQuery; spGridView.DataBind(); } private void GetEmployees() { var empQuery = from emp in Employees where emp.Project.DueDate < DateTime.Now.AddMonths(6) select new { emp.Title, emp.JobTitle, ProjectTitle = emp.Project.Title }; spGridView.DataSource = empQuery; spGridView.DataBind(); } private void GetProjects() { var projQuery = from p in Projects where p.DueDate < DateTime.Now.AddMonths(6) select new { Title = p.Title, JobTitle = p.Description, ProjectTitle = p.DueDate }; spGridView.DataSource = projQuery; spGridView.DataBind(); }
By changing the value of the drop-down control, you change the query that is executed This changes the data displayed in the grid view control.
In Solution Explorer, right-click AccessMultipleListswithLINQ, and then click Deploy.
Test the Solution
In this task, you add the Web Part to the Home page, make selections in the drop-down control, and then see the results.
To test the solution
Open Internet Explorer to the web site specified in the project.
On the ribbon, click the Edit icon.
On the ribbon, under the Editing Tools tab, click Insert, and then click Web Part.
Under Categories, click Custom. SPLinqDemoWebPart should appear in the list of Web Parts.
Highlight SPLinqDemoWebPart and on the right-side of the page, click Add.
From the drop-down list, select Projects. The results of the selection should resemble Figure 1.
Figure 1. Results of selecting Projects from the drop-down list