Saturday, June 24, 2017

Get Distinct Values from a SharePoint List along with Where Condition.

Copyright from: https://rmanimaran.wordpress.com/

Get Distinct Values from a SharePoint List along with Where Condition.
Q: I have a SharePoint List named Sales with the following values. Here Country field is having repeating country names. If I want to get the distinct values of the country from this how to get that.
Using List‘s method: GetDistinctFieldValues
1
2
3
4
5
6
7
8
9
10
11
SPList myList = web.Lists.Cast().FirstOrDefault(l => l.Title == "Sales");
if(myList!=null)
  {
   object[,] UniqueValues;
   uint UniqueValuesCount =   myList.GetDistinctFieldValues(myList.Fields[SPBuiltInFieldId.Title], out UniqueValues);
 
for (int i = 0; i < UniqueValuesCount; i++)
  {
    Console.WriteLine(UniqueValues.GetValue(0, i).ToString());
   }
}
The above code will print the following values
IND
AUS
CAN
USA
The above output can be get using the Lamda expression
1
2
3
4
5
6
7
8
9
10
IEnumerable<object> itemsDistinct = myList.Items.Cast()
.Select(item => item["Title"])
.Distinct();
using (IEnumerator<object> enumerator = itemsDistinct.GetEnumerator())
{
  while (enumerator.MoveNext())
   {
    Console.WriteLine(enumerator.Current.ToString());
   }
}
The above code also returns the distinct Country values from the Sales List.
Suppose I want to check a condition while getting a distinct count. Those country which meets that condition needs to be get displayed. This can be done in Lamda expression.
1
2
3
4
5
6
7
8
9
10
11
IEnumerable<object> itemsDistinctWithFilter = myList.Items.Cast()
.Where(item => Convert.ToInt32(item["Sales"]) > 50)
.Select(item => item["Title"])
.Distinct();
using (IEnumerator<object> enumerator = itemsDistinctWithFilter.GetEnumerator())
{
  while (enumerator.MoveNext())
   {
     Console.WriteLine(enumerator.Current.ToString());
   }
}
The above code will displays the following output.
IND
USA
AUS
Note: The GetDistinctFieldValues will returns the Country names in ascending order. But the Lambda we need to do the sort explicity.
Sorting in ascending
1
2
3
4
5
IEnumerable<object> itemsDistinctWithFilter = myList.Items.Cast()
.Where(item => Convert.ToInt32(item["Sales"]) > 50)
.Select(item => item["Title"])
.Distinct()
.OrderBy(item=>item);
Now the Output is
AUS
IND
USA
Sort in Descending
1
2
3
4
5
IEnumerable<object> itemsDistinctWithFilter = myList.Items.Cast()
.Where(item => Convert.ToInt32(item["Sales"]) > 50)
.Select(item => item["Title"])
.Distinct()
.OrderByDescending(item=>item);
Now the Output is
USA
IND
AUS