How to get a subset of Users from ASP.NET Identity 2

Using a slightly modified version of the default ASP.NET MVC 5 template (with Individual Accounts), I am trying to get a subset of users based on an intermediary table. I have already built up an administration UI that can return a list of all users, but now I need to limit the set of users returned based on the currently logged in user's access privileges defined in the intermediary table.

Essentially, each user will have access to 1 or more clinics, so there will be one record for each clinic to which they have access.

If the currently logged in user belongs to a given role (e.g., "Clinic Admin"), then they should have the ability to retrieve a list of any users who belong to any of the clinics to which they have access.

Can anyone help point me in the right direction? This is my first Anything.NET application, so please feel free to explain like I'm five. :-)

Thank you in advance for any help you can offer.

Additional information:

Visual Studio 2013 Update 5
Entity Framework 6
MS SQL Server 2008 R2

Here is the intermediary table's class (ClinicUser):

[Table("clinic_users")]
public class ClinicUser
{
    [Key]
    public virtual ApplicationUser ApplicationUsers { get; set; }

    [Required]
    public string Id { get; set; }

    [Required]
    public System.Guid provider_id { get; set; }

    [Required]
    public System.Guid health_system_id { get; set; }

    [Required]
    public System.Guid clinic_id { get; set; }
}

Here is my ApplicationUser class:

public class ApplicationUser : IdentityUser
{
    public string FirstName { get; set; }

    public string LastName { get; set; }

    public string FullName
    {
        get { return FirstName + " " + LastName; }
    }

    [ForeignKey("ClinicUsers")]
    public override string Id
    {
        get
        {
            return base.Id;
        }
        set
        {
            base.Id = value;
        }
    }

    public virtual ClinicUser ClinicUsers { get; set; }

    public IEnumerable<SelectListItem> RolesList { get; set; }

    public async Task<ClaimsIdentity> GenerateUserIdentityAsync(UserManager<ApplicationUser> manager)
    {
        // Note the authenticationType must match the one defined in CookieAuthenticationOptions.AuthenticationType
        var userIdentity = await manager.CreateIdentityAsync(this, DefaultAuthenticationTypes.ApplicationCookie);
        // Add custom user claims here
        userIdentity.AddClaims(ClinicClaimsProvider.GetClaims(userIdentity));
        return userIdentity;
    }
}

In case it wasn't clear, what I'm really trying to do is narrow the list of ApplicationUsers to return only the list of users to which I have access to based on the clinics we have have in common.

If I were writing this as a SQL query, this would be one way to accomplish what I want (I just can't seem to quite get what I want with LINQ):

SELECT *
FROM AspNetUsers au
WHERE Id IN (
    SELECT Id
    FROM clinic_users
    WHERE clinic_id IN (
            SELECT clinic_id
            FROM clinic_users
            WHERE Id = 'CurrentUserId'
            )
    )

Solution

I solved this a while back, but I thought I had better come back here and update my question with an answer, in case this might help someone else.

I updated my Clinic and ClinicUser classes accordingly:

Clinic.cs

[Table("clinics")]
public class Clinic
{
    [Key]
    public System.Guid ClinicId { get; set; }

    public List<ClinicUser> ClinicUsers { get; set; }
}

ClinicUser.cs

[Table("clinic_users")]
public class ClinicUser
{
    [Key, Column(Order = 0)]
    public string UserId { get; set; }

    [Key, Column(Order = 1)]
    public System.Guid ClinicId { get; set; }

    [ForeignKey("UserId")]
    public virtual ApplicationUser ApplicationUser { get; set; }

    [ForeignKey("ClinicId")]
    public Clinic Clinic { get; set; }
}

Also, I updated the following excerpt of my ApplicationUser class from this:

[ForeignKey("ClinicUsers")]
public override string Id
{
    get
    {
        return base.Id;
    }
    set
    {
        base.Id = value;
    }
}

public virtual ClinicUser ClinicUsers { get; set; }

to this:

public List<ClinicUser> ClinicUsers { get; set; }

Finally, in my ApplicationUsersController's Index() action, I was able to use this:

   public async Task<ActionResult> Index()
    {
        if (User.IsInRole("Admin")) return View(await UserManager.Users.ToListAsync());

        var userId = User.Identity.GetUserId();

        //Get the Ids of the current user's clinics
        var userClinics = db.ClinicUsers.Where(cu => cu.UserId == userId).Select(cu => cu.ClinicId).ToList();

        //Get all userIds of the user at the current user's clinics 
        var clinicUserIds = db.ClinicUsers.Where(cu => userClinics.Contains(cu.ClinicId)).ToList().Select(cu => cu.UserId);

        var users = UserManager.Users.Where(u => clinicUserIds.Contains(u.Id));

        return View(await users.ToListAsync());
    }

In essence, if the user has the "Admin" role, then they will get a list of all users in the database. If they aren't, they will only get a list of the users that also belong to the clinics they have in common.

It may not be perfect, but it works. If anyone has any suggestions on how to improve this, I would be glad to hear it.

Again, my thanks to Archil (https://stackoverflow.com/users/4089212/archil-labadze) for his helpful responses.