共用方式為

Using Query Classes

  • 發行項

Before the advent of LINQ to SharePoint, the SPQuery and SPSiteDataQuery classes were the predominant approaches to performing data operations on SharePoint lists. There are still many scenarios in which these classes provide the most effective approach—and, in some cases, the only viable approach—to data access.

Both the SPQuery class and the SPSiteDataQuery class allow you to construct a query using collaborative application markup language (CAML). You can then use the SPList object model to execute your query against one or more SharePoint lists. This topic explains when you should use each class and describes the considerations that should guide your implementations.

Using SPQuery

The SPQuery class is used to retrieve data from a specific list. In most cases, you should use LINQ to SharePoint, instead of the SPQuery class, to perform data operations on lists. However, there are still some circumstances in which SPQuery is the most appropriate option—or the only option—for data access. Most notably, using the SPQuery class is the only supported server object model approach for programmatically working with data in external lists.

In SharePoint 2010, the SPQuery class has been extended to allow you to specify joins and projected fields. The high-level process for using the SPQuery class is as follows:

  • Create an SPQuery instance.
  • Set properties on the SPQuery instance to specify the CAML query, along with various additional query parameters as required.
  • Call the GetItems method on an SPList instance, passing in the SPQuery instance as a parameter.

The following code example shows this.

SPListItemCollection results;

var query = new SPQuery
{
  Query = "[Your CAML query statement]",
  ViewFields = "[Your CAML FieldRef elements]",
  Joins = "[Your CAML Joins element]",
  ProjectedFields = "[Your CAML ProjectsFields element]"
};

results = SPContext.Current.Web.Lists["ListInstance"].GetItems(query);

This chapter does not provide instructions on how to configure SPQuery instances, because this is well covered by the product documentation. However, the following are brief summaries of the key properties of interest:

  • The Query property specifies the CAML query that you want to execute against the list instance.
  • The ViewFields property specifies the columns that you want your queries to return as CAML FieldRef elements.
  • The Joins property specifies the join predicates for your query as a CAML Joins element.
  • The ProjectedFields property defines fields from foreign joined lists as a CAML ProjectedFields element. This allows you to reference these fields in your ViewFields property and in your query statement.

Using SPQuery with Regular SharePoint Lists

You should consider using the SPQuery class, instead of LINQ to SharePoint, in the following scenarios:

  • When you have anonymous users on your site. LINQ to SharePoint does not support anonymous user access.

    Note

    This limitation exists at the time of publication. However, it may be resolved in future service packs or cumulative updates.

  • When a lookup column in a list refers to a list in another site within the site collection. In this situation, SPQuery allows you to use a join predicate that spans both sites. Although you can use LINQ to SharePoint to query across sites with some additional configuration, the process required to generate entity classes is more complex. By default, LINQ to SharePoint returns only the ID field from the target list, in which case, you would need to run additional queries to retrieve relevant field values from the target list. For more information about generating LINQ classes that work across site boundaries, see the section, "Using List Joins across Sites," in Using LINQ to SharePoint.

  • When performance is paramount. Using LINQ to SharePoint incurs some additional overhead, because the LINQ query must be dynamically converted to CAML at run time. If you are running a time-sensitive operation and performance is critical, you may want to consider creating the CAML yourself and using SPQuery to execute the query directly. Generally speaking, this approach is only required in extreme cases.

Using SPQuery with External Lists

Using the SPQuery class is the only supported way to query external lists. Using this approach, you can query an external list in exactly the same way that you would query a regular SharePoint list. However, there are some additional considerations when you access data from an external list:

  • You cannot join across external lists, even if you have defined entity associations in the BDC model.
  • You can specify authorization rules by assigning permissions to the external content type. Most Web services and databases will also implement authentication and authorization. You will need to implement a security scheme either by using the Secure Store Service or by configuring your own security mechanisms.
  • Throttling mechanisms and limits differ from those that apply to regular SharePoint lists. When you query an external list, the throttling settings for the BDC runtime apply.

If you want to access external data from a sandboxed application, without using a full-trust proxy, you must use an external list. Using the BDC Object Model or directly accessing external systems is prohibited in the sandbox environment. As a result, using the SPQuery class and the SPList object model with external lists is the only option for external data access if you want your solution to run in the sandbox.

Note

For security reasons, the identity token for the current user is removed from the sandbox worker process. If you need to access external lists from within the sandbox environment, you must use the Secure Store Service to map the managed account that runs the User Code Proxy Service to the credentials required by the external system. For more information, see Hybrid Approaches in the Execution Models section of this documentation.

Using SPSiteDataQuery

The SPSiteDataQuery class is used to query data from multiple lists across different sites in a site collection. SPSiteDataQuery is commonly used in list aggregation scenarios, where list data from team sites or other subsites is collated and presented in a single interface. Unlike the SPQuery class, you cannot use join predicates or projected fields with the SPSiteDataQuery class. The SPSiteDataQuery will only aggregate data from SharePoint lists and will ignore data from external lists.

Note

Because of a bug in SharePoint 2010, an SPException (hr=0x80004005) is thrown if you execute an SPSiteDataQuery on a site that contains an external list with a column named Id. This may be fixed in a future service pack or cumulative update.

The high-level process for using the SPSiteDataQuery class is as follows:

  • Create an SPSiteDataQuery instance.
  • Set properties on the SPSiteDataQuery instance to specify the lists or list types to include in the query, the individual sites to include in the query, and the CAML query itself.
  • Call the GetSiteData method on an SPWeb instance, passing in the SPSiteDataQuery instance as a parameter. The GetSiteData method returns a DataTable.

The following code example, which was adapted from the sandbox reference implementation, shows this.

SPSiteDataQuery query = new SPSiteDataQuery();
query.Lists = "<Lists BaseType='1' />";
query.ViewFields = "<FieldRef Name='SOWStatus' />" + 
                   "<FieldRef Name='EstimateValue' />";
query.Query = "<OrderBy><FieldRef Name='EstimateValue' /></OrderBy>";
query.Webs = "<Webs Scope='SiteCollection' />";

SPWeb web = SPContext.Current.Web;
DataTable results = web.GetSiteData(query);

In terms of efficiency, the SPSiteDataQuery class provides an optimal approach to data access in the following scenarios:

  • When you need to query multiple lists within the same site collection for the same content
  • When you need to query across two or more lists that are not related by lookup columns

You should avoid using LINQ to SharePoint to aggregate list data across sites. LINQ to SharePoint is designed to aggregate data across list relationships defined by lookup columns. Attempting cross-site operations in LINQ to SharePoint typically requires a post-query join operation in memory, which is a resource intensive process. In contrast, the SPSiteDataQuery class is optimized for querying list data across multiple sites in a site collection and across multiple lists within a single site.

Note

The SPSiteDataQuery class is available in SharePoint Foundation 2010. SharePoint Server 2010 includes additional built-in components that are appropriate for certain list aggregation scenarios. These components include the Content Query Web Part and the Portal Site Map Navigation Provider.