Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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.

I do not want to loop through UserProfileManager because a) it's sloooow and b) requires Elevated Permissions that even SPSecurity.RunWithElevatedPermissions doesn't provide.

Before I write a timer job that does that, is there some other way to do that?

share|improve this question
    
Accessing the database isn't necessarily not allowed. It is unsupported to make changes to the database, and the tables are not documented because they can (and do) change at any time. The API is preferred for access as it is usually backwards compatible, so if you develop code that references it, it is less likely to break when an update comes out. There is nothing wrong with what you've done, provided you don't go around telling everyone that the only way to get a list of departments is "SELECT ..." –  shufler Dec 19 '11 at 22:39
add comment

2 Answers 2

As far as I am aware this can only be done with choice fields. For something like the department you would likely have to iterate through the profiles.

share|improve this answer
add comment
up vote 1 down vote accepted

Ended up querying Active Directory directly since the SharePoint 2010 User Profile Store doesn't have a way to be queried efficiently.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.