SharePoint 2010 List Joins – SPQuery enchancements

Joins

With relational lists in SharePoint 2010, we also have a better support for joins.

Following ways of joining lists to pull out aggregated data:

  • SharePoint API (SPQuery object)
  • LINQ to SharePoint
  • CAML
  • SharePoint Designer 2010

In this article I will tell you the SPQuery enhancements regarding joins and relational data.

SPQuery enhancements

Using the object model with SharePoint 2010, you now have the ability to use the properties of the SPQuery object:

  • SPQuery.Joins
  • SPQuery.ProjectedFields

SPQuery.Joins Property

With the new property called Joins on the SPQuery object, we can (using CAML) define a join for our query like so:

A working sample of the SPQuery code could look like this code:

SPQuery query = new SPQuery();
query.Query = “[YOUR CAML QUERY HERE]“;
query.Joins = “[YOUR JOIN CAML HERE (See above for example)]“;
query.ViewFields = “[SAME AS BEFORE]“;

SPListItemCollection items = myList.GetItems(query);
foreach(SPListItem item in items)
// Do with your items here like you’re used to, but now all joined up

So the only thing you really need to do is to make sure you’ve got the join set up with the SPQuery.Joins property – and then trigger
SPQuery.ProjectedFields Property

The SDK states the ProjectedFields property quite clearly:

…itemizes fields from joined foreign lists so that they can be referenced in the Where element and the ViewFields element

You should have a go at this link to learn more about projected fields, and how you can utilize them in your SPQuery objects:

http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spquery.projectedfields(office.14).aspx

Advertisements

About Joseph Velliah
As a SharePoint Developer my professional interests tend to be technical and SharePoint focused. I run a blog at "SP RIDER" where you can expect to read HOW TOs and scenarios that I run into during my day to day job. I hope my posts will give back a little to the community that is helped me.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: