Sujeewa Ediriweera

February 18, 2012

List Join in SharePoint 2010 using CAML

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

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


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


Output :


Accessing secure store service programmatically – part 02 (Impersonation)

Filed under: SharePoint — sujeewa.ediriweera @ 3:41 am
Tags: , , , ,

Over view
Although we have the run with elevated privileges option , In some cases  we need to execute specific functions with specific  user account privileges .Impersonation is very common when we are dealing with SharePoint services.


Initiate site object with different user token and execute code under nitiated site. You can do following

  • Create impersonate SPWeb object inside impersonated site object.
  • Get impersonate SPContext using impersonated site object.
  • Get impersonate SPServiceContext using impersonated site object.

You need to have a specific domain account to use as impersonate user. In my case I have setup a Secure Service Store Application key to retrieve impersonate user information


Code for Impersonation

Blog at