Community Server and Extended Profile Properties

Anyone who has done much customization for Community Server will eventually run into the need to add more profile properties for users. These properties are really easy to add. The problem is that you cannot easily search on these fields because they are stored as two text columns in the database. There's an old add-on that offered one way to get at these values. Another would be do your searching in code since the values are easy to get at once the User objects have been loaded.

While it isn't efficient enough for regular site-based use, and it certainly isn't pretty; you can query the database for these values using text manipulation procedures in Sql Server. Adding first-class columns to the profile table requires a major effort in CS, so this approach can work if you want to add ad-hoc usre reports.

<disclaimer>I really can't recommend the following for adding functionality to your CS search page - but if you manage to use it and have some performance measures, please share them.</disclaimer>

I added three functions to my CS database*:

GetUserPropertyValue - takes the UserId, property name, property type (b,s,i), and settingsID and comes back with the named property value.

GetPropertyStartIndex - takes the property name you're searching for, and the list of property names. Used by GetUserPropertyValue.

GetPropertyLength - takes the same params as above, and returns the text length of the property value.

The start index, and length are what is stored in the property names string. You then go to the property values string to get the value only. I broke this operation up to into separate procedures for readability.

Finally, I created a modified version of cs_vw_Users_FullUser to include values for the fields I was interested in by adding this function call into the select query:

dbo.GetUserPropertyValue(cs_UserID, N'MySpecialProperty', N'S', SettingsID)

This way, I could write a report against all users and search, sort, and filter by custom values.

Download the sql code.

This hack will work for asp.net based profiles too since the methodology used to store these key/value strings in cs_UserProfile (PropertyNames | PropertyValues) is the same as is used for the base aspnet_Profile table for asp.net membership profiles.

*my sql-guru friend John wrote the original manipulation query.