Filter users on role and other criterias

Topics: Core, Customizing Orchard, General
Jul 9, 2014 at 9:07 AM
Edited Jul 9, 2014 at 9:11 AM
I'm trying to build an interface where a user with the "manager" role can list/edit other profiles with the "customer" role. The UI page should display a paged result, and only list the customers attached to the manager via another criteria (say, they both are attached to the same "shop").

So here are my parts (both attached to the "User" content type):
public class ManagerPartRecord : ContentPartRecord
{
    public virtual ShopPartRecord Shop { get; set; }
    // Other properties
}
public class ManagerPart : ContentPart<ManagerPartRecord>
{
    public ShopPartRecord Shop
    {
        get { return this.Record.Shop; }
        set { this.Record.Shop = value; }
    }
    // Other properties
}
public class CustomerPartRecord : ContentPartRecord
{
    public virtual ShopPartRecord Shop { get; set; }
    // Other properties
}
public class CustomerPart : ContentPart<CustomerPartRecord>
{
    public ShopPartRecord Shop
    {
        get { return this.Record.Shop; }
        set { this.Record.Shop = value; }
    }
    // Other properties
}
And the current code to filter customers on shop AND role:
public IEnumerable<CustomerPart> GetCustomers(ShopPart shop, int pageIndex, int pageSize, out int totalNumberOfCustomers)
{
    // Get the ids of ALL the customers
    List<int> customerIds = this.UserRolesRepository
                    .Fetch(x => x.Role.Name == "Customer")
                    .Select(x => x.UserId)
                    .ToList();
    
    // Filter the users on customer role and shop ID
    IContentQuery<CustomerPart> query = this.OrchardServices.ContentManager
        .Query<CustomerPart>(VersionOptions.Published, "User")
        .ForContentItems(customerIds)
        .Where<CustomerPartRecord>(x => x.Shop.Id == currentUser.Shop.Id);
    
    // Get total number of matching customers, useful for paging
    totalNumberOfCustomers = query.Count();
    
    // Get a single page of results
    return query.Slice((pageIndex - 1) * pageSize, pageSize);
}
But this is utterly ineffective when the customer's count starts increasing (even if they are not attached to the same shop - 3000 customers in different shops and the query takes more than 20 seconds to execute). The problem is the injection of all the customer IDs in the query.

If I execute the query without the ".ForContentItems(customerIds)" line it executes fast and nicely (< 1 second), but it also returns non-customers users, and I don't want to filter the results in-memory as that would mean retrieving all results, filtering, and applying the paging in-memory (1 shop can contain many-many customers...).