Why is getting roles when getting a list of users so slow?

Question

I'm getting all users in the DB and their associated roles. It works, but man is it slow.

        var ctx = new SiteUserContext();
        var ctxUserList = ctx.Users.ToList();


        //var userManager = new UserManager<SiteUser>(new UserStore<SiteUser>(new SiteUserContext()));

        var jsonModels = from user in ctxUserList
                         select new
                         {
                             userName = user.UserName,
                             Roles = (from userRole in user.Roles
                                      join role in ctx.Roles on userRole.RoleId
                                      equals role.Id
                                      select role.Name).ToList(),
                             id = user.Id
                         };

Getting just a list of users is fine, about 600ms for 100 users. But as soon as I try and add in the roles, I end up waiting 5-10 seconds. Each user only has 1 or 2 roles. This isn't exactly a huge query.

I tried using the userManager GetRolesById(user.Id) but that was even slower. 10+ seconds.

Any tips on making this run quickly would be greatly appreciated.

Answer 1

What you have here is executing N+1 queries to get the information since you get the list of users, then enumerate the list of users to streamline. Using LINQ we can do this a bit more efficiently. I've used this before to do this in one process.

var usersWithRoles = (from user in ctx.Users  
                      select new  
                       {  
                        UserId = user.Id,                                        
                        Username = user.UserName,  
                        Email = user.Email,  
                        RoleNames = (from userRole in user.Roles  
                                     join role in context.Roles on userRole.RoleId   
                                     equals role.Id  
                                     select role.Name).ToList()  
                                  })
                    .ToList()
                    .Select(p => new   
                    {  
                       UserId = p.UserId,  
                       Username = p.Username,  
                       Email = p.Email,  
                       Role = string.Join(",", p.RoleNames)  
                    });  

This should be much faster and get it with 1 query!

Edit: Looking at your request, if you just want the roles as a list, you can skip the second projection from this example. (I had a need to display this in a list, thus the string.Join in the example.