public IEnumerable <AspNetUserViewModel> ListAspNetUsers(AspNetUserSearchCriteria searchCriteria)
{
// entity framework is needed due to the ApplicationUserManager, so we'll use it here as well
List <AspNetUserViewModel> result = null;
using (var ctx = this.CreateContext())
{
var command =
@"select top 100
u.UserName
,u.Email
,r.Name Role
from
dbo.AspNetUsers u
-- note: join may duplicate users in result set
left join dbo.AspNetUserRoles ur on (u.Id = ur.UserId)
left join dbo.AspNetRoles r on (ur.RoleId = r.Id)
where
(@UserName is null or @UserName = '' or u.UserName like '%' + @UserName + '%')
and (@Email is null or @Email = '' or u.Email like '%' + @Email + '%')
and (@Role is null or @Role = '' or r.Name like '%' + @Role + '%')
order by 1
";
result = ctx.Database
.SqlQuery <AspNetUserViewModel>(
command,
new SqlParameter("@UserName", searchCriteria.UserName ?? ""),
new SqlParameter("@Email", searchCriteria.Email ?? ""),
new SqlParameter("@Role", searchCriteria.Role ?? ""))
.ToList();
}
// query joins users to roles. Could be duplicates. Consolidate.
result = result
.GroupBy(x => x.UserName)
.Select(x => new AspNetUserViewModel()
{
UserName = x.Key,
Email = x.Min(g => g.Email),
Role = string.Join(", ", x.Select(g => g.Role))
})
.ToList();
return(result);
}
public ActionResult ListUsers(string u, string e, string r)
{
var sc = new AspNetUserSearchCriteria()
{
UserName = u,
Email = e,
Role = r
};
ViewBag.Sc = sc;
ViewBag.AspNetUsers = this.UserRepository.ListAspNetUsers(sc).ToList();
return(View());
}