Accessing Multiple SharePoint 2010 Lists by Using LINQ

SharePoint QuickStart Banner

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

  1. Start Visual Studio 2010, click File, point to New, and then click Project.

  2. Navigate to the Visual C# node in the Installed Templates section, click SharePoint, and then click 2010.

  3. Select the Visual Web Part project template, provide a name (such as AccessMultipleListswithLINQ) and a location for your project, and then click OK.

  4. 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

  1. In Solution Explorer, right-click AccessMultipleListswithLINQ, and then click Open Folder in Windows Explorer.

  2. 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.

  3. 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

  4. 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.

  5. Close the Command Prompt window.

  6. Now add the file to the project. Switch back to the Visual Studio project screen.

  7. In Solution Explorer, right-click AccessMultipleListswithLINQ, point to Add, and then click Existing Item.

  8. From the Add Existing Item dialog window, select SPLinq.cs and then click Add.

  9. In Solution Explorer, right-click References, and then click Add Reference.

  10. Click the Browse tab and type C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\ISAPI in the File Name box.

  11. 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

  1. In Solution Explorer, right-click VisualWebPart1.webpart and then click Open.

  2. Change the value of the Title property to SPLinqDemoWebPart.

  3. In Solution Explorer, expand VisualWebPart1, and then double-click VisualWebPart1UserControl.ascx. Visual Studio displays the HTML for the visual Web Part user control.

  4. 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>
    
  5. In Solution Explorer, right-click VisualWebPart1UserControl.ascx, and then click View Code.

  6. Add the following statements at the top of the code screen.

    using Microsoft.SharePoint.Linq; 
    using Microsoft.SharePoint; 
    using System.Linq;
    
  7. Add the following variables after the VisualWebPartUserControl class.

    EntityList<ProjectsItem> Projects;
    EntityList<EmployeesItem> Employees;
    EntityList<BugsItem> Bugs;
    
  8. 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

  1. 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.

  2. 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

  1. Open Internet Explorer to the web site specified in the project.

  2. On the ribbon, click the Edit icon.

  3. On the ribbon, under the Editing Tools tab, click Insert, and then click Web Part.

  4. Under Categories, click Custom. SPLinqDemoWebPart should appear in the list of Web Parts.

  5. Highlight SPLinqDemoWebPart and on the right-side of the page, click Add.

  6. 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

    Results of selecting projects from the list

Next Steps