Skip to main content

SharePoint and List Joins with CAML


This came to me as an issue when I started migrating one project from SharePoint 2010 to SharePoint 2013. The code uses LINQ to SharePoint and it was not written in a clever way. We decide to use CAML queries and optimize the code.

The scenario: Create gridview that gathers information from three(or more) lists.
The problem: How to create the CAML query joins and projected fields correctly.

Explanation:

Option 1: Get generated CAML query from LINQ to SharePoint code
This doesn’t work. I wrote the LINQ to SharePoint code connected the three lists. When I executed the code the following error was thrown – “The query uses unsupported elements, such as references to more than one list, or the projection of a complete entity by using EntityRef/EntitySet.” Here is some explanation about it and why this can’t be used.

Option 2: Write the query yourself
I found this article in MSDN. Read it !
The second part in it is exactly my scenario. But it takes me a lot of time to understand how exactly to write this in server-side code. So here is a working code of the described scenario:



And yes, this works but what if we want to get more columns. Here is same scenario but more complicated.


I have added new colums and added new projected fields in the query.
The following code get all the additional data:



Some important points:
1. Only the Order’s list columns can be used with their real internal names.
2. The other columns are all projected and there are limitaton of the column's type. For example the 'Contact Person' column in my scenario can't be a Lookup or Person and Group.
3. The type of the projected field is always ‘Lookup’
4. ShowField is the value of the internal names of the fields
5. The ListAlias and List in the Join statement are not the real names(or Urls) of the list. The next Join statement is equal to the above one:

End.
Labels:

Comments

Post a Comment

Popular posts from this blog

ClientPeoplePicker in SharePoint 2013

Start using SharePoint 2013 I noticed that the way for selecting people or groups is changed. The new way is simple – just ‘Enter name or email address’ without any icons for ‘Check Names’ or ‘Browse’. I guess that the PeoplePicker is changed but NO. PeoplePicker sitll has the same functionality as before. There is a new control called ClientPeoplePicker . How to use it: 1. Add this references <% @ Register TagPrefix ="wssawc" Namespace ="Microsoft.SharePoint.WebControls" Assembly ="Microsoft.SharePoint, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %> 2. Add the following control declaration       < wssawc : ClientPeoplePicker          Required ="true"          ValidationEnabled ="true"          ID ="peoplePicker"          runat ="server"          InitialHelpText =" <% $Resources:wss,aclinv_PickerIntialHelperText %> "          VisibleSu
14 comments
Read more

Error: A duplicate field name "xxxx" was found

Soon I have some experience with migrating solution from SharePoint 2010 to SharePoint 2013. The migration was on 3 steps. First one was just to copy the custom fields, content types and list definitions from the old VS 2010 project into a new VS 2012 project for SharePoint 2013. Looks like pretty simple task but ….. The problem:  Error “ A duplicate field name "xxxx" was found ” when the feature for provisioning the fields and content types is activated. The solution: Review the field definitions and make sure no field has Name property equal to some of the ‘reserved’ values. Explanations: In SharePoint 2010 there was no problem to have these fields as a definition of custom fields: < Field     Type = " Note "     ID = " {893FB9BC-2DA4-4451-A1D1-88010CDC6A08} "     Name = " Description "     StaticName = " Description "     DisplayName = " Description 1 "     Required = " FALSE "     MaxL
1 comment
Read more

Using SharePoint Attachments' controls with elevated security

The scenario:  Create custom forms for Add/Edit/Delete items to a list which is not accessible by the regular users. The forms should support working with attachments. The problem: The default SharePoint controls don’t work if the current user doesn’t have access to the list items. The solution: Use custom code for generating the SharePoint default HTML so the out-of-the-box javascript works correct. Explanations:  The scenario occurs when some list need to be hidden form the users. The list has broken security inheritance and users can't navigate directly to it. Working with its fields requires crating web warts or application pages with appropriate controls on them and server-side code running under elevated privileges. The issue here is that the OOTB (out-of-the-box) controls for Attachments don't work if the user doesn’t have access to the related list item. The OOTB controls are: AttachmentsField , AttachmentUpload , AttachmentButton . There is a lot of code
1 comment
Read more