In SharePoint 2007 a popular and (relatively) straight-forward method of querying SharePoint Foundation data from lists was using the SPQuery class and involved writing the query using the Collaborative Application Markup Language (CAML). Unfortunately writing CAML XML is a process that is typically error-prone and most developers rely on tools to auto-generate the query, such as the U2U CAML Query Builder. The result is that large portions of these CAML XML-strings are embedded in the solution source code. This is problematic if the structure of the lists being queried is changed as these CAML queries will break without any notification -- Compiling the SharePoint solution doesn’t check the validity of the embedded XML-string against the columns or field-types of the target SharePoint list(s).
SharePoint 2010 introduces the LINQ to SharePoint provider to allow developers to write queries using the LINQ syntax to query SharePoint lists. The immediate advantage is that developers are now writing their queries against strongly-typed entity classes -- Using Visual Studio’s integrated intellisense and the compiler warnings for field existence and type-checking significantly reduces the time taken to debug query errors introduced when list structure is changed.
Most developers are familiar with LINQ syntax and the official MSDN article-set Managing Data with LINQ to SharePoint (http://msdn.microsoft.com/en-us/library/ee537339.aspx) is a good introduction to the basics. The purpose of this article will be to discuss some of the lessons-learned surrounding generating DataContext classes using the SPMetal and how to effectively use the resulting provider and entity classes within a custom SharePoint application.
Generating a DataContext for a Subset of Lists
Using SPMetal to generate a DataContext class by default will generate entity classes for all lists located on the target site. When the architecture of your SharePoint portal involves multiple sub-sites with different custom lists residing within each sub-site this can often cause duplication when generating your DataContexts. For instance, generating a DataContext for SubSiteA might result in entity classes Announcement, Page, Task and Portfolio while the subsequent generation of a DataContext for SubSiteB would include Announcement, Page and Vehicle – Note the duplication of the entities Announcement and Page between both the DataContext classes, when ultimately the point is to have two DataContext classes that only contain entities Portfolio and Vehicle, respectively. This can be accomplished by passing an XML configuration to the SPMetal command that specifies the lists to be generated in the resulting DataContext.
For example, to generate a DataContext for the Portfolio entity from SubSiteA:
Where the Portfolio.xml is the following:
The configuration XML should be self-explanatory – It contains an element that specifies to include the list titled Portfolio in the output (), and then another element indicating that all other lists located in the web should not be processed (). Running this command yields the desired result of a PortfolioDataContext containing the single entity class Portfolio.
Using configurations to separate list entity classes into different DataContexts results in reduced duplication of classes as entities relating to common out-of-the-box SharePoint lists (Announcements or Tasks) won’t appear in each generated DataContext and can be separated into their own specific DataContexts that can be re-used across sites. For instance, the project’s code-base could include a single PageDataContext class that can be used for querying Page list items across every sub-site. Furthermore, developers can remain productive by generating separate DataContext classes targeting lists on the sub-site their code interacts with.
Getting Human-Readable Choice Field Values
If a list contains one or more columns that are of the type Choice (or Multi-Choice) then SPMetal will generate strongly-typed enumerations representing the valid set of values that can be assigned to the associated entity.
Consider the following enumeration generated from a PortfolioType Choice column with a valid set of values Modern Art, Depressing Poetry and Literature:
Each of these generated enumeration always include values None and Invalid followed by the remaining set of valid values that can be assigned to the Choice field. Integer values assigned to each enumeration value are a power of 2 and enables developers to use bit-wise operations to represent Multi-Choice values and perform comparisons (Note: you will encounter issues if your choice field contains more than thirty options as overflow will occur and SPMetal will ‘wrap-around’ the generated values). Furthermore, enumeration values are decorated with a ChoiceAttribute attribute whose value represents the human-readable display text it represents (as spaces and special characters are stripped in the enumeration value name).
When developing custom views against a list a common requirement is binding a UI element, such as a dropdown, radio-button or checkbox list, to the valid set of options for a lookup field. Since SPMetal generates Choice fields values in the format shown above we can create utility methods for retrieving the human-readable display text that can be used as a data-source for data-bound controls. The following utility methods use reflection to inspect enumeration values to extract their corresponding display text. Note that the following examples reference the PortfolioType enumeration from above. You’ll need to include the following import declarations to use these methods:
LINQ to SharePoint and RunWithElevatedPrivileges
Most developers will be familiar with the method SPSecurity.RunWithElevatedPrivileges for executing code through the privileges assigned to the App Pool account. When developing custom views or WebParts there’s usually the need to allow the current user (or an anonymous user) to access the details of list items that are restricted to a security group they are not part of. Wrapping data retrieval or commands in this method call will by-pass the security context assigned to the current user and interact with lists in an unrestricted way.
When moving from CAML queries to the SharePoint LINQ DataContexts we would expect to be able to re-use this same method to elevate the permissions, causing the DataContext to execute in an un-restricted manner when querying lists. However an obscure problem exists for the DataContext class – If the SPContext.Current property has been assigned then it will implicitly execute all of its queries using that context, regardless of whether the queries are wrapped in RunWithElevatedPrivileges.
This issue has been identified an discussed on a number of blogs (a very good explanation can be found on Joe Unified, http://jcapka.blogspot.com/2010/05/making-linq-to-sharepoint-work-for.html), with the source of the problem being in the SPServerDataConnection class that manages the connection to the underlying SPWeb queries will be run against. If the SPContext.Current has been assigned a value it naively accepts this as the execution context – Because no new SP* objects are initialized the associated SPSite and SPWeb objects aren’t set to run under the App Pool account and instead run under the current user.
As some clever developers have figured-out the SPServerDataConnection class can be coerced into initializing new SP* objects by clearing the current HttpContext before instantiating the LINQ to SharePoint DataContext classes. The steps are as follows:
Create a backup reference to the current HttpContext;
Clear the HttpContext (this ensures that the SPServerDataConnection class will initialize new SP* objects when any DataContext classes are constructed);
Run the code by calling the existing SPSecurity.RunWithElevatedPrivileges method;
Re-assign the backup reference of the original HttpContext.
And the resulting code is:
Returning to the example of the Portfolio list located on SubSiteA this utility method can be called in the following manner (to return all Portfolio items of type ‘Literature’).
LINQ to SharePoint is an exciting technology which enables developers to use .NET 3.5’s LINQ syntax to quickly create data access solutions that connect to SharePoint 2010. However, as the technology is still relatively new there are some nuances that could prevent a person or team from adopting it as easily they would have in a non-SharePoint environment, such as LINQ2SQL or Entity Framework. Relying on SPMetal and its configuration options to generate the DataContext and related entity classes, and the representation of Choice fields and potential ways to data-bind against the values were discussed with some implementation suggestions. As well a resolution to the security context issue when dealing with a situation where list data needs to be queried in an anonymous context was presented. This certainly isn’t the breadth or depth of any LINQ to SharePoint discussion, but I hope it has been informative and can aid developers in understanding and embracing this new technology.
Managing Data with LINQ to SharePoint, http://msdn.microsoft.com/en-us/library/ee537339.aspx
Using LINQ to SharePoint, http://msdn.microsoft.com/en-us/library/ff798478.aspx
Reference Implementation: SharePoint List Data Models, http://msdn.microsoft.com/en-us/library/ff798373.aspx
Making Linq to SharePoint work for Anonymous users, http://jcapka.blogspot.com/2010/05/making-linq-to-sharepoint-work-for.html