Why is the asp.NET profile designed in such a horrible way?

Question

In the current project I'm working on, we are using the asp.NET profile to store information about users, such as their involvment in a mailing list.

Now, in order to get a list of all the users in the mailing list, I cannot simply do a database query, as the asp.NET profile table is, simply put, awful.

For those who do not know, the profile table has two main columns, the 'keys' column, and 'values' column, and they are organised as so:

Keys: Key1:dataType:startIndex:endIndex:key2:dataType . . etc.

Values: value1value2value3...

This is pretty much impossible to query using SQL, so the only option to find users that have a specific property is to load up a list of ALL the users and loop through it.

In a site with over 150k members, this is understandably very slow!

Are there specific reasons why the Profile was designed like this, or is it just a terrible way of doing dynamically-generated data?

Answer 1

The whole point of the Provider model is that it abstracts away the data source. The idea is that, as a developer, you don't need to know how the data is stored or in what format - you just have a common set of methods for accessing it. This means you can swap providers without changing a single line of code. It also means that you specifically do not try and access data direct from the data source (eg. going straight to the database) by bypassing the provider methods - that defeats the whole point.

The default ASP.NET profile provider is actually very powerful, as it can not only store simple value types (strings, ints etc.) but it can also store complex objects and entire collections in a single field. Try doing that in a relational database! However, the downside of this generic-ism is that it comes at a cost of efficiency. Which is why, if you have a specific need, then you are supposed to implement your own provider. For example, see SearchableSqlProfileProvider - The Searchable SQL Profile Provider.

Of course, your third option is to simple not use the profile provider - nobody is forcing you to! You could implement your own classes/database entirely, as you would have had to do in other frameworks.

Answer 2

I agree that it's a pretty bad way to store profile data, but I suspect the use case was just to get the profile data for a user with a single query but in such a way that it can be extended to handle any number of different profile properties. If you don't like it, you can always write your own, custom profile provider that separates each value out into its own column. Having implemented various membership and role providers, I don't think that this would be too complicated a task. The number of methods doesn't look too large.

Answer 3

I have implemented various custom providers (membership/Sitemap/Roles etc) and havent really looked at the ASP.NET Profile Provider after seeing that kind of thing (Name/Value pairs or XML data). I am not sure, but I think the Profile is primary created for User Preferences/Settings where the settings are only required for a specific user, I dont think the Profile is meant for User "Data" that can be queried?

Note: This is an assumtion based on what I think I know, please comment on this otherwise.