Determining if a list of users are in role is very slow

Question

User-1072848465 posted

I am trying to determine the number of users in specific roles. My code works, but it takes almost 3 min to finish the loop. Can some offer a suggestion to speed up the process?

Here is my code.

int adminCount = 0;
int technicianCount = 0;
int salesPersonCount = 0;
int inActiveCount = 0;

var um = new UserManager<ApplicationUser>(new UserStore<ApplicationUser>(new ApplicationDbContext()));
CompanyAccountRepository DArepository = new CompanyAccountRepository();
iCompanyAccountRepository = DArepository;
List<CompanyAccountDto> companyAccounts =iCompanyAccountRepository.GetAspNetUserIdAccounts(Id);
foreach (var account in companyAccounts)
            {

                if (um.IsInRole(account.AspNetUserId, "CompanyAdmin") && account.Active)
                {
                    adminCount++;
                }
                else if (um.IsInRole(account.AspNetUserId, "SalesPerson") && account.Active)
                {
                    salesPersonCount++;
                }
                else if (um.IsInRole(account.AspNetUserId, "Technician") && account.Active)
                {
                    technicianCount++;
                }

                if (!account.Active)
                {
                    inActiveCount++;
                }
            }

I have tried the async methods for IsInRoleAsync, but it too produces a long wait.

Thanks
Brad

Answer 1

User475983607 posted

Use a group by linq query and then use count() to get the number of each group.

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/linq/group-elements-in-a-sequence

Answer 2

User-1072848465 posted

Are you recommending grouping the users from UserManager prior to checking the company accounts? If you have a code example that may help.

Thanks
Brad

Answer 3

User475983607 posted

The main issue with the posted code is it is doing a database call to get all the company accounts by Id. Then it loops through the list and does another database call for each record in the list due to the UserManager. 

bdassow

Are you recommending grouping the users from UserManager prior to checking the company accounts?

No...

The UserManager is designed for managing single users it is not the best tool for managing many users.

I assume there is key relationship between the CompanyAccount, User, and Role tables.  Craft a linq query against the ApplicationDbContext that does a count for each role or craft a group by linq query as shown in the reference doc.

Basically, rethink the approach. 

If you have a code example that may help.

That's a bit difficult as we cannot see the schema.

Answer 4

User-1072848465 posted

I will rethink my approach and try and build a linq query based on your recommendation. Thanks for the direction.