Posted by: R Manimaran | May 20, 2011

Get Distinct Values from a SharePoint List along with Where Condition

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


SPList myList = web.Lists.Cast<SPList>().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

IEnumerable<object> itemsDistinct = myList.Items.Cast<SPListItem>()
.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.

IEnumerable<object> itemsDistinctWithFilter = myList.Items.Cast<SPListItem>()
.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

IEnumerable<object> itemsDistinctWithFilter = myList.Items.Cast<SPListItem>()
.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

IEnumerable<object> itemsDistinctWithFilter = myList.Items.Cast<SPListItem>()
.Where(item => Convert.ToInt32(item["Sales"]) > 50)
.Select(item => item["Title"])
.Distinct()
.OrderByDescending(item=>item);

Now the Output is

USA

IND

AUS

Advertisements

Responses

  1. Beautiful! You are the best!

  2. thx !

  3. Hi there! Do you use Twitter? I’d like to follow you if that would be okay. I’m definitely enjoying your
    blog and look forward to new updates.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: