How do I query identity data efficiently in ASP.Net Core?

Question

Background

I have a website written in ASP.NET Core v2.1.1.

I have a custom identity user class:

public class FooIdentityUser : IdentityUser<string>, IIdentityModel
{
    [MaxLength(50)]
    public string FirstName { get; set; }
    [MaxLength(50)]
    public string LastName { get; set; }

    public string FullName => $"{FirstName} {LastName}";

    public bool FooBool { get; set; }
}

and a custom identity role class:

public class FooIdentityRole : IdentityRole<string>
{

}

Which I then reference in the dbcontext:

public class FooIdentityDbContext : IdentityDbContext<FooIdentityUser,FooIdentityRole,string>
{
    public FooIdentityDbContext(DbContextOptions<FooIdentityDbContext> options)
        : base(options)
    {
    }
}

Requirement

My overall requirement is that I want to give system admin users the ability to view and eventually manage user data from within the admin area of the website.

Specifically:

• I want to provide a list of users that are in a foo role
• And / or I want to list all users that have FooBool set to true
• And / or I want to query on email address, first name & last name
• And / or carry out a sort

Question

Does anyone have any links to web pages where this has been done before or can you respond on how I can implement this feature? I have attempted a couple of approaches below.

Approaches / Research

From what I can see there are two approaches to doing this:

Approach 1

Because I want to list users specifically for a user role based in a view, I can see that user manager provides a method for this:

_userManager.GetUsersInRoleAsync(fooRoleName)

The issue I have with this is it returns an IList so whilst it will return all users with this role, if I want to query on FooBool and / or FirstName, LastName or Email Address, it will need to cycle through the list to filter these out which would be inefficient if there are 10s of thousands or 100s of thousands of users?

Ideally, this would return an IQueryable so it wouldn't hit the database until my where and order by had been applied but I can't find a way of doing this?

Approach 2

The other way may be to query the context directly through my generic repository.

public class GenericIdentityRepository<TModel> : IIdentityRepository<TModel> where TModel : class, IIdentityModel
{
    private readonly ILogger _logger;
    public FooIdentityDbContext Context { get; set; }
    private readonly DbSet<TModel> _dbSet;

    public GenericIdentityRepository(FooIdentityDbContext dbContext, ILogger<GenericIdentityRepository<TModel>> logger)
    {
        Context = dbContext;
        _logger = logger;
        _dbSet = Context.Set<TModel>();
    }

    public IQueryable<TModel> GetAll()
    {
        _logger.LogDebug("GetAll " + typeof(TModel));
        IQueryable<TModel> query = _dbSet;
        return query;
    }

    public IQueryable<TModel> GetAllNoTracking()
    {
        _logger.LogDebug("GetAllNotTracking " + typeof(TModel));
        IQueryable<TModel> query = GetAll().AsNoTracking();
        return query;
    }
}

I was looking to see if I could do something by creating custom classes for userrole and then using linq to give me an IQueryable?

public class FooIdentityUserRole : IdentityUserRole<string>
{
    public virtual FooIdentityUser User { get; set; }
    public virtual FooIdentityRole Role { get; set; }
}

And then somehow query the data to return an IQueryable but I'm struggling to produce the correct linq I need to do this.

Answer 1

My suggestion is to use the FooIdentityDbContext directly in your controllers and just query the data in the way you want. I don't know a way you could achieve what you want using the UserManager class. Maybe there is but honestly, I wouldn't mix things. UserManager is more useful when you are dealing with a single user and want to do things with it such as AddToRoleAsync or ChangePasswordAsync.

You have much more flexibility using the DbContextclass directly. You don't need some fancy generic repository. Keep it simple and concise unless you definitely need the abstraction (which almost always you don't)

Down to the actual answer: You've already configured the entities correctly, so now just inject the FooIdentityDbContext and start querying. Something like this:

public class HomeController : Controller
{
    private readonly FooIdentityDbContext_dbContext;

    public HomeController(FooIdentityDbContext dbContext)
    {
        _dbContext = dbContext ?? throw new ArgumentNullException(nameof(dbContext));
    }

    public async Task<IActionResult> UserList(string roleName, bool fooBool, string firstName)
    {
        // You are interested in Users on Roles, so it's easier to start on the UserRoles table
        var usersInRole = _dbContext.UserRoles.Select(userRole => userRole);

        // filter only users on role first
        if (!string.IsNullOrWhiteSpace(roleName))
        {
            usersInRole = usersInRole.Where(ur => ur.Role.Name == roleName);
        }

        // then filter by fooBool
        usersInRole = usersInRole.Where(ur => ur.User.FooBool == fooBool);

        // then filter by user firstname or whatever field you need
        if (!string.IsNullOrWhiteSpace(firstName))
        {
            usersInRole = usersInRole.Where(ur => ur.User.FirstName.StartsWith(firstName));
        }

        // finally materialize the query, sorting by FirstName ascending
        // It's a common good practice to not return your entities and select only what's necessary for the view.
        var filteredUsers = await usersInRole.Select(ur => new UserListViewModel
        {
            Id = ur.UserId,
            Email = ur.User.Email,
            FooBool = ur.User.FooBool,
            FirstName = ur.User.FirstName
        }).OrderBy(u => u.FirstName).ToListAsync();

        return View("UserListNew", filteredUsers);
    }
}

Bonus: I've been reading the EF Core in Action book by Jon Smith and it's great. I highly recommend reading it if you want to keep using EF Core in your projects. It's full of nice tips and real world examples.