public async Task <ActionResult> Index() { var query = await( from user in _dbContext.User join ur in _dbContext.UserRole on user.UserId equals ur.RoleId into UserRole from userRole in UserRole.DefaultIfEmpty() join role in _dbContext.Role on userRole.RoleId equals role.RoleId into Role from role in Role.DefaultIfEmpty() select new { user, userRole, role } ).ToListAsync(); var userList = new List <UserRoleViewModel>(); foreach (var group in query.GroupBy(q => q.user.UserId)) { var first = group.First(); userList.Add(new UserRoleViewModel { UserId = first.user.UserId.ToString(), UserName = first.user.UserName, Roles = first.role != null ? group.Select(g => g.role).Select(r => r.Name) : new List <string>() }); } return(View(userList)); }