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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.