Hi Everyone

I am relatively new to Nhibernate. I have been trying to do something using Criteria objects and have found it very hard to do. It’s a relatively simple thing to do using HQL or Sql but we prefer Criteria objects as they retain the mappings.

The problem:

I need to check whether a particular user has a particular role. I try to do the following

using (NHibernate.ISession session = NHibernateSessionManager.Instance.GetSession<UserRelationship>(appSession))
            {
                ICriteria criteria = session.CreateCriteria(typeof(UserRelationship));
                criteria.CreateCriteria("User", "usr", JoinType.LeftOuterJoin);
                criteria.Add(Expression.Eq("usr.Id", userId));
                criteria.CreateCriteria("ExternalRole", "ex", JoinType.LeftOuterJoin);
                criteria.Add(Expression.Eq("ex.Name", roleName));
                relationshipList = criteria.List<UserRelationship>();   
         
                              

            }

but that gives me following error: System.Data.SqlClient.SqlException: The correlation name 'ex1_' is specified multiple times in a FROM clause.

 It works fine when I run the criteria for ClientExternalRoleRelationship as it doesn’t have to join to the ExternalRole table twice but because UserRelationship is a base class for ExternalRoleRelationship and ClientExternalRoleRelationship . How do I tell it not to use same alias twice but to use a different one for each join? I even tried FetchMode.Join etc with the "Role" in the association Path. That works fine but if I then try and provide a filter on the Role object to check for a particluar RoleName nhibernate doesn’t like it and thinks that I am accessing a property on the root object (UserRelationship)

Help will be greatly appreciated.

The mapping files are attached

                                                      .

Classification: UNCLASSIFIED

<<UserRelationship.hbm.xml>> <<ExternalUserRelationships.hbm.xml>> <<InternalUserRelationships.hbm.xml>> <<ProgrammeRole.hbm.xml>> <<Role.hbm.xml>> <<User.hbm.xml>>