Data Access in SharePoint 2010

SharePoint 2010 introduces several new ways in which you can programmatically interact with your data. Most notably, the introduction of LINQ to SharePoint allows you to build complex list queries with the user-friendly language integrated query (LINQ) syntax instead of constructing queries with the somewhat cumbersome Collaborative Application Markup Language (CAML). Both LINQ to SharePoint and CAML now support join predicates in queries, which moves the SharePoint list-based data model a step closer to the power and flexibility of a relational database. One of the major evolutions in the latest version of SharePoint is the blurring of the distinction, from the user's perspective, between internal and external data. The introduction of external lists in SharePoint2010 means that you can use many of the same techniques to query data, regardless of whether that data resides in a SharePoint content database or an external system.

The introduction of new options for data access brings new challenges and best practices for developers. This chapter provides an overview of each approach to server-side data access and provides guidance on the benefits and potential pitfalls of each approach.

Note

Client data access techniques, such as the new client object model and REST services, are not covered in this chapter. These techniques cover a large area of functionality and merit a chapter of their own. For more information, see Data Access for Client Applications.

SharePoint 2010 offers three key approaches that you can use to query data:

• CAML queries (SPQuery and SPSiteDataQuery). The SPQuery and SPSiteDataQuery classes allow you to construct and submit CAML queries to perform data operations. CAML suffers from a number of shortcomings, including quirky syntax, lack of tooling support, and difficult debugging. However, CAML remains the core engine for data operations and is still the most appropriate choice in some scenarios. The CAML schema has been expanded in SharePoint 2010 to include support for join predicates. You can also use the SPQuery class to query external lists. Note that the results returned by CAML queries are non-typed items.
• LINQ to SharePoint. SharePoint 2010 allows you to use LINQ queries to perform data operations on SharePoint lists, including support for join predicates. LINQ to SharePoint operates against strongly-typed entities that represent the items in your lists, and SharePoint 2010 includes a command-line tool named SPMetal that you can use to generate these entities. Internally, the LINQ to SharePoint provider generates the underlying CAML queries that perform the data operations.
• The Business Connectivity Services (BCS) object model. SharePoint 2010 includes new BCS APIs that enable you to interact with external data. The BDC Object Model allows you to programmatically navigate the data entities and associations in your Business Data Connectivity (BDC) models and to invoke the stereotyped operations defined on these entities.

The following table provides a summary of the different scenarios in which you can use each of these approaches to data access.

Scenario	LINQ to SharePoint	SPQuery	SPSiteDataQuery	BDC object model
Querying SharePoint list data within a site
Using joins on SharePoint lists within a site
Using joins on SharePoint list data across sites within a site collection
Aggregating list data across multiple sites within a site collection
Querying external list data within a site
Navigating associations between entities in a BDC model (external data)
Accessing binary streams from external data
Accessing external data from a sandboxed application (requires the use of external lists)
Querying external data that returns complex types
Querying external data that uses a non-integer, or 64-bit integer, ID field
Navigating bidirectional associations between entities in a BDC model (external data)
Locating an entity by specifying a field other than the ID field (external data)
Querying entities that include fields that do not map to an SPFieldType (external data)
Perform bulk operations on external data

Note

For information about how to use LINQ to SharePoint and CAML queries, see Querying from Server-side Code on MSDN.

This section includes the following topics that will help you to understand the key details, performance issues, and best practices behind the different approaches to data access in SharePoint 2010:

• Using Query Classes. This topic identifies scenarios in which you should consider using the CAML-based query classes—SPQuery and SPSiteDataQuery—for data access.
• Using LINQ to SharePoint. This topic examines the use of the new LINQ to SharePoint provider, and identifies key efficiency issues and potential stumbling blocks.
• Using the BDC Object Model. This topic identifies scenarios in which you must use the BDC object model for data access and provides insight about how you can use the BDC object model to accomplish various common tasks.

Out of Scope Topics

There are a number of additional approaches to data access that are beyond the scope of this documentation. You may want to consider the following components for more specialized scenarios:

• The ContentIterator class. This class is new in SharePoint Server 2010. It enables you to iterate sites, lists, and list items in chunks, in order to avoid violating query throttling thresholds. This class is useful in circumstances where you cannot avoid iterating large lists. If you think a list is likely to grow large, consider using the ContentIterator class to access the list from the start. For more information, see Large Lists.
• The ContentByQueryWebPart class. This class is part of the publishing infrastructure in SharePoint Server 2010. It allows you to submit a CAML query across sites and lists within a site collection and then format the HTML output by specifying an XSL transform. The ContentByQueryWebPart class makes extensive use of caching to provide an efficient means of data retrieval.
• The PortalSiteMapProvider class. This class is also part of the publishing infrastructure in SharePoint 2010. It provides an efficient mechanism that you can use to query and access cached objects within a site collection. The PortalSiteMapProvider class offers a useful alternative to the ContentByQueryWebPart class when you require programmatic access to the objects you are retrieving and is capable of caching query results from SPQuery or SPSiteDataQuery. Like the ContentByQueryWebPart class, the PortalSiteMapProvider makes extensive use of caching for reasons of performance and efficiency.