Grabbing Users with a specific value in their profile

Question

I'm using membership and roles for authentication in my vb .net application. We have about 5 roles in the application with certain roles filling out a specific profile value. Example is the role is store and the profile value is store number. Obviously if you work for headquarters you don't have a store number so I don't care about it. Each store can also have more than 1 employee.

I need to get the users for a specific store number. Meaning I would only want the users that belong to store number 101 to show up that list. The way that we are doing this now is going through all the users and adding the users that fit the criteria into a sorted list. This functions but the problem is when you start passing about 3,000 users or so. It just becomes to slow to be any good.

How would you guys find a different way of doing it? I really don't want to do custom stored procedure or changing the underlying classes because I'm afraid of it all breaking on a later version of .net that they change membership and roles.

Answer 1

You're using the built-in .NET role manager that saves to a SQL Server instance I take it? What format are your user object in when you're currently looking at them to evaluate the criteria? If you post a code sample I have an idea...

Answer 2

This is really the sort of thing you want to filter on in SQL. I don't think there is any trick to get around doing a linear scan of your data and get the results you want.

If doing this in SQL isn't an option then maybe you can avoid creating a second list and just sort your main user array and have the display only display the ones you care about. That would save the memory copy time at least.

Answer 3

    Public Shared Function LoadALLUsersInRole(ByVal Code As Integer, ByVal Role As String) As ArrayList
        Dim pb As ProfileBase
        Dim usersArrayList As New ArrayList
        Dim i As Integer
        Dim AllUsersInRole() As String = Roles.GetUsersInRole(Role)

        For i = 0 To AllUsersInRole.Length - 1

            pb = ProfileBase.Create(AllUsersInRole(i), True)

            'Check to see if the current user in the collect belongs to this Store.
            If CType(pb.GetPropertyValue("Store.Code"), Integer) = Code Then                    
                    usersArrayList.Add(AllUsersInRole(i))                   
            End If
            pb = Nothing
        Next

        Return usersArrayList
    End Function

That is the example code of how I'm doing it. The reason that I don't want to do it on the SOL side is that I would have a huge dependency on the fact that membership and roles doesn't change.