Read-Only Joined Collection in NHibernate

Question

Here is the basic situation. In our application, we have Roles and Permissions. There is a many-to-many relationship between roles and permission. The catch is, that a user can be assigned roles directly, or they can have a role that they have acquired by paying for a service. To make things more interesting, if the user cancels the paid service, or it expires, then they should no longer have that role. My issue is that I would like to have a (read-only) collection of the Effective Permissions on any given user, and I'd like to be able to query against it. Some mapping details:

    public ApplicationPermissionMap()
    {
        Table("CLSYS_ApplicationPermissions");

        Id(ap => ap.ID)
            .Column("ApplicationPermissionID")
            .GeneratedBy.Assigned();

        //...
    }

    public ApplicationRoleMap()
    {
        Table("CLSYS_ApplicationRole");

        Id(role => role.ID)
            .Column("ApplicationRoleID");           

        HasManyToMany(r => r.Permissions)
            .Table("CLSYS_ApplicationRolePermissions")
            .ParentKeyColumn("ApplicationRoleID")
            .ChildKeyColumn("PermissionID")
            .Cascade.None();

        //...
    }

    public PaidServiceMap()
    {

        Table("ECOM_PaidService");

        Id(ps => ps.ID)
            .Column("PaidServiceID");

        Component(ps => ps.Status, statusMapping =>
        {
            statusMapping.Map(status => status.ID)
                .Column("StatusID")
                .Not.Nullable();

        });

        HasManyToMany(ps => ps.ApplicationRoles)
            .Table("ECOM_PaidServiceRoles")
            .ParentKeyColumn("PaidServiceID")
            .ChildKeyColumn("RoleID")
            .Cascade.None();

        //....
    }

    public UserMap()
    {
        Table("CLSYS_User");

        Id(user => user.ID)
            .Column("UserID");

        HasManyToMany(user => user.Roles)
            .Table("CLSYS_User_ApplicationRoles")
            .ParentKeyColumn("UserID")
            .ChildKeyColumn("RoleID")
            .Cascade.None();

        //...

    }

What I need is something like this (an addition to UserMap above)

   public UserMap()
    {
        //...

        HasMany(user => user.EffectivePermissions)
          //Union ( user -> ApplicationRoles -> permissions)
          //Union ( user -> PaidServices [with active status] -> permissions)
          //Distinct
    }

And I would like to be able to query on these permissions like so

    _session.Linq<User>().Where(u => u.EffectivePermssions.Contains(somePermission) && user.SomeOtherCriteria == something);

I understand that I wouldn't be able to modify this collection directly, and that is perfectly acceptable. Can anyone help me out with this mapping?

Answer 1

As a short term solution, I have created a view that handles the union logic, and have the following mapping:

         HasManyToMany(user => user.EffectivePermissions)
            .Table("VW_CLSYS_User_EffectiveApplicationPermissions")
            .ParentKeyColumn("UserID")
            .ChildKeyColumn("ApplicationPermissionID")
            .Inverse();

This is not ideal, as I'd prefer to have the logic in the mapping for the user. Any other suggestions are welcome.