Sujeewa Ediriweera

February 18, 2012

List Join in SharePoint 2010 using CAML

Filed under: SharePoint — sujeewa.ediriweera @ 1:48 pm
Tags: , , , ,

Overview
As a typical .net developer I have used SQL table join operation frequently.  SQL gave us  very rich way of manipulating data and we felt the luxury of it. The real fun happened when we where dealing with SharePoint 2007. In SharePoint 2007 we didn’t have direct way to join list and filter data programmatically. Link list concept was there but we need to use SharePoint designer to do it. Basically CAML query didn’t support list join operation (SharePoint 2007). In SharePoint 2007 my desired way of doing list join operation was through LINQ . But we had some performance issues with it. At some point we used third party tool to do the join operation for us. E.g. Bamboo List Roll-up web part

But thanks to Microsoft in 2010 release of SharePoint, it supports CAML query list join operations with the help of SPQuery class.

Recently I came across a customer requirement where I need to bring three lists information to a single view and present it to user in SharePoint 2010.I knew SharePoint 2010 supports list join operations so I started researching on how we join SharePoint lists using CAML query.

In My example I have employee List and department list. Employee list has lookup column to department list.

Department List

Employee List

Solution

In SharePoint 2010 Use CAML query   and SPQuery class for list join operation.

SPQuery object
Query Filtering , order by commands
Joins Inner Join or Left join commands
ProjectedFields Lookup list fields
ViewFields Available fields in result SPListItem

Code:

Output :

About these ads

2 Comments »

  1. practical post,great!

    Comment by Chanaka Pradeep — February 19, 2012 @ 5:43 pm | Reply

  2. I was in ur session in sharepoint user meeting thanks for that session i got so many stuffs about sharepoint , and in the begining i didnt thought that u r gng to do a session i thought that u r just a member heheheheh somehow thanks buddy!!!!!! welldone

    Comment by dedunu — June 16, 2012 @ 10:01 am | Reply


RSS feed for comments on this post. TrackBack URI

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 )

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

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: