NHibernate inner join gives "Path expected for join"

Question

I have three Tables:

- Person
- User
- PersonSecret

where PersonSecret reference to Person and User:

<class name="PersonSecret" table="PersonSecret" lazy="false"  >
<id name="Id" column="Id" type="Guid">      
                <generator class="assigned"/>
</id>
...
<many-to-one  name="Person" class="Person" foreign-key="FK_Person_PersonSecret" lazy="proxy" fetch="select">
    <column name="PersonId"/>
</many-to-one>
<many-to-one  name="User" class="User" foreign-key="FK_User_PersonSecret" lazy="proxy" fetch="select">
    <column name="UserId"/>
</many-to-one>

This is the mapping from User to PersonSecret:

<set name="PersonSecrets" lazy="true" inverse="true" cascade="save-update" >
<key>
    <column name="UserId"/>
</key>
<one-to-many class="PersonSecret"/>

And this from Person to PersonSecret:

<set name="PersonSecrets" lazy="true" inverse="true" cascade="save-update" >
<key>
    <column name="PersonId"/>
</key>
<one-to-many class="PersonSecret"/>

Now, i try to select all Persons, which has a Entry in PersonSecret for a specific User:

var query = this.Session.CreateQuery(@"from Person a inner join PersonSecret b 
          where b.UserId = :userId and b.RemindeBirthday = :remind");

This gives me now the ExceptionMessage: "Path expected for join"

Can someone help me, what I am doing wrong? - Thanks.

Answer 1

There are a couple of issues with your HQL query:

• When using HQL you need to reference the names of the properties on your model classes instead of the column names they map to. In this case you should reference the PersonSecret.User.Id property in your where clause, instead of the UserId column.
  
• You should also specify the Person.PersonSecrets property in the join clause, otherwise NHibernate won't know which columns to join on.
• You should specify your additional filter as a join condition using the with keyword instead of in the where clause.

Here's the correct version:

var query = this.Session.CreateQuery(
    @"from Person as a inner join a.PersonSecrets as b with b.User.Id = :userId and b.RemindeBirthday = :remind");

Related resources:

• HQL: The Hibernate Query Language - Associations and joins