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 :

Advertisements

Blog at WordPress.com.