I was in a situation where I had a parent list with some details and then a separate list for child items. The child list had a lookup to the parent list to make the association between the two. I needed a query which allowed me to search on some fields in the parent list and some fields in the child list.
When I was thinking about this I came up with a few different ideas which are each discussed below:
- Multiple Caml Queries. One option was I could run a Caml query against the parent list to get the items which meet the parent item criteria and then do another Caml query looking for items in the child list which are associated to the items returned in the parent Caml query but also meet the filtering on the child list. Obviously this is not idea as it would involve a decent amount of code to do but would also be fairly inefficient as well.
- Linq to SharePoint. While this would probably have been the easiest approach as you can very quickly join two lists using Linq, once you have your entry classes generated, but I decided against this approach. My reasoning was based around a solution design perspective. I decided that while Linq to SharePoint would prove beneficial in this situation it would require adding and maintain another element in the solution thus increasing the complexity.
- Join Caml Query. This was something I had never done before but I was aware it was one of the new features in SharePoint. I done some research and it seemed like a fairly straightforward approach so I decided to go with this.
There are various articles out there on how to join lists via a Caml query so I won’t go into a great deal of detail only the points I found interesting.
The first thing is the Caml query will be created as you normally would be it needs to be run against the child list not the parent list.
In order to map the lists together you use the Joins property of the SPQuery object. As I mentioned there is a lot of information out there for more complicated joins but in my situation it was simply joining parent and child lists together, see example below.
With the join created the next step is to set up the projected fields, these are the fields from the parent list which you want to display or filter against in the where clause, see below. The Name element is any name you want to give it and this will be used in the view fields and query sections of the SPQuery object. As far as I can see the type is always Lookup. The ShowField element is the name of the field in the parent list you want to map to this new field.
You must ensure all projected fields are listed in the view fields SPQuery property. Again anyone who has done basic Caml queries will have seen this before the only consideration is when using fields from the parent list you have to use the name set in the projected fields not the name of the column in the parent list, see below.
With the join between the lists done and the mapping for the fields in the parent list you can then write your Caml query as per normal and filter against details in the parent list, see below for a simple example Caml query.
The final element is to associate all these with your SPQuery object and then pass this to the list GetItems method.
Getting to this point did take some changing of the various Caml query properties and it was slightly frustrating but I don’t think this is particularly related to joins within Caml but more of a general issue with Caml.
There was one issue which seems particularly related to joins and this was when trying to get a DataTable of the results instead of a SPListItemCollection, see example below. This throws a NullReferenceException, see stack trace below, and it seems the only way around this is to work with the SPListItemCollection and not a DataTable
The error was System.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.SharePoint.SPFieldMap.EnsureFieldArray() at Microsoft.SharePoint.SPFieldMap.GetFieldObject(Int32 columnNumber) at Microsoft.SharePoint.SPListItemCollection.GetVisibleFieldIndices(Boolean isJsGrid, Int32& arrVisibleFieldIndices, Int32& iVisibleFieldCount) at Microsoft.SharePoint.SPListItemCollection.GetDataTableCore(Boolean isJsGrid)
I think that the join functionality in SharePoint 2010 is a useful feature, however given the fiddly nature of this along with how easy it is to accomplish the same thing using Linq to SharePoint will mean a lot of people won’t use this approach. In my circumstances it works very well and with a few extra lines of code, compared to a normal Caml Query, gives me a lot of extra functionality.