Home > SharePoint 2010 > Limitations of the SharePoint join using CAML

Limitations of the SharePoint join using CAML

Limitation One

In SharePoint 2010, you can join the primary list to a foreign list and include more than one field from the foreign list. However, the limitation is that the included fields from foreign list have to be the following type:

  • Calculated (treated as plain text)
  • ContentTypeId
  • Counter
  • Currency
  • DateTime
  • Guid
  • Integer
  • Note (one-line only)
  • Number
  • Text

The above limitation also explains why you cannot include some types of the fields from the remote list when creating a lookup.

Limitation Two

When using CAML query to join SharePoint lists, there can be joins to multiple lists, multiple joins to the same list, and chains of joins. However, the limitations are only inner and left outer joins are permitted and the field in the primary list must be a Lookup type field that looks up to the field in the foreign list.

Limitation Three

The support for writing the JOIN query in CAML is very limited.I have to hand-code the CAML query to join the lists,not fun at all.Although some blogs post mentioned about using LINQ to SharePoint and get the CAML code from there , but I never get it to work.You can check this blog post  for this.Let me know if it works for you.

References:

http://msdn.microsoft.com/en-us/library/ee535502.aspx

http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spquery.joins.aspx

Advertisements
Categories: SharePoint 2010
  1. August 24, 2011 at 6:48 am

    There seems to be another limitation — though I can’t quite put my finger on it just yet.

    The Join operation will throw an error: “The query uses unsupported elements, such as references to more than one list, or the projection of a complete entity by using EntityRef/EntitySet.” in some cases.

    I’ve been testing this to figure out what causes it. There are various posts and docs that indicate that this can be the result of using unsupported operators (which I’m not), but I think it may actually be due to the number of lookup/fields on the source list/table in question.

    The SDK docs do add one additional caveat to this: “For example, if a query to table A effectively requires a separate query to Table B for every row of Table A, then the query to table A is not supported”. This seems to be a weird note to me; isn’t this what an inner join is supposed to do?

  2. June 6, 2014 at 7:38 am

    It would be good to note that regarding limitation number two, the lookup field must be a single value lookup field. There is good evidence that multiple value lookup fields do not work for caml joins.

  1. No trackbacks yet.

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

%d bloggers like this: