Performing List Data Querying and Manipulation

Hour 7, “Understanding SharePoint 2010 Server Side Development,” looked at how to query and manipulate data in lists using the object model—the SPList and SPListItem class. While this is good for a simple scenario, this is not the best way to query and edit data in lists. This section shows other better and recommended ways to do the same.

Querying List Data Using CAML

CAML (Collaborative Application Markup Language) is an XML-based query language used in conjunction with the SPQuery and SPSiteDataQuery classes to perform data operations on SharePoint lists. While the SPQuery class is used to retrieve data from a specific list, the SPSiteDataQuery class is used to retrieve data from multiple lists across different sites in a particular site collection. To query data from a list using the SPQuery class, you need to specify the properties of an SPQuery object as shown in Table 11.3.

Table 11.3. CAML Query Properties

Consider a scenario where you have the following two lists in your SharePoint site:

• Departments—Having fields like Department ID, Department Name, and Department Location

• Employees—Having fields like Employee Number, First Name, Last Name, Designation, and Department ID (a lookup column to the Departments list), Department Name (linked to Department Name in the Departments list via the Department ID lookup column)

Department related information for an employee is present in the Departments list, which is linked to the Employees list via the Department ID column. In SharePoint, columns (such as Department ID) defining relationships between lists are of type lookup, and SharePoint provides you the option to select an existing column from the foreign list while defining a column of this type. For example, Figure 11.8 shows how the Department ID column should be defined in the Employees list.

Figure 11.8. Defining the Department ID lookup column in the Employees list

Now let’s use the SPQuery class and retrieve all employees from the Employees list having the designation of Manager. The following code helps achieve this:

// Create SPQuery object
SPQuery allManagersQuery = new SPQuery
{
    Query =
    "<Where>
       <Eq>
         <FieldRef Name='Designation' />
         <Value Type='Text'>Manager</Value>
       </Eq>
     </Where>",
    ViewFields =
    "<FieldRef Name='Last_x0020_Name' />
     <FieldRef Name='LinkTitle' />
     <FieldRef Name='Department_x0020_ID_x003a_Depart' />",
};

// Call GetItems method on Employees list passing SPQuery instance
SPListItemCollection results =
SPContext.Current.Web.Lists
["Employees"].GetItems(allManagersQuery);

if (results != null && results.Count != 0)
{
    // Enumerate through the result set and
    // display Last Name, First Name and
    // Department fields
    foreach (SPListItem listItem in results)
    {
        writer.Write("{0}  {1}  {2}<br/>",
         listItem["Last_x0020_Name"],
         listItem["LinkTitle"],
         listItem["Department_x0020_ID_x003a_Depart"]);
    }
}

The easiest way to build a CAML query for the preceding scenario is to create a view (on the Employees list) to get all managers with the filter criteria configured as shown in Figure 11.9.

Figure 11.9. Specifying filter criteria for the AllManagers view

Once the view is ready, use Visual Studio’s Server Explorer to connect to the SharePoint site and examine the view’s property. Copy the contents of the SchemaXml property to Notepad and extract the value of the Query and ViewFields elements, as shown in Figure 11.10. The value of these elements is used directly in the preceding code while creating a new instance of the SPQuery class.

Figure 11.10. Extracting contents of the Query and ViewFields elements from the AllManagers view’s schema definition

If you examine the contents of the AllManagersWebPart, you will notice that the lookup fields containing an employee’s department name, displays a number followed by semicolon and hash characters and finally the department name, as shown in Figure 11.11.

Figure 11.11. The Lookup field contains the Lookup ID and Lookup Value separated by “;#” characters

This is because the lookup fields store the Lookup ID along with the actual Lookup value, separated by “;#” characters. The SPFieldLookupValue class’s LookupValue property helps to obtain the actual lookup value. The following code demonstrates how to use this class to get only the department name from the lookup:

SPFieldLookupValue DepartmentLookup =
new SPFieldLookupValue(
listItem["Department_x0020_ID_x003a_Depart"].ToString());
string departmentName = DepartmentLookup.LookupValue;

Next, try to get all employees located in the north wing. Since the Department Location column is not a linked lookup column (via the Department ID field), you need to include a join predicate here to join the Employees and Department lists and apply the filter on the Department Location column to get the employees located in the North Wing. Make the following modifications to your SPQuery object:

SPQuery northWingEmployeesQuery = new SPQuery
{
    Query = @"<Where>
                    <Eq>
                       <FieldRef Name='EmployeeDepartmentLocation' />
                       <Value Type='Text'>North Wing</Value>
                    </Eq>
                </Where>",
    ViewFields = @"<FieldRef Name='Last_x0020_Name' />
                    <FieldRef Name='LinkTitle' />
                    <FieldRef Name='EmployeeDepartmentLocation' />",
    Joins = @"<Join Type='INNER' ListAlias='EmployeeDepartmentList'>
                    <Eq>
                        <FieldRef Name='Department_x0020_ID' RefType='Id' />
                        <FieldRef List='EmployeeDepartmentList' Name='ID' />
                    </Eq>
                </Join>",
    ProjectedFields = "<Field Name=' EmployeeDepartmentLocation' Type='Lookup' List='EmployeeDepartmentList' ShowField='Department_x0020_Location' />"
};

First, understand the Joins property in the preceding code. We specify here that we want an Inner Join with the Department ID (internal name Department_x0020_ID) being the join field, and we assign the EmployeeDepartmentList alias to the join result. The ProjectedFields property helps to specify (and assign an alias as well) the columns to retrieve from the foreign list. We retrieve DepartmentLocation in this case and call it EmployeeDepartmentLocation. Once the EmployeeDepartmentLocation field is defined as a projected field, it can be used in the Query to filter the list items having the department name of North Wing. Further, because you want to display the department’s location in the result, this field is included in the ViewFields property as well.

The rest of the procedure of obtaining the results remains the same, as in the case of the all managers query. Refer to the NorthWingEmployeesWebPart in the DataAccessDemo project in the sample code for this hour for a complete listing of the code.

Now look at the usage of the SPSiteDataQuery class, which is used to retrieve data from multiple lists across different sites in a particular site collection. Retrieve the value of title field for all the list items across all the generic lists in the current site collection. The following code represents the SPSiteDataQuery object for this operation:

// Create SPSiteDataQuery object
SPSiteDataQuery siteDataQuery = new SPSiteDataQuery
{
    ViewFields = @"<FieldRef Name='Title'/>",
    Lists = @"<Lists BaseType='0' />",
    Webs = "<Webs Scope='SiteCollection' />",
    RowLimit = 10
};

// Call GetSiteData method on the current web
DataTable results = SPContext.Current.Web.GetSiteData(siteDataQuery);

Further, you can also restrict your results to lists of a particular server template by specifying a value for the ServerTemplate attribute.

Using the Webs property, you can restrict the scope of your query in the following manner:

• <Webs Scope='SiteCollection' /> searches the entire site collection.

• <Webs Scope='Recursive' /> searches the current web and its child webs recursively.

SPSiteDataQuery does not support Joins and projected fields. Refer to the AllListTitlesWebPart in the code samples for this hour for complete source code of this example.

Querying List Data Using LINQ

CAML takes some amount of time to gain expertise in. Debugging CAML is not easy, and often you get cryptic error messages that are difficult to interpret. Language-Integrated Query (LINQ) to SharePoint offers an alternative. However, you cannot completely do away with CAML as it is still the most appropriate choice in certain scenarios. Even internally CAML queries are generated corresponding to the LINQ statements, which ultimately perform the specified data operations.

LINQ provides a SQL-like syntax that can be used to query data. It operates against strongly typed entities that represent the items in your lists. These entities can be generated with the help of a command-line tool, shipped with SharePoint, called SPMetal.

---

Try It Yourself: Write LINQ to Query Data

Let’s look at the approach to get employees located in the North Wing, using LINQ to SQL. Earlier you retrieved the related records using CAML in the NorthWingEmployees WebPart.

1. To start using LINQ to SharePoint, add a reference to the Microsoft.SharePoint.Linq.dll located in the 14ISAPI directory and include the following using statements in your code:

using System.Linq;
using Microsoft.SharePoint.Linq;

2. Next create entities for the lists you want to manipulate with LINQ. You can use SPMetal.exe, located in the 14BIN folder, to generate these.

SPMetal /web:http://SiteUrl /code:C:EntityFileName.cs /language:csharp

3. At this point you are ready to write your LINQ query and query the results. The following code demonstrates exactly how to do this:

// Create Data Context object
using (HumanResourceEntitiesDataContext dataContext =
    new HumanResourceEntitiesDataContext("http://YourSiteUrl"))
{

// LINQ query to get employees located in North Wing
var results = from employee in dataContext.Employees.ToList()
                join department in dataContext.Departments
                on employee.DepartmentID.DepartmentID
                equals department.DepartmentID
                where department.DepartmentLocation == "North Wing"
                select new
                {
                    employee.LastName,
                    employee.Title,
                    department.DepartmentLocation
                };

if (results != null && results.Count() != 0)
{
    // Enumerate through the result set and
    // display Last Name, First Name and
    // Employee Location
    foreach (var resultItem in results)
    {
        writer.Write("{0}  {1}  {2}<br/>",
                        resultItem.LastName,
                        resultItem.Title,
                        resultItem.DepartmentLocation);
    }
}
}

The preceding LINQ query joins the Employees and Departments entities on the Department ID field. Next you specify filter criteria in the where statement to get the employees at the North Wing location. Finally, you select the employee’s last name, first name (which is the title field internally), and the department location.

Because LINQ to SharePoint operates against strongly typed entities (generated using SPMetal.exe) you get a good amount of IntelliSense support from Visual Studio while writing LINQ queries (see Figure 11.12).

Figure 11.12. Visual Studio provides IntelliSense support for LINQ queries.

Querying lists using LINQ is much easier than writing CAML queries directly. However, at certain times, when you have complex LINQ queries, you may want to ensure that your LINQ expressions translate into efficient CAML queries. In such cases it can be useful to review a CAML query generated by the LINQ to SharePoint provider. To do so, you can use the Log property of the DataContext to specify a TextWriter object and log the results to a text file or event log, as illustrated in the following code:

// Create Data Context object
using (HumanResourceEntitiesDataContext dataContext =
    new HumanResourceEntitiesDataContext("http://YourSiteUrl"))
{
    // Specify TextWriter object instance to the DataContext class
    StringBuilder sb = new StringBuilder();
    StringWriter stringWriter = new StringWriter(sb);
    dataContext.Log = stringWriter;

    // Your CAML query goes here
    // ............
    // ............

    // Write the generated CAML to event log
    EventLog.WriteEntry("CAML Query", sb.ToString());
}

---