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

SharePoint App settings

ListJoins.aspx

  1. <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ListJoins.aspx.cs" Inherits="CamlQueryWeb.Pages.ListJoins" %>
  3. <!DOCTYPE html>
  5. <html xmlns="http://www.w3.org/1999/xhtml">
  6. <head runat="server">
  7. <title></title>
  8. </head>
  9. <body>
  10. <form id="form1" runat="server">
  11. <div>
  12. <br />
  13. <br />
  14. <div>
  15. ContactDetails List
  16. <asp:GridView ID="grdContactDetails" runat="server"></asp:GridView>
  17. </div>
  18. <br />
  19. <br />
  20. <div>
  21. ProjectDetails List
  22. <asp:GridView ID="grdProjectDetails" runat="server"></asp:GridView>
  23. </div>
  24. <br />
  25. <br />
  26. <div>
  27. [ ProjectDetails left join ContactDetails ]
  28. <asp:GridView ID="grdListJoin" runat="server"></asp:GridView>
  29. </div>
  30. </div>
  31. </form>
  32. </body>
  33. </html>
ListJoins.aspx.cs code
  1. protected void Page_Load(object sender, EventArgs e)
  2. {
  3. // The following code gets the client context and Title property by using TokenHelper.
  4. // To access other properties, the app may need to request permissions on the host web.
  5. var spContext = SharePointContextProvider.Current.GetSharePointContext(Context);
  7. using (var clientContext = spContext.CreateUserClientContextForSPHost())
  8. {
  9. clientContext.Load(clientContext.Web, web => web.Title);
  10. clientContext.ExecuteQuery();
  11. Response.Write(clientContext.Web.Title);
  12. }
  14. JoinOperation();
  15. }
Step 1: Bind ContactDetails Grid with List Data
  1. List ContactDetailsList = clientContext.Web.Lists.GetByTitle("ContactDetails");
  3. CamlQuery Query2 = CamlQuery.CreateAllItemsQuery();
  4. Query2.ViewXml = string.Format("<View><Query></Query></View>");
  6. Microsoft.SharePoint.Client.ListItemCollection ContactsCollListItem = ContactDetailsList.GetItems(Query2);
  7. clientContext.Load(ContactsCollListItem);
  8. clientContext.ExecuteQuery();
  10. DataTable dtContacts = new DataTable("Contacts");
  11. dtContacts.Columns.Add("ID");
  12. dtContacts.Columns.Add("FirstName");
  13. dtContacts.Columns.Add("LastName");
  14. dtContacts.Columns.Add("Address");
  15. dtContacts.Columns.Add("PhoneNo");
  16. dtContacts.Columns.Add("Pincode");
  19. foreach (Microsoft.SharePoint.Client.ListItem item in ContactsCollListItem)
  20. {
  21. DataRow dr = dtContacts.NewRow();
  23. dr["ID"] = item["ID"];
  24. dr["FirstName"] = item["FirstName"];
  25. dr["LastName"] = item["LastName"];
  26. dr["Address"] = item["Address"];
  27. dr["PhoneNo"] = item["PhoneNo"];
  28. dr["Pincode"] = item["Pincode"];
  30. dtContacts.Rows.Add(dr);
  31. }
  33. grdContactDetails.DataSource = dtContacts;
  34. grdContactDetails.DataBind();
Step 2: Bind ProjectDetails Grid with List Data
  1. List ProjectDetailsList = clientContext.Web.Lists.GetByTitle("ProjectDetails");
  2. CamlQuery Query1 = CamlQuery.CreateAllItemsQuery();
  3. Query1.ViewXml = string.Format("<View><Query></Query></View>");
  5. Microsoft.SharePoint.Client.ListItemCollection ProjectCollListItem = ProjectDetailsList.GetItems(Query1);
  6. clientContext.Load(ProjectCollListItem);
  7. clientContext.ExecuteQuery();
  9. DataTable dtProjects = new DataTable("Projects");
  10. dtProjects.Columns.Add("Title");
  11. dtProjects.Columns.Add("ManagerID");
  14. foreach (Microsoft.SharePoint.Client.ListItem item in ProjectCollListItem)
  15. {
  16. DataRow dr = dtProjects.NewRow();
  17. FieldLookupValue Managerlkp = (FieldLookupValue)item["ManagerID"];
  19. dr["Title"] = item["Title"];
  20. dr["ManagerID"] = CheckLookupValue(Managerlkp);
  22. dtProjects.Rows.Add(dr);
  23. }
  25. grdProjectDetails.DataSource = dtProjects;
  26. grdProjectDetails.DataBind();
Step 3: Dynamic List Join Query Building
  1. CamlQuery camlQuery = CamlQuery.CreateAllItemsQuery();
  3. string QueryStr = "";
  4. string JoinQuery = "";
  5. string ViewdFieldsQuery = "";
  6. string ProjectedFieldsQuery = "";
  8. string joinListTitle = "ContactDetails";
  9. string joinFieldName = "ManagerID";
  11. /************** viewdFields ***************/
  12. string[] viewdFields = new string[] { "Title", "ManagerID", "FirstName", "LastName", "Address", "PhoneNo", "Pincode" };
  14. foreach (var f in viewdFields)
  15. {
  16. ViewdFieldsQuery += string.Format("<FieldRef Name='{0}' />", f);
  17. }
  19. /************** projectedFields ***************/
  21. string[] projectedFields = new string[] { "FirstName", "LastName", "Address", "PhoneNo", "Pincode" };
  23. foreach (var f in projectedFields)
  24. {
  25. ProjectedFieldsQuery += string.Format("<Field Name='{1}' Type='Lookup' List='{0}' ShowField='{1}' />", joinListTitle, f);
  26. }
  28. /******************* Joins ************************/
  30. //JoinQuery += "<Join Type='INNER' ListAlias='ContactDetails'>" +
  31. // "<Eq>" +
  32. // "<FieldRef Name='ManagerID' RefType='ID' />" +
  33. // "<FieldRef List='ContactDetails' Name='ID' />" +
  34. // "</Eq>" +
  35. // "</Join>";
  37. JoinQuery += "<Join Type='LEFT' ListAlias='" + joinListTitle + "'>" + // ContactDetails
  38. "<Eq>" +
  39. "<FieldRef Name='" + joinFieldName + "' RefType='ID' />" + // ManagerID
  40. "<FieldRef List='" + joinListTitle + "' Name='ID' />" + // ContactDetails
  41. "</Eq>" +
  42. "</Join>";
  44. /**************************************************/
  46. QueryStr = @"<View>" +
  47. "<ViewFields>" +
  48. ViewdFieldsQuery +
  49. "</ViewFields>" +
  50. "<Joins>" +
  51. JoinQuery +
  52. "</Joins>" +
  53. "<ProjectedFields>" +
  54. ProjectedFieldsQuery +
  55. "</ProjectedFields>" +
  56. "</View>";
  58. camlQuery.ViewXml = string.Format(QueryStr);
  60. List oList = clientContext.Web.Lists.GetByTitle("ProjectDetails");
  62. Microsoft.SharePoint.Client.ListItemCollection collListItem = oList.GetItems(camlQuery);
  63. clientContext.Load(collListItem);
  64. clientContext.ExecuteQuery();
  66. int itemcount = collListItem.Count;
  68. DataTable dt = new DataTable("Projects");
  69. dt.Columns.Add("Title");
  70. dt.Columns.Add("ManagerID");
  71. dt.Columns.Add("FirstName");
  72. dt.Columns.Add("LastName");
  73. dt.Columns.Add("Address");
  74. dt.Columns.Add("PhoneNo");
  75. dt.Columns.Add("Pincode");
  77. foreach (Microsoft.SharePoint.Client.ListItem item in collListItem)
  78. {
  79. DataRow dr = dt.NewRow();
  81. FieldLookupValue Managerlkp = (FieldLookupValue)item["ManagerID"];
  82. FieldLookupValue FNamelkp = (FieldLookupValue)item["FirstName"];
  83. FieldLookupValue LNamelkp = (FieldLookupValue)item["LastName"];
  84. FieldLookupValue Addresslkp = (FieldLookupValue)item["Address"];
  85. FieldLookupValue PhoneNolkp = (FieldLookupValue)item["PhoneNo"];
  86. FieldLookupValue Pincodelkp = (FieldLookupValue)item["Pincode"];
  88. dr["Title"] = item["Title"];
  89. dr["ManagerID"] = CheckLookupValue(Managerlkp);
  90. dr["FirstName"] = CheckLookupValue(FNamelkp);
  91. dr["LastName"] = CheckLookupValue(LNamelkp);
  92. dr["Address"] = CheckLookupValue(Addresslkp);
  93. dr["PhoneNo"] = CheckLookupValue(PhoneNolkp);
  94. dr["Pincode"] = CheckLookupValue(Pincodelkp);
  96. dt.Rows.Add(dr);
  97. }
  99. grdListJoin.DataSource = dt;
  100. grdListJoin.DataBind();
SharePoint Lookup value checking function
  1. public string CheckLookupValue(FieldLookupValue LookupField)
  2. {
  3. string Ans = "";
  5. if (LookupField == null)
  6. Ans = "";
  7. else
  8. Ans = LookupField.LookupValue;
  10. return Ans;
  11. }
Output
output

Source Code
  1. using Microsoft.SharePoint.Client;
  2. using System;
  3. using System.Collections.Generic;
  4. using System.Data;
  5. using System.Linq;
  6. using System.Web;
  7. using System.Web.UI;
  8. using System.Web.UI.WebControls;
  10. namespace CamlQueryWeb.Pages
  11. {
  12. public partial class ListJoins : System.Web.UI.Page
  13. {
  14. protected void Page_PreInit(object sender, EventArgs e)
  15. {
  16. Uri redirectUrl;
  17. switch (SharePointContextProvider.CheckRedirectionStatus(Context, out redirectUrl))
  18. {
  19. case RedirectionStatus.Ok:
  20. return;
  21. case RedirectionStatus.ShouldRedirect:
  22. Response.Redirect(redirectUrl.AbsoluteUri, endResponse: true);
  23. break;
  24. case RedirectionStatus.CanNotRedirect:
  25. Response.Write("An error occurred while processing your request.");
  26. Response.End();
  27. break;
  28. }
  29. }
  31. protected void Page_Load(object sender, EventArgs e)
  32. {
  33. // The following code gets the client context and Title property by using TokenHelper.
  34. // To access other properties, the app may need to request permissions on the host web.
  35. var spContext = SharePointContextProvider.Current.GetSharePointContext(Context);
  37. using (var clientContext = spContext.CreateUserClientContextForSPHost())
  38. {
  39. clientContext.Load(clientContext.Web, web => web.Title);
  40. clientContext.ExecuteQuery();
  41. Response.Write(clientContext.Web.Title);
  42. }
  44. JoinOperation();
  45. }
  47. public void JoinOperation()
  48. {
  49. var spContext = SharePointContextProvider.Current.GetSharePointContext(Context);
  51. using (var clientContext = spContext.CreateUserClientContextForSPHost())
  52. {
  53. List ContactDetailsList = clientContext.Web.Lists.GetByTitle("ContactDetails");
  54. CamlQuery Query2 = CamlQuery.CreateAllItemsQuery();
  55. Query2.ViewXml = string.Format("<View><Query></Query></View>");
  57. Microsoft.SharePoint.Client.ListItemCollection ContactsCollListItem = ContactDetailsList.GetItems(Query2);
  58. clientContext.Load(ContactsCollListItem);
  59. clientContext.ExecuteQuery();
  61. DataTable dtContacts = new DataTable("Contacts");
  62. dtContacts.Columns.Add("ID");
  63. dtContacts.Columns.Add("FirstName");
  64. dtContacts.Columns.Add("LastName");
  65. dtContacts.Columns.Add("Address");
  66. dtContacts.Columns.Add("PhoneNo");
  67. dtContacts.Columns.Add("Pincode");
  70. foreach (Microsoft.SharePoint.Client.ListItem item in ContactsCollListItem)
  71. {
  72. DataRow dr = dtContacts.NewRow();
  74. dr["ID"] = item["ID"];
  75. dr["FirstName"] = item["FirstName"];
  76. dr["LastName"] = item["LastName"];
  77. dr["Address"] = item["Address"];
  78. dr["PhoneNo"] = item["PhoneNo"];
  79. dr["Pincode"] = item["Pincode"];
  81. dtContacts.Rows.Add(dr);
  82. }
  84. grdContactDetails.DataSource = dtContacts;
  85. grdContactDetails.DataBind();
  87. /***********************************************************************/
  89. List ProjectDetailsList = clientContext.Web.Lists.GetByTitle("ProjectDetails");
  90. CamlQuery Query1 = CamlQuery.CreateAllItemsQuery();
  91. Query1.ViewXml = string.Format("<View><Query></Query></View>");
  93. Microsoft.SharePoint.Client.ListItemCollection ProjectCollListItem = ProjectDetailsList.GetItems(Query1);
  94. clientContext.Load(ProjectCollListItem);
  95. clientContext.ExecuteQuery();
  97. DataTable dtProjects = new DataTable("Projects");
  98. dtProjects.Columns.Add("Title");
  99. dtProjects.Columns.Add("ManagerID");
  102. foreach (Microsoft.SharePoint.Client.ListItem item in ProjectCollListItem)
  103. {
  104. DataRow dr = dtProjects.NewRow();
  105. FieldLookupValue Managerlkp = (FieldLookupValue)item["ManagerID"];
  107. dr["Title"] = item["Title"];
  108. dr["ManagerID"] = CheckLookupValue(Managerlkp);
  110. dtProjects.Rows.Add(dr);
  111. }
  113. grdProjectDetails.DataSource = dtProjects;
  114. grdProjectDetails.DataBind();
  116. /***********************************************************************/
  118. CamlQuery camlQuery = CamlQuery.CreateAllItemsQuery();
  120. string QueryStr = "";
  121. string JoinQuery = "";
  122. string ViewdFieldsQuery = "";
  123. string ProjectedFieldsQuery = "";
  125. string joinListTitle = "ContactDetails";
  126. string joinFieldName = "ManagerID";
  128. /************** viewdFields ***************/
  129. string[] viewdFields = new string[] { "Title", "ManagerID", "FirstName", "LastName", "Address", "PhoneNo", "Pincode" };
  131. foreach (var f in viewdFields)
  132. {
  133. ViewdFieldsQuery += string.Format("<FieldRef Name='{0}' />", f);
  134. }
  136. /************** projectedFields ***************/
  138. string[] projectedFields = new string[] { "FirstName", "LastName", "Address", "PhoneNo", "Pincode" };
  140. foreach (var f in projectedFields)
  141. {
  142. ProjectedFieldsQuery += string.Format("<Field Name='{1}' Type='Lookup' List='{0}' ShowField='{1}' />", joinListTitle, f);
  143. }
  145. /******************* Joins ************************/
  147. //JoinQuery += "<Join Type='INNER' ListAlias='ContactDetails'>" +
  148. // "<Eq>" +
  149. // "<FieldRef Name='ManagerID' RefType='ID' />" +
  150. // "<FieldRef List='ContactDetails' Name='ID' />" +
  151. // "</Eq>" +
  152. // "</Join>";
  154. JoinQuery += "<Join Type='LEFT' ListAlias='" + joinListTitle + "'>" + // ContactDetails
  155. "<Eq>" +
  156. "<FieldRef Name='" + joinFieldName + "' RefType='ID' />" + // ManagerID
  157. "<FieldRef List='" + joinListTitle + "' Name='ID' />" + // ContactDetails
  158. "</Eq>" +
  159. "</Join>";
  161. /**************************************************/
  163. QueryStr = @"<View>" +
  164. "<ViewFields>" +
  165. ViewdFieldsQuery +
  166. "</ViewFields>" +
  167. "<Joins>" +
  168. JoinQuery +
  169. "</Joins>" +
  170. "<ProjectedFields>" +
  171. ProjectedFieldsQuery +
  172. "</ProjectedFields>" +
  173. "</View>";
  175. camlQuery.ViewXml = string.Format(QueryStr);
  177. List oList = clientContext.Web.Lists.GetByTitle("ProjectDetails");
  179. Microsoft.SharePoint.Client.ListItemCollection collListItem = oList.GetItems(camlQuery);
  180. clientContext.Load(collListItem);
  181. clientContext.ExecuteQuery();
  183. int itemcount = collListItem.Count;
  185. DataTable dt = new DataTable("Projects");
  186. dt.Columns.Add("Title");
  187. dt.Columns.Add("ManagerID");
  188. dt.Columns.Add("FirstName");
  189. dt.Columns.Add("LastName");
  190. dt.Columns.Add("Address");
  191. dt.Columns.Add("PhoneNo");
  192. dt.Columns.Add("Pincode");
  194. foreach (Microsoft.SharePoint.Client.ListItem item in collListItem)
  195. {
  196. DataRow dr = dt.NewRow();
  198. FieldLookupValue Managerlkp = (FieldLookupValue)item["ManagerID"];
  199. FieldLookupValue FNamelkp = (FieldLookupValue)item["FirstName"];
  200. FieldLookupValue LNamelkp = (FieldLookupValue)item["LastName"];
  201. FieldLookupValue Addresslkp = (FieldLookupValue)item["Address"];
  202. FieldLookupValue PhoneNolkp = (FieldLookupValue)item["PhoneNo"];
  203. FieldLookupValue Pincodelkp = (FieldLookupValue)item["Pincode"];
  205. dr["Title"] = item["Title"];
  206. dr["ManagerID"] = CheckLookupValue(Managerlkp);
  207. dr["FirstName"] = CheckLookupValue(FNamelkp);
  208. dr["LastName"] = CheckLookupValue(LNamelkp);
  209. dr["Address"] = CheckLookupValue(Addresslkp);
  210. dr["PhoneNo"] = CheckLookupValue(PhoneNolkp);
  211. dr["Pincode"] = CheckLookupValue(Pincodelkp);
  213. dt.Rows.Add(dr);
  214. }
  216. grdListJoin.DataSource = dt;
  217. grdListJoin.DataBind();
  219. //Label1.Text = Convert.ToString(itemcount);
  220. };
  221. }
  223. public string CheckLookupValue(FieldLookupValue LookupField)
  224. {
  225. string Ans = "";
  227. if (LookupField == null)
  228. Ans = "";
  229. else
  230. Ans = LookupField.LookupValue;
  232. return Ans;
  233. }
  235. }
  236. }
Thank you! Please mention your queries if you have any in the comments section below.