Need a Multi Value Profile Property Using SqlTableProfileProvider

Question

In my ASP.Net Framework 4 Web Application project (VS 2010), I am using SqlTableProfileProvider (http://www.asp.net/downloads/sandbox/table-profile-provider-samples). I have it working very nicely with a set of custom properties like FirstName, LastName, FavoritePizzaTopping etc.

However, I would like to implement a multi valued profile property of type ArrayList, List, Dictionary or Collection (whichever is recommended).

The property will store a list of Brands associated with the user. I have a table in the database that stores UserId and BrandId but I'm not sure how to implement this concept.

I have looked at http://msdn.microsoft.com/en-us/library/d8b58y5d(v=vs.100).aspx but my properties are not defined in the web.config, they are defined within ProfileCommon : ProfileBase.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Profile;
using System.Web.Security;
using AbcProfileProvider;

namespace Abc.Profiles
{
    public class ProfileCommon : ProfileBase
    {
        public static ProfileCommon GetProfile()
        {
            return Create(HttpContext.Current.Request.IsAuthenticated ?
               HttpContext.Current.User.Identity.Name : HttpContext.Current.Request.AnonymousID,
               HttpContext.Current.Request.IsAuthenticated) as ProfileCommon;
        }

        public static ProfileCommon GetUserProfile(string username)
        {
            return Create(username) as ProfileCommon;
        }

        public static ProfileCommon GetUserProfile()
        {
            return Create(Membership.GetUser().UserName) as ProfileCommon;
        }

        [CustomProviderData("FirstName;nvarchar")]
        public string FirstName
        {
            get { return ((string)(base["FirstName"])); }
            set { base["FirstName"] = value; Save(); }
        }

        [CustomProviderData("LastName;nvarchar")]
        public string LastName
        {
            get { return ((string)(base["LastName"])); }
            set { base["LastName"] = value; Save(); }
        }

        [CustomProviderData("Address1;nvarchar")]
        public string Address1
        {
            get { return ((string)(base["Address1"])); }
            set { base["Address1"] = value; Save(); }
        }

        [CustomProviderData("Address2;nvarchar")]
        public string Address2
        {
            get { return ((string)(base["Address2"])); }
            set { base["Address2"] = value; Save(); }
        }

        [CustomProviderData("City;nvarchar")]
        public string City
        {
            get { return ((string)(base["City"])); }
            set { base["City"] = value; Save(); }
        }

        [CustomProviderData("State;nvarchar")]
        public string State
        {
            get { return ((string)(base["State"])); }
            set { base["State"] = value; Save(); }
        }

        [CustomProviderData("ZipCode;nvarchar")]
        public string ZipCode
        {
            get { return ((string)(base["ZipCode"])); }
            set { base["ZipCode"] = value; Save(); }
        }

        [CustomProviderData("Phone;nvarchar")]
        public string Phone
        {
            get { return ((string)(base["Phone"])); }
            set { base["Phone"] = value; Save(); }
        }

        [CustomProviderData("Fax;nvarchar")]
        public string Fax
        {
            get { return ((string)(base["Fax"])); }
            set { base["Fax"] = value; Save(); }
        }

        [CustomProviderData("Email;nvarchar")]
        public string Email
        {
            get { return ((string)(base["Email"])); }
            set { base["Email"] = value; Save(); }
        }

        [CustomProviderData("Brands")]
        public List<Brand> Brands
        {
            get { return ((List<Brand>)(base["Brands"])); }
            set { base["Brands"] = value; Save(); }
        }

        [CustomProviderData("Territories")]
        public List<Territory> Territories
        {
            get { return ((List<Territory>)(base["Territories"])); }
            set { base["Territories"] = value; Save(); }
        }
    }
}

I believe the solution lies within SqlProfileProvider.cs, but I'm not sure how to implement it, the following are two methods from that file (I would have posted the entire file but there were limitations):

    public override SettingsPropertyValueCollection GetPropertyValues(SettingsContext context, SettingsPropertyCollection collection) {
        SettingsPropertyValueCollection svc = new SettingsPropertyValueCollection();

        if (collection == null || collection.Count < 1 || context == null)
            return svc;

        string username = (string)context["UserName"];
        if (String.IsNullOrEmpty(username))
            return svc;

        SqlConnection conn = null;
        try {
            conn = new SqlConnection(_sqlConnectionString);
            conn.Open();

            GetProfileDataFromTable(collection, svc, username, conn);
        }
        finally {
            if (conn != null) {
                conn.Close();
            }
        }

        return svc;
    }

    private void GetProfileDataFromTable(SettingsPropertyCollection properties, SettingsPropertyValueCollection svc, string username, SqlConnection conn) {
        List<ProfileColumnData> columnData = new List<ProfileColumnData>(properties.Count);
        StringBuilder commandText = new StringBuilder("SELECT u.UserID");
        SqlCommand cmd = new SqlCommand(String.Empty, conn);

        int columnCount = 0;
        foreach (SettingsProperty prop in properties) {
            SettingsPropertyValue value = new SettingsPropertyValue(prop);
            svc.Add(value);

            string persistenceData = prop.Attributes["CustomProviderData"] as string;
            // If we can't find the table/column info we will ignore this data
            if (String.IsNullOrEmpty(persistenceData)) {
                // REVIEW: Perhaps we should throw instead?
                continue;
            }
            string[] chunk = persistenceData.Split(new char[] { ';' });
            if (chunk.Length != 2) {
                // REVIEW: Perhaps we should throw instead?
                continue;
            }
            string columnName = chunk[0];
            string columnType = chunk[1];

            if (columnType == "Table")
            {
                continue;
            }
            // REVIEW: Should we ignore case?
            SqlDbType datatype = (SqlDbType)Enum.Parse(typeof(SqlDbType), chunk[1], true);

            columnData.Add(new ProfileColumnData(columnName, value, null /* not needed for get */, datatype));
            commandText.Append(", ");
            commandText.Append("t."+columnName);
            ++columnCount;
        }

        commandText.Append(" FROM "+_table+" t, vw_aspnet_Users u WHERE u.ApplicationId = '").Append(AppId);
        commandText.Append("' AND u.UserName = LOWER(@Username) AND t.UserID = u.UserID");
        cmd.CommandText = commandText.ToString();
        cmd.CommandType = CommandType.Text;
        cmd.Parameters.AddWithValue("@Username", username);
        SqlDataReader reader = null;

        try {
            reader = cmd.ExecuteReader();
            //If no row exists in the database, then the default Profile values
            //from configuration are used.
            if (reader.Read()) {
                Guid userId = reader.GetGuid(0);
                for (int i = 0; i < columnData.Count; ++i) {
                    object val = reader.GetValue(i+1);
                    ProfileColumnData colData = columnData[i];
                    SettingsPropertyValue propValue = colData.PropertyValue;

                    //Only initialize a SettingsPropertyValue for non-null values
                    if (!(val is DBNull || val == null))
                    {
                        propValue.PropertyValue = val;
                        propValue.IsDirty = false;
                        propValue.Deserialized = true;
                    }
                }

                // need to close reader before we try to update the user
                if (reader != null) {
                    reader.Close();
                    reader = null;
                }

                UpdateLastActivityDate(conn, userId);
            }
        }
        finally {
            if (reader != null) {
                reader.Close();
            }
        }
    }

Here is the relevant section of web.config:

<membership>
  <providers>
    <clear />
    <add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider" connectionStringName="ApplicationServices" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="false" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10" applicationName="/" />
  </providers>
</membership>
<profile enabled="true" defaultProvider="TableProfileProvider" inherits="Abc.Profiles.ProfileCommon, AbcProfileProvider">
  <providers>
    <clear />
    <add name="AspNetSqlProfileProvider" type="System.Web.Profile.SqlProfileProvider" connectionStringName="ApplicationServices" applicationName="/" />
    <add name="TableProfileProvider" type="Abc.ProfileProviders.SqlTableProfileProvider, AbcProfileProvider" connectionStringName="ApplicationServices" table="MembershipProfiles" applicationName="/" />
    <add name="StoredProcedureProfileProvider" type="Abc.ProfileProviders.SqlStoredProcedureProfileProvider, AbcProfileProvider" connectionStringName="ApplicationServices" setProcedure="setCustomProfileData" readProcedure="getCustomProfileData" applicationName="/" />
  </providers>
</profile>
<roleManager defaultProvider="NetSqlAzManRoleProvider" enabled="true" cacheRolesInCookie="false" cookieName=".ASPROLES" cookieTimeout="30" cookiePath="/" cookieRequireSSL="false" cookieSlidingExpiration="true" cookieProtection="All">
  <providers>
    <clear />
    <add connectionStringName="ApplicationServices" applicationName="/" name="AspNetSqlRoleProvider" type="System.Web.Security.SqlRoleProvider" />
    <add applicationName="/" name="AspNetWindowsTokenRoleProvider" type="System.Web.Security.WindowsTokenRoleProvider" />
    <add name="NetSqlAzManRoleProvider" type="NetSqlAzMan.Providers.NetSqlAzManRoleProvider" connectionStringName="NetSqlAzManConnectionString" storeName="Abc Authorization Store" applicationName="The App" userLookupType="DB" defaultDomain="" UseWCFCacheService="False" />
  </providers>
</roleManager>

I'm hoping someone on StackOverflow has already implemented something like this or can point me in the right direction.

Thank you!