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