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)
- {
-
-
- 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";
-
-
- string[] viewdFields = new string[] { "Title", "ManagerID", "FirstName", "LastName", "Address", "PhoneNo", "Pincode" };
-
- foreach (var f in viewdFields)
- {
- ViewdFieldsQuery += string.Format("<FieldRef Name='{0}' />", f);
- }
-
-
-
- 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);
- }
-
-
-
-
-
-
-
-
-
-
- JoinQuery += "<Join Type='LEFT' ListAlias='" + joinListTitle + "'>" +
- "<Eq>" +
- "<FieldRef Name='" + joinFieldName + "' RefType='ID' />" +
- "<FieldRef List='" + joinListTitle + "' Name='ID' />" +
- "</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 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)
- {
-
-
- 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";
-
-
- string[] viewdFields = new string[] { "Title", "ManagerID", "FirstName", "LastName", "Address", "PhoneNo", "Pincode" };
-
- foreach (var f in viewdFields)
- {
- ViewdFieldsQuery += string.Format("<FieldRef Name='{0}' />", f);
- }
-
-
-
- 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);
- }
-
-
-
-
-
-
-
-
-
-
- JoinQuery += "<Join Type='LEFT' ListAlias='" + joinListTitle + "'>" +
- "<Eq>" +
- "<FieldRef Name='" + joinFieldName + "' RefType='ID' />" +
- "<FieldRef List='" + joinListTitle + "' Name='ID' />" +
- "</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();
-
-
- };
- }
-
- 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.