Sharepoint 2010, get the Distinct Values of a User Profile Property?

Question

Is there any way in SharePoint 2010 to get the Distinct values of a property accross all users? For example, I want a list of all Departments in the Profile Store.

In SQL I can do

SELECT DISTINCT(PropertyVal) FROM [ProfileDB].[dbo].[UserProfileValue]
WHERE PropertyID = 14

but accessing the SQL Database is a big no-no of course, so I wonder if there is something in the object model?

Note that accessing the User Information List on an SPSite is not good enough, as only a fraction of all users is in that list. I want to get it directly from the Profile Store.

Answer 1

The blog entry Sharepoint User Profiles describes how to get all user profiles from the central profile store. MSDN has a description of the UserProfileManager class, and configuring the user Profile store using the object model.

It seems in all cases a SPSite is needed to provide context. You should be able to get all profiles by passing in the URL of your sharepoint server. The UserProfileManager.UserProfiles collection provides access to all user profiles.

To get all distinct values of a profile property for all users, iterate over all profiles, pull out the required property value and add this to a hashset. At the end of iteration, the hashset holds all the distinct values.

It's not going to be as efficient as directly quering the db - the object model doesn't seem to be designed to slice the data the way you want - but it will give you the results going through the officially supported channels.

Answer 2

So it sounds like you have a couple of options and neither are ideal...

1. You fetch all the profiles, loop through and add the unique values to a hashtable to pick out the distinct ones. Although this also requires a lot of permissions to perform this.

2. Add an SP to the database and do it there and risk having the Sharepoint team change the database format. This probably isn't supported but it's probably not too dangerous either, it's not like you are changing the table format and you can always rewrite the sp in the event that the sharepoint team do make a change.

If I had this task I would most likely create the stored procedure

Answer 3

http://blog.tegdesign.com/2010/09/01/retrieving-user-profile-data-in-vb-net-from-sharepoint/