SharePoint Joins

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.