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.

Comments

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

<%@RegisterTagPrefix="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%>" VisibleSuggestions="3" Rows="1" AllowMultipleEntities="false" CssClass

The column name that you entered is already in use or reserved. Choose another name.

The problem: 
You want to create column with a specific name in SharePoint but SharePoint gives you the message "The column name that you entered is already in use or reserved. Choose another name."



Why this is a problem?
Because you need to create custom columns and content types using the default SharePoint interface.
And the business users have to see properly named columns - for example "Description", not "My Description".

Solution:
It is important to know something technical about SharePoint (versions Office 365, 2013, 2010):
 - it supports columns with same Display Names.
 - you can’t have columns with same Internal names

Let’s implement the following common scenario:
You are creating a SharePoint customization and you need the following column:
   Type: Multiple lines of text
   Internal Name: MyProjectDescription
   Display Name: Description

 Here is the correct way to achieve it:
1. Create the column MyProjectDescription.
This will create column with I…

Office 365 Migration API: how to migrate the taxonomy metadata

If you wander whether the Office 365 Migration API supports migration of taxonomy metadata, the answer is YES. The API supports it but there isn't good documentation about how to modify your xml files after the command ConvertTo-SPOMigrationTargetedPackage.

In this post, I'm going to show you the approach. I assume that you have base knowledge of how to use the Migration API.

The scenario I'm going to use is simple - migrate single Document library with single document.
The document has one taxonomy column (MyTaxonomy) and three versions:

Here are the steps:

Step 1: Export the document library

Export-SPWeb "https://portal.cosingens.com/" ` -ItemUrl "/Shared Documents" ` -Path "\\cos-dev-03\export1\Export" ` -NoFileCompr -IncludeVersions 4
This command will export the document library and will create the initial package.
The command is not part of the Migration API. It is well know since SharePoint 2010 and is part of the server side obje…