SharePoint 2010 Data Access with LINQ to SharePoint

SharePoint 2010 Data Access with LINQ to SharePoint

news

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:

GetAllChoiceAttributeValues

Example:

Results:

GetChoiceAttributeValues(IEnumerable values)

Example:

Results:

GetChoiceAttributeValue(T value)

Example:

Results:

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:

  1. Create a backup reference to the current HttpContext;
  2. Clear the HttpContext (this ensures that the SPServerDataConnection class will initialize new SP* objects when any DataContext classes are constructed);
  3. Run the code by calling the existing SPSecurity.RunWithElevatedPrivileges method;
  4. 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’).

Conclusion

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.

Happy coding!

References

  • 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

 Recent Posts

select to filter posts

Gamification: Coming to a workplace near you!

by Scott Johnston

Video games aren't just a wave of the future; they're the current reality for millions of players who take to their consoles and PCs every day.

read more

Managing Voter Experience

by Jim Ward

I had the chance to run a political campaign for a friend in the last civic election in Edmonton. A terrific experience, and full of surprises. But how could a computer guy possibly be qualified to run a campaign? If you’ve never been involved in one, ...

read more

Red Adair Rings True In IT

by Kerry Wiebe - Director of Business Development

“If you think it's expensive to hire a professional to do the job, wait until you hire an amateur.” - Red Adair This quote resonated with me when I recently took on a do-it-yourself (DIY) renovation project at my home. I decided to do the project ...

read more

My History with Umbraco

by Terence Jee

Umbraco is an open source, free CMS that allows users to download it and use for their own needs.

read more

Prototyping with Twitter Bootstrap

by Davis Levine

Front end frameworks can be powerful tools in a designer's development process. It can save a ton of time in the project process, and with most frameworks employing a fluid grid system, scaling to different devices becomes less of a headache.

read more

Knowledge of Possibilities

by Ammneh Azeim

Recently, I read a great blog post by Daniel Burrus which states “Give your customers the ability to do what they can’t currently do but would want to if they only knew it was possible.” The key here is knowledge of possibilities and knowledge which comes

read more

Safety Codes Council Adopts Microsoft Dynamics CRM

by Andrew Slessor

Recently, the Alberta Safety Codes Council adopted Microsoft Dynamics CRM as their platform for the Master Electrician Management System.

read more

Legal Aid Alberta Implements Microsoft Dynamics® CRM

by Andrew Slessor

Legal Aid Alberta Improves Client, Case and Financial Management with Microsoft Dynamics® CRM Legal Aid Alberta (LAA) provides cost-effective services to 200,000 Albertans in need each year.

read more

World Usability Day

by iomer

​​ This Thursday, November 7 is World Usability Day, a day dedicated to making the world a more user friendly place.

read more

PanEx 2011 Unites Developers and UX Professionals

by iomer

Last night I attended PanEx 2011 organized by UX YEG, Edmonton's User Experience community group.

read more

Committed to Disruption

by Jim Ward

​ One of the things I love about our industry is the counterintuitive truths that make themselves evident on a regular basis.

read more

SharePoint 2010 Data Access with LINQ to SharePoint

by Anthony Haxby

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

read more

Case Study: Human Security Report Project

by iomer

The Human Security Report Project (HSRP) is an independent research centre affiliated with Simon Fraser University’s School for International Studies in Vancouver, Canada.

read more

Exciting new features in Dynamics CRM 2011

by Andrew Slessor

​​​​ We have been implementing Microsoft Dynamics CRM 2011 for a few months now and I am excited with many of the new features that have been added to the system.

read more

Bringing Teams Together: Calendar Overlays in SharePoint 2010

by iomer

Calendar aggregation is a topic that will come up time and time again when discussing desired functionality for a collaborative environment.

read more

An Opportunity to Pause

by Jim Ward

I was at the Microsoft Canadian Leadership Summit in Redmond last week. It's a great event for learning about future trends and technologies, as well as considering and applying the lessons presented by Microsoft experts and futurists.

read more

SQL Server Replication

by Mike Hebert - solution architect

Database replication is all about distributing data. There are many common scenarios where replication may make sense, whether it is to enable a mobile workforce, to allow intensive reporting or data mining without interfering with day-to-day operations,

read more

Stop Chasing Butterflies

by Jim Ward

Had some great learnings last week. Picture this. You've put your strategic plan together, and your team is busy doing the right things.

read more

The Coffee Still Matters

by Jim Ward - Founder

Former Starbucks CEO Howard Behar penned a wonderful book on leadership called “It’s Not about the Coffee”, based on his experiences building the Starbucks brand.

read more

Top Ten Reasons for Collaborative Governance

by Andrew Slessor

Collaborative Governance is the principal of sharing ownership of the intranet with various groups or individuals in an organization. Instead of no owner, or a single one, a collaborative team governs through the application of policies, standards and tem

read more

Website Sustainability

by iomer

​Website sustainability may sound like a complex topic but the concepts are quite straightforward and are fundamental to successful website development and growth.

read more

Diagnostic Logging

by Steven Sirockman - solution manager

As a SharePoint Technology Specialist, I have had many phone calls from clients asking me to come in and troubleshoot their SharePoint Server environment. One of the first questions I ask them is if they have configured diagnostic logging.

read more

Intranet Users Roles and Responsibilities

by Andrew Slessor

In order to manage a sucessful intranet, users must be certain of what tasks they are responsible for on the site. The roles for intranet users can be seperated into seven categories; these are described below.

read more

Snippet Compiler versus LINQPad

by Mike Hebert

Jeff Key’s Snippet Compiler is a popular developer tool that has been around for years – I first learned about it in a 2004 MSDN article titled “Ten Must-Have Tools Every Developer Should Download Now”.

read more

Dashboarding SQL Performance Counters

by Gord Barker - solution architect

In a previous blog I wrote about benchmarking SQL performance. This is important for those rare occasions when someone says the system is not running fast enough.

read more

3 Tools for Clearer, More Effective Writing

by Kael Nicholson

Need to convey a wealth of information in a tiny space? When working in the technical world, writers are constantly torn between the need to accurately describe complex ideas and the desire to make them clear and accessible.

read more

Integrating AJAX and Telerik with SharePoint

by Gord Barker

​To extend your SharePoint site to include Ajax, you will need to perform a few steps

read more

Logging SQL Performance Statistics

by Gord Barker - solution architect

One of the most important tools in analyzing a SQL Server performance problem is a good understanding of the normal behaviour your system exhibits.

read more

Five Things I'm Looking Forward to in SharePoint 2010

by iomer

Microsoft has released some Sneak Peek videos giving a preview of what we can expect to see in SharePoint 2010.

read more

SharePoint Application Performance

by iomer

Recently we were approached by a customer who was having performance issues with a custom application built on top of SharePoint.

read more

A Program to add files to a Zip file

by Gord Barker - solution architect

I recently had to retrieve 45 days worth of Air Quality data from a web site. I used WGET to retrieve 35000 tiny text files to a sub directory.

read more

The Top 10 SQL Scripts You Must Have

by Gord Barker - solution architect

Here is a collection of important SQL Script gathered from around the net (including the Steve and Chris Show on SSWUG). These are scripts you should know about or have in your toolkit.

read more

Edmonton Technology Community

by Christina Gray

As technology continues to advance, keeping up and prioritizing what you ‘need to know’ becomes more of a challenge.

read more