CreateAlias and NHibernate joins on classes w/ no mapped relationship

Question

I'm beginning to wonder if I am in fact too dumb to use NHibernate. I'm currently using FluentNHibernate to create simple db mappings and that's worked well for us. When dealing w/ our various classes in isolation, we're able to read and write, perform updates, etc. to all of those classes. My problem however is in trying to build up a query that involves anything more complex than filtering upon fields of the entity type. A perfect example is illustrated below--

The pertinent portions of the mappings:

public class UserMap : ClassMap<User> {
    Id(u => u.Id).Column("UserID").GeneratedBy.Identity();
    //other non-pertinent fields 
}

public class RoleMap : ClassMap<Role> {
    Id(r => r.Id).Column("RoleId").GeneratedByIdentity();
    //snip
}

public class RoleMapMap : ClassMap<RoleMap> {
    Id(rm => rm.Id).Column("RoleMapId").GeneratedByIdentity();
    Map(rm => rm.UserId);
    Map(rm => rm.RoleId);
    //snip
}

The intent is to generate a query w/ the Criteria API to retrieve all users of a specific role--at a high level, filter rolemap based on a specific role ID, then join to Users, and return only those users.

Attempted with following, but my usage of CreateAlias is obviously flawed, as the runtime exception basically tells me that it has no idea what "RoleMap" in the below is as it relates to the User object.

var criteria = session.CreateCriteria<User>().
                CreateAlias("RoleMap", "rm").
                Add(Expression.Eq("rm.UserId", "UserId")).
                Add(Expression.Eq("rm.RoleId", 99)).
                SetResultTransformer(new 
                    DistinctRootEntityResultTransformer());

var users = criteria.List<User>();

Can someone point me in the right direction? I'd prefer not to edit the underlying objects to expose collections--(e.g. a User.Roles[] collection) as there's cases where we specifically have tables used solely for joins but we don't want floating to the middle tier. So learning how to join isolated classes is going to matter to us.

Answer 1

Your mapping contains no way to navigate from User to RoleMap, yet that is what you are trying to do in your Criteria API call. You have multiple options. Here are a couple:

1) Allow User to navigate to RoleMap in your mapping. This is the easiest and how it's normally done.

2) Use two queries, one to get a list of UserIds based on the RoleMap to Role relationship and then a second query to get all the Users for those UserIds.

You say you don't want a User.Roles collection in your middle tier, but NHibernate should exist in your data layer, not necessarily your business layer. You can allow NHibernate to know about User.Roles while effectively hiding it from your business layer.

Joining isolated classes isn't really what ORMs are built for. ORMs are built for joining related classes. Related classes are generally mapped to related tables at the database level. To join isolated classes, you are going to need to do things like option 2 above where you run multiple queries and/or work around the lack of a relationship in custom code.