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.

Enrico Campidoglio · Accepted Answer · 2011-06-29T12:57:07.937

4

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

edited Jun 29 '11 at 12:57

answered Jun 29 '11 at 11:44

Enrico Campidoglio

56,676
12
126
154

@t.kehl I was a little to quick in answering this one. I updated my answer with a different solution. – Enrico Campidoglio Jun 29 '11 at 12:44
@Enrico - Can you explain me, how to do this: "specify the Person.PersonSecrets property in the join clause"? – BennoDual Jun 29 '11 at 13:55
@t.kehl I've provided you the correct version of the query in my answer. The join clause should be `from Person as a inner join a.PersonSecrets as b`. – Enrico Campidoglio Jun 29 '11 at 13:59
@Enrico - ah sorry - it is working - I haven't seen the s of PersonSecrets :-) - Now it is clear how this works. - Thank you very much for your help. – BennoDual Jun 29 '11 at 14:07

score -1 · Answer 2 · answered Jun 29 '11 at 11:38

-1

you have forgotten the "ON" term.

inner join PersonSecret b ON b.PersonId = a.PersonId

answered Jun 29 '11 at 11:38

Anubis

2,484
2
22
33

1

HQL doesn't support the SQL join syntax. – Enrico Campidoglio Jun 29 '11 at 11:51
then use CreateSQLQuery instead of CreateQuery – Anubis Jun 29 '11 at 13:25

NHibernate inner join gives "Path expected for join"

2 Answers2