Post
Ask Question

Requirement using Lambda Expression

Requirement using Lambda Expression:

  1. Get distinct values from SharePoint list between two date range values.
  2. Find Count, Average, Sum from SharePoint based on above distinct values.
  3. Join two lists using lambda expression.

Consider two lists:

  1. Feedback Report
  2. Projects

  1. using (SPSite oSite = new SPSite(SPContext.Current.Site.Url))   
  2. {  
  3.     using (SPWeb web = oSite.OpenWeb())  
  4.     {  
  5.         SPList ResultList = web.Lists.Cast<SPList>().FirstOrDefault(list => list.RootFolder.Name == "Feedback Report");  
  6.         SPList ProjectstList = web.Lists["Projects"];  
  7.         IEnumerable<object> itemsDistinct = ResultList.Items.Cast<SPListItem>()  
  8.         .Where(item => Convert.ToString(item["Project Name"]) != "" &&  
  9.         (Convert.ToDateTime(Convert.ToDateTime(item["Created"]).ToShortDateString()) >= Convert.ToDateTime(dtStartdate.ToShortDateString())  
  10.         && Convert.ToDateTime(Convert.ToDateTime(item["Created"]).ToShortDateString()) <= Convert.ToDateTime(dtEnddate.ToShortDateString())))  
  11.         .Select(item => item["Project ID"])  
  12.         .Distinct();  
  13.   
  14.         using (IEnumerator<object> enumerator = itemsDistinct.GetEnumerator())  
  15.         {  
  16.             while (enumerator.MoveNext())  
  17.             {  
  18.                 Int32 iProjectID = Convert.ToInt32(enumerator.Current.ToString());  
  19.                   
  20.                 Int32 RatingCount = ResultList.Items.Cast<SPListItem>()  
  21.                 .Where(item => Convert.ToInt32(item["Project ID"]) == iProjectID)  
  22.                 .Count();  
  23.   
  24.                 double OverallAverage = ResultList.Items.Cast<SPListItem>()  
  25.                 .Where(item => Convert.ToInt32(item["Project ID"]) == iProjectID)  
  26.                 .Average(t => Convert.ToDouble(t["Overall"]));  
  27.                   
  28.                 double OverallSum = ResultList.Items.Cast<SPListItem>()  
  29.                 .Where(item => Convert.ToInt32(item["Project ID"]) == iProjectID)  
  30.                 .Sum(t => Convert.ToDouble(t["Overall"]));         
  31.   
  32.                 //join with projects list  
  33.                 IEnumerable<SPList> projs = ProjectstList.Items.Cast<SPListItem>()  
  34.                 .Where(item => Convert.ToInt32(item["ID"]) == iProjectID)  
  35.                 .Select(item =>  
  36.                      {  
  37.                          ProjectName = Convert.ToString(item["Project Title"]);  
  38.                          ProjectManager = Convert.ToString(item["Project Manager"]);  
  39.                      });  
  40.   
  41.                 IEnumerator<object> icnumProjs = projs.GetEnumerator();  
  42.             }  
  43.   
  44.             Int32 TotalRatingCount = ResultList.Items.Cast<SPListItem>()  
  45.             .Where(item => Convert.ToString(item["Project Name"]) != "")  
  46.             .Count();  
  47.   
  48.             double TotalOverallAverage = ResultList.Items.Cast<SPListItem>()  
  49.             .Where(item => Convert.ToString(item["Project Name"]) != "")  
  50.             .Average(t => Convert.ToDouble(t["Overall"]));  
  51.               
  52.             double TotalOverallSum = ResultList.Items.Cast<SPListItem>()  
  53.             .Where(item => Convert.ToString(item["Project Name"]) != "")  
  54.             .Sum(t => Convert.ToDouble(t["Overall"]));  
  55.         }  
  56.     }  
  57. }