CAML Query List Join Operation With SharePoint App Model

SharePoint Joins

Only Inner & Left joins are permitted.
Joins can only be defined on lookup columns.
Projected fields cannot be used to sort in the view.

SharePoint List

We are having two lists for this example. ContactDetails and ProjectDetails.

SharePoint App settings to read List Data

ListJoins.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ListJoins.aspx.cs" Inherits="CamlQueryWeb.Pages.ListJoins" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<br />
<br />
<div>
ContactDetails List
<asp:GridView ID="grdContactDetails" runat="server"></asp:GridView>
</div>
<br />
<br />
<div>
ProjectDetails List
<asp:GridView ID="grdProjectDetails" runat="server"></asp:GridView>
</div>
<br />
<br />
<div>
[ ProjectDetails left join ContactDetails ]
<asp:GridView ID="grdListJoin" runat="server"></asp:GridView>
</div>
</div>
</form>
</body>
</html>

ListJoins.aspx.cs code

protected void Page_Load(object sender, EventArgs e)
{
// The following code gets the client context and Title property by using TokenHelper.
// To access other properties, the app may need to request permissions on the host web.
var spContext = SharePointContextProvider.Current.GetSharePointContext(Context);
using (var clientContext = spContext.CreateUserClientContextForSPHost())
{
clientContext.Load(clientContext.Web, web => web.Title);
clientContext.ExecuteQuery();
Response.Write(clientContext.Web.Title);
}
JoinOperation();
}

Step 1: Bind ContactDetails Grid with List Data

List ContactDetailsList = clientContext.Web.Lists.GetByTitle("ContactDetails");
CamlQuery Query2 = CamlQuery.CreateAllItemsQuery();
Query2.ViewXml = string.Format("<View><Query></Query></View>");
Microsoft.SharePoint.Client.ListItemCollection ContactsCollListItem = ContactDetailsList.GetItems(Query2);
clientContext.Load(ContactsCollListItem);
clientContext.ExecuteQuery();
DataTable dtContacts = new DataTable("Contacts");
dtContacts.Columns.Add("ID");
dtContacts.Columns.Add("FirstName");
dtContacts.Columns.Add("LastName");
dtContacts.Columns.Add("Address");
dtContacts.Columns.Add("PhoneNo");
dtContacts.Columns.Add("Pincode");
foreach (Microsoft.SharePoint.Client.ListItem item in ContactsCollListItem)
{
DataRow dr = dtContacts.NewRow();
dr["ID"] = item["ID"];
dr["FirstName"] = item["FirstName"];
dr["LastName"] = item["LastName"];
dr["Address"] = item["Address"];
dr["PhoneNo"] = item["PhoneNo"];
dr["Pincode"] = item["Pincode"];
dtContacts.Rows.Add(dr);
}
grdContactDetails.DataSource = dtContacts;
grdContactDetails.DataBind();

Step 2: Bind ProjectDetails Grid with List Data

List ProjectDetailsList = clientContext.Web.Lists.GetByTitle("ProjectDetails");
CamlQuery Query1 = CamlQuery.CreateAllItemsQuery();
Query1.ViewXml = string.Format("<View><Query></Query></View>");
Microsoft.SharePoint.Client.ListItemCollection ProjectCollListItem = ProjectDetailsList.GetItems(Query1);
clientContext.Load(ProjectCollListItem);
clientContext.ExecuteQuery();
DataTable dtProjects = new DataTable("Projects");
dtProjects.Columns.Add("Title");
dtProjects.Columns.Add("ManagerID");
foreach (Microsoft.SharePoint.Client.ListItem item in ProjectCollListItem)
{
DataRow dr = dtProjects.NewRow();
FieldLookupValue Managerlkp = (FieldLookupValue)item["ManagerID"];
dr["Title"] = item["Title"];
dr["ManagerID"] = CheckLookupValue(Managerlkp);
dtProjects.Rows.Add(dr);
}
grdProjectDetails.DataSource = dtProjects;
grdProjectDetails.DataBind();

Step 3: Dynamic List Join Query Building

CamlQuery camlQuery = CamlQuery.CreateAllItemsQuery();
string QueryStr = "";
string JoinQuery = "";
string ViewdFieldsQuery = "";
string ProjectedFieldsQuery = "";
string joinListTitle = "ContactDetails";
string joinFieldName = "ManagerID";
/************** viewdFields ***************/
string[] viewdFields = new string[] { "Title", "ManagerID", "FirstName", "LastName", "Address", "PhoneNo", "Pincode" };
foreach (var f in viewdFields)
{
ViewdFieldsQuery += string.Format("<FieldRef Name='{0}' />", f);
}
/************** projectedFields ***************/
string[] projectedFields = new string[] { "FirstName", "LastName", "Address", "PhoneNo", "Pincode" };
foreach (var f in projectedFields)
{
ProjectedFieldsQuery += string.Format("<Field Name='{1}' Type='Lookup' List='{0}' ShowField='{1}' />", joinListTitle, f);
}
/******************* Joins ************************/
//JoinQuery += "<Join Type='INNER' ListAlias='ContactDetails'>" +
// "<Eq>" +
// "<FieldRef Name='ManagerID' RefType='ID' />" +
// "<FieldRef List='ContactDetails' Name='ID' />" +
// "</Eq>" +
// "</Join>";
JoinQuery += "<Join Type='LEFT' ListAlias='" + joinListTitle + "'>" + // ContactDetails
"<Eq>" +
"<FieldRef Name='" + joinFieldName + "' RefType='ID' />" + // ManagerID
"<FieldRef List='" + joinListTitle + "' Name='ID' />" + // ContactDetails
"</Eq>" +
"</Join>";
/**************************************************/
QueryStr = @"<View>" +
"<ViewFields>" +
ViewdFieldsQuery +
"</ViewFields>" +
"<Joins>" +
JoinQuery +
"</Joins>" +
"<ProjectedFields>" +
ProjectedFieldsQuery +
"</ProjectedFields>" +
"</View>";
camlQuery.ViewXml = string.Format(QueryStr);
List oList = clientContext.Web.Lists.GetByTitle("ProjectDetails");
Microsoft.SharePoint.Client.ListItemCollection collListItem = oList.GetItems(camlQuery);
clientContext.Load(collListItem);
clientContext.ExecuteQuery();
int itemcount = collListItem.Count;
DataTable dt = new DataTable("Projects");
dt.Columns.Add("Title");
dt.Columns.Add("ManagerID");
dt.Columns.Add("FirstName");
dt.Columns.Add("LastName");
dt.Columns.Add("Address");
dt.Columns.Add("PhoneNo");
dt.Columns.Add("Pincode");
foreach (Microsoft.SharePoint.Client.ListItem item in collListItem)
{
DataRow dr = dt.NewRow();
FieldLookupValue Managerlkp = (FieldLookupValue)item["ManagerID"];
FieldLookupValue FNamelkp = (FieldLookupValue)item["FirstName"];
FieldLookupValue LNamelkp = (FieldLookupValue)item["LastName"];
FieldLookupValue Addresslkp = (FieldLookupValue)item["Address"];
FieldLookupValue PhoneNolkp = (FieldLookupValue)item["PhoneNo"];
FieldLookupValue Pincodelkp = (FieldLookupValue)item["Pincode"];
dr["Title"] = item["Title"];
dr["ManagerID"] = CheckLookupValue(Managerlkp);
dr["FirstName"] = CheckLookupValue(FNamelkp);
dr["LastName"] = CheckLookupValue(LNamelkp);
dr["Address"] = CheckLookupValue(Addresslkp);
dr["PhoneNo"] = CheckLookupValue(PhoneNolkp);
dr["Pincode"] = CheckLookupValue(Pincodelkp);
dt.Rows.Add(dr);
}
grdListJoin.DataSource = dt;
grdListJoin.DataBind();

SharePoint Lookup value checking function

public string CheckLookupValue(FieldLookupValue LookupField)
{
string Ans = "";
if (LookupField == null)
Ans = "";
else
Ans = LookupField.LookupValue;
return Ans;
}

Output
output

Source Code

using Microsoft.SharePoint.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace CamlQueryWeb.Pages
{
public partial class ListJoins : System.Web.UI.Page
{
protected void Page_PreInit(object sender, EventArgs e)
{
Uri redirectUrl;
switch (SharePointContextProvider.CheckRedirectionStatus(Context, out redirectUrl))
{
case RedirectionStatus.Ok:
return;
case RedirectionStatus.ShouldRedirect:
Response.Redirect(redirectUrl.AbsoluteUri, endResponse: true);
break;
case RedirectionStatus.CanNotRedirect:
Response.Write("An error occurred while processing your request.");
Response.End();
break;
}
}
protected void Page_Load(object sender, EventArgs e)
{
// The following code gets the client context and Title property by using TokenHelper.
// To access other properties, the app may need to request permissions on the host web.
var spContext = SharePointContextProvider.Current.GetSharePointContext(Context);
using (var clientContext = spContext.CreateUserClientContextForSPHost())
{
clientContext.Load(clientContext.Web, web => web.Title);
clientContext.ExecuteQuery();
Response.Write(clientContext.Web.Title);
}
JoinOperation();
}
public void JoinOperation()
{
var spContext = SharePointContextProvider.Current.GetSharePointContext(Context);
using (var clientContext = spContext.CreateUserClientContextForSPHost())
{
List ContactDetailsList = clientContext.Web.Lists.GetByTitle("ContactDetails");
CamlQuery Query2 = CamlQuery.CreateAllItemsQuery();
Query2.ViewXml = string.Format("<View><Query></Query></View>");
Microsoft.SharePoint.Client.ListItemCollection ContactsCollListItem = ContactDetailsList.GetItems(Query2);
clientContext.Load(ContactsCollListItem);
clientContext.ExecuteQuery();
DataTable dtContacts = new DataTable("Contacts");
dtContacts.Columns.Add("ID");
dtContacts.Columns.Add("FirstName");
dtContacts.Columns.Add("LastName");
dtContacts.Columns.Add("Address");
dtContacts.Columns.Add("PhoneNo");
dtContacts.Columns.Add("Pincode");
foreach (Microsoft.SharePoint.Client.ListItem item in ContactsCollListItem)
{
DataRow dr = dtContacts.NewRow();
dr["ID"] = item["ID"];
dr["FirstName"] = item["FirstName"];
dr["LastName"] = item["LastName"];
dr["Address"] = item["Address"];
dr["PhoneNo"] = item["PhoneNo"];
dr["Pincode"] = item["Pincode"];
dtContacts.Rows.Add(dr);
}
grdContactDetails.DataSource = dtContacts;
grdContactDetails.DataBind();
/***********************************************************************/
List ProjectDetailsList = clientContext.Web.Lists.GetByTitle("ProjectDetails");
CamlQuery Query1 = CamlQuery.CreateAllItemsQuery();
Query1.ViewXml = string.Format("<View><Query></Query></View>");
Microsoft.SharePoint.Client.ListItemCollection ProjectCollListItem = ProjectDetailsList.GetItems(Query1);
clientContext.Load(ProjectCollListItem);
clientContext.ExecuteQuery();
DataTable dtProjects = new DataTable("Projects");
dtProjects.Columns.Add("Title");
dtProjects.Columns.Add("ManagerID");
foreach (Microsoft.SharePoint.Client.ListItem item in ProjectCollListItem)
{
DataRow dr = dtProjects.NewRow();
FieldLookupValue Managerlkp = (FieldLookupValue)item["ManagerID"];
dr["Title"] = item["Title"];
dr["ManagerID"] = CheckLookupValue(Managerlkp);
dtProjects.Rows.Add(dr);
}
grdProjectDetails.DataSource = dtProjects;
grdProjectDetails.DataBind();
/***********************************************************************/
CamlQuery camlQuery = CamlQuery.CreateAllItemsQuery();
string QueryStr = "";
string JoinQuery = "";
string ViewdFieldsQuery = "";
string ProjectedFieldsQuery = "";
string joinListTitle = "ContactDetails";
string joinFieldName = "ManagerID";
/************** viewdFields ***************/
string[] viewdFields = new string[] { "Title", "ManagerID", "FirstName", "LastName", "Address", "PhoneNo", "Pincode" };
foreach (var f in viewdFields)
{
ViewdFieldsQuery += string.Format("<FieldRef Name='{0}' />", f);
}
/************** projectedFields ***************/
string[] projectedFields = new string[] { "FirstName", "LastName", "Address", "PhoneNo", "Pincode" };
foreach (var f in projectedFields)
{
ProjectedFieldsQuery += string.Format("<Field Name='{1}' Type='Lookup' List='{0}' ShowField='{1}' />", joinListTitle, f);
}
/******************* Joins ************************/
//JoinQuery += "<Join Type='INNER' ListAlias='ContactDetails'>" +
// "<Eq>" +
// "<FieldRef Name='ManagerID' RefType='ID' />" +
// "<FieldRef List='ContactDetails' Name='ID' />" +
// "</Eq>" +
// "</Join>";
JoinQuery += "<Join Type='LEFT' ListAlias='" + joinListTitle + "'>" + // ContactDetails
"<Eq>" +
"<FieldRef Name='" + joinFieldName + "' RefType='ID' />" + // ManagerID
"<FieldRef List='" + joinListTitle + "' Name='ID' />" + // ContactDetails
"</Eq>" +
"</Join>";
/**************************************************/
QueryStr = @"<View>" +
"<ViewFields>" +
ViewdFieldsQuery +
"</ViewFields>" +
"<Joins>" +
JoinQuery +
"</Joins>" +
"<ProjectedFields>" +
ProjectedFieldsQuery +
"</ProjectedFields>" +
"</View>";
camlQuery.ViewXml = string.Format(QueryStr);
List oList = clientContext.Web.Lists.GetByTitle("ProjectDetails");
Microsoft.SharePoint.Client.ListItemCollection collListItem = oList.GetItems(camlQuery);
clientContext.Load(collListItem);
clientContext.ExecuteQuery();
int itemcount = collListItem.Count;
DataTable dt = new DataTable("Projects");
dt.Columns.Add("Title");
dt.Columns.Add("ManagerID");
dt.Columns.Add("FirstName");
dt.Columns.Add("LastName");
dt.Columns.Add("Address");
dt.Columns.Add("PhoneNo");
dt.Columns.Add("Pincode");
foreach (Microsoft.SharePoint.Client.ListItem item in collListItem)
{
DataRow dr = dt.NewRow();
FieldLookupValue Managerlkp = (FieldLookupValue)item["ManagerID"];
FieldLookupValue FNamelkp = (FieldLookupValue)item["FirstName"];
FieldLookupValue LNamelkp = (FieldLookupValue)item["LastName"];
FieldLookupValue Addresslkp = (FieldLookupValue)item["Address"];
FieldLookupValue PhoneNolkp = (FieldLookupValue)item["PhoneNo"];
FieldLookupValue Pincodelkp = (FieldLookupValue)item["Pincode"];
dr["Title"] = item["Title"];
dr["ManagerID"] = CheckLookupValue(Managerlkp);
dr["FirstName"] = CheckLookupValue(FNamelkp);
dr["LastName"] = CheckLookupValue(LNamelkp);
dr["Address"] = CheckLookupValue(Addresslkp);
dr["PhoneNo"] = CheckLookupValue(PhoneNolkp);
dr["Pincode"] = CheckLookupValue(Pincodelkp);
dt.Rows.Add(dr);
}
grdListJoin.DataSource = dt;
grdListJoin.DataBind();
//Label1.Text = Convert.ToString(itemcount);
};
}
public string CheckLookupValue(FieldLookupValue LookupField)
{
string Ans = "";
if (LookupField == null)
Ans = "";
else
Ans = LookupField.LookupValue;
return Ans;
}
}
}

Thank you! Please mention your queries if you have any in the comments section below.

11 Comments

Join the conversation! Your thoughts help the community grow.

ajay kere9y ago
Good One But can you please tell me what if Contact details list contains any others tables lookup value then How can We display that lookup value ?
Santhakumar Munuswamy11y ago
Good one
Rupali Shinde11y ago
thanks
Supreeth J11y ago
nice
Mukesh Kumar11y ago
Great Job, Thanks
Nilesh Jadav11y ago
Great Good Work :)
Harshad Pansuriya11y ago
Nice one
Priyaranjan K S11y ago
I guess this a PHA implementation .
- Rupali Shinde11y ago
  Yes, it is Provider Hosted App Model
Priyaranjan K S11y ago
Nicely Implemented !
Vinodh Narayanan11y ago
Good.one