Searching for Profile Properties

I understand that the user profile data is stored as a continuous string in the PropertyValuesString field in aspnet_Profile with floating positions for each particular property.

So, how would I have to formulate my SQL-Query in order to extract users that match specific profile properties (let's say I want to find all members that are male, or all that have language set to 'spanish', etc.)?

Unfortunately, you have just stumbled across one of the larger disadvantages of the serialized Profile implementation. Due to the way profile data is stored, while there are SQL functions available which can pull out specific data, the performance of any search based on these techniques would be pretty pitiful.

The only real way to build a solid search is to write your own custom ProfileProvider, where you could then use a more conventional schema to implement more advanced profile searching.

This works, and also work for asp.net 2.0 profiles

Yep, that's the function I was referring to - just don't think I'd want to base a commercial-grade search solution around that method.

Are you planning to write a cs ProfileProvider for this task in the future?

Member handling is quite important for community sites....

ralph

It's looking likely that I may have to do just that for an external CS project that I'm currently speccing. We'll have to invesigate the exact performance and maintenance constraints of developing a solution to work with the existing provider before a final decision is made, though.

There's this one which stores each profile property in it's own row, I haven't downloaded the samples yet (in work restrictions!) but the code on the page is incomplete so can't say if it works or not.

Genyus wrote:

It's looking likely that I may have to do just that for an external CS project that I'm currently speccing. We'll have to invesigate the exact performance and maintenance constraints of developing a solution to work with the existing provider before a final decision is made, though.

Any news on that external project yet?
Will there be a chance that the solution (or at least the concept) developed for it might be shared here?

HI

Just checking.... 4 month later.... are there any news on extended profiles that can be queried via SQL?

Thanks
ralph

I too would love to to be able to search on these properties, but I am running into all sorts of problems. 
Please keep us posted on your project.

Just to say, we never went down the custom profile provider in the end. I don't know if anyone else out there has done this yet.