In the Microsoft Office SharePoint Server world, as in most other systems, it’s all about the data. This means getting data in, getting data out, making changes, and cleaning up (i.e., the basic Create, Read, Update, Delete—CRUD—operations). We all know, however, that it’s not quite that simple, because we need to deal with local versus remote scenarios, security, performance, concurrency, and more—all while trends continue to change in SharePoint, the .NET Framework, and even in the software development industry. This article is the first in a series that will take an in-depth look at working with the new data access approaches in SharePoint Server 2010, as well as changes to the existing approaches.
We will take a close look at the three new data access technologies in SharePoint 2010: LINQ to SharePoint, SharePoint 2010 Data Services, and the SharePoint 2010 Client Object Model. I’ll explain what each technology is, what it’s used for, how it works, and how to make the best use of it while avoiding common gotchas. We’ll start with LINQ To SharePoint in this article—partly because it’s typically the most commonly used of the three technologies, partly because it underpins some of the other aspects we’ll examine later, and partly because we’ll learn some key concepts and practices that also carry over to the other technologies.
LINQ to SharePoint Definition and Rationale
Whenever I’m trying to learn a new concept or technology, I find it helpful to try to understand the core principles of what predated the technology, as well as what the new technology is trying to address. To do this for LINQ to SharePoint, let’s take a stroll way back down memory lane to the distant past—perhaps around the time of the Wild West and the woolly frontiers. Back then, things were decidedly rough-and-ready. If you wanted chicken for dinner, you gosh-darn-it went and caught and plucked and gutted a chicken! If you wanted water, you gosh-darn-it went down to the well and drew some water! Even worse than that, if you wanted some records out of your SharePoint installation, by golly you hand-crafted a full-bodied, long-winded Collaborative Application Markup Language (CAML) query, something like this:
<Query>
<Where>
<Geq>
<FieldRef Name="Field1" />
<Value Type="Number">1500</Value>
</Geq>
</Where>
</Query>
Of course, to execute that CAML query from your program, you needed to write some .NET code to do the call, wrapping it perhaps in an SPQuery object such as the following:
SPQuery query = new SPQuery();
StringBuilder sbQuery = new StringBuilder();
sbQuery.Append("<Where>");
sbQuery.Append("<Geq>");
sbQuery.Append("<FieldRef Name =\"" + fieldName1 + "\"/>");
sbQuery.Append("<Value Type=\"" + fieldType1 + "\">" + fieldValue1 + "</Value>");
sbQuery.Append("</Geq>");
sbQuery.Append("</Where>");
query.Query = sbQuery.ToString(); Notice some key points about this code block. It’s loosely typed in a big string variable (well, a StringBuilder to be exact). This means it’s highly susceptible to human error—just one typo and the whole query will self-destruct. Of course, we can only discover this much later, at runtime, rather than nice and early during compile time. In fact, if you don’t have the correct quality gates in place, your query might even end up all the way into production before the trouble unfolds! Furthermore, because we’re just dealing with string variable names for the fields, we have no way to determine in place what types we’re dealing with—we have to go look them up. Similarly, we don’t have access to the productivity and intuitiveness of IntelliSense (everyone’s favorite Visual Studio feature). Finally, the code is very verbose—there’s a lot of ceremony required just to execute a fairly simple query.
To make matters worse, in SharePoint 2010–land, we now have better relationship capabilities between lists. In general, this is a great feature—but it makes our CAML queries (and the resulting .NET code) considerably longer and more complex when we want to express these joins, such as in the following example:
<Query>
<Where>
<And>
<BeginsWith>
<FieldRef Name="ContentTypeId" />
<Value Type="ContentTypeId">0x0100</Value>
</BeginsWith>
<Eq>
<FieldRef Name="ClientCity" />
<Value Type="Lookup">Cape Town</Value>
</Eq>
</And>
</Where>
</Query>
<ViewFields>
<FieldRef Name="Title" />
<FieldRef Name="ClientTitle" />
<FieldRef Name="BudgetHours" />
</ViewFields>
<ProjectedFields>
<Field Name="ClientTitle" Type="Lookup" List="Client" ShowField="Title" />
<Field Name="ClientCity" Type="Lookup" List="Client" ShowField="City" />
</ProjectedFields>
<Joins>
<Join Type="LEFT" ListAlias="Client">
<Eq>
<FieldRef Name="Client" RefType="ID" />
<FieldRef List="Client" Name="ID" />
</Eq>
</Join>
</Joins>
The associated .NET code is similarly far longer and more unwieldy. The bottom line is that, although CAML provides quite a bit of functionality and is continuing to evolve, it isn’t pleasant to work with directly. So, until Microsoft implements an Xbox Kinect plug-in to let users query SharePoint directly, using elaborate gestures and dances, we could at least settle for some kind of middle ground that abstracts the CAML away—which is where Language-Integrated Query (LINQ) comes in. (Incidentally, if you happen to build the aforementioned Kinect plug-in, be sure to email me for where to send royalties for the idea.)