Translate this Sql query into NHibernate Linq or Criteria?

Question

I have a security schema where certain entities are secured by having a SecureEntity reference. A SecureEntity has a collection of RolePermissions, each of which has an Allow flag and a Priority. The idea is to match the user's roles against the RolePermissions on the SecureEntity. For example, a user may be allowed by their lowest priority permission but denied by a higher one, so it is the highest one that we are interested in. In this example the root entity I am querying is called ProcessCategory.

(SecureRoleId is the match for the user's role; SecureRoleName is just a string description.)

Assume a user has roles (1,2) and the SecureEntity has RolePermissions:

SecureRoleId = 1, Priority = 0, Allow = true
SecureRoleId = 2, Priority = 1, Allow = false

In this case the entity would not be selected. But if the user only had role 1, the entity would be selected. Of course, the SecureEntity may contain a bunch of other roles that the user does not have and are irrelevant.

The sql code below works and does this: 'select the entity if the highest priority role permission that the user also has is Allow=true'. So it basically filters RolePermission on the users own roles (IN clause), sorts by Priority, and takes the highest one if that is an Allow.

Here is the Sql:

select pc.* from ProcessCategory pc
join SecureEntity se 
    join RolePermission rp on se.SecureEntityId = rp.SecureEntityId 
on pc.SecureEntityId = se.SecureEntityId
where rp.RolePermissionId = (select top 1 RolePermissionId 
                from RolePermission
                where Allow = 1
                and SecureEntityId = se.SecureEntityId
                and SecureRoleId in(0,1)
                order by Priority desc)

There may be another way to write the above Sql but it does what I need. Ideally I would like to achieve this using NHibernate Linq or Criteria. I spent a few hours trying to get Linq to work and failed with various 'invalid operation' exceptions on the inner join to RolePermission. I don't have much experience with ICriteria or MultiCriteria and would be interested if anybody can help me.

Note that the Fluent mapping for the objects is straightforward:

 <some-entity>.References(x => x.SecureEntity) 

and

 SecureEntity.HasMany(x => x.RolePermissions).Not.Inverse();

Answer 1

Okay. I couldn't get this to work using native NH Linq, although that doesn't mean that it is not possible. But I looked through all the NH unit tests for Linq and couldn't find anything equivalent.

To get it working I created a database function called UserHasPermission that does everything in:

on pc.SecureEntityId = se.SecureEntityId
where rp.RolePermissionId = (select top 1 RolePermissionId 
            from RolePermission
            where Allow = 1
            and SecureEntityId = se.SecureEntityId
            and SecureRoleId in(0,1)
            order by Priority desc)

This works with any kind of secured entity. I then mapped that function as an NH Linq function by following the instructions in this page: http://wordpress.primordialcode.com/index.php/2010/10/01/nhibernate-customize-linq-provider-user-defined-sql-functions/.

If you follow those instructions, you have to create a normal LinqToObjects extension in C# that has an identical signature to your database one. You can then do your NH Linq query like:

return base.Query<T>().Where(c => ((ISecureEntity)c)
            .SecureEntity.Id
            .UserHasPermissions(user.SecureRoleIdsCsv) == 1);

The only problem I found was that my original Sql function returned a bit, which I mapped to a NH Boolean type. However this produced a really strange bit of sql that had several "Where ''True'' = ''True''" clauses that blew up in Sql Server. So I changed the result to an integer and everything worked okay. A bit counter-intuitive, but...

Doing it this way allowed me to carry on transparently using Linq for all my queries, without affecting existing code, because it automatically prepended each query with the security check.

Note that I looked in the Rhino Security source code and it uses a multiple criteria that is much too complex for me to understand with my limited NH knowledge. If I had done it using CreateCriteria, could I have combined it with Linq though?

• The instructions in the above link do not make it clear that when you have created your own Dialect that registers your Sql function, you have to make sure you reference it in your NH configuration file (or code), otherwise you will get some kind of 'unknown type' exception.