Automatic join with NHibernate / Many-to-one / HQL

Question

Hello everyone,

I'm working on a .NET C# project on which I had to use NHibernate Mapping Attributes to map my objects to my tables in my database.

Now let's explain what my problem is.

I have two mapped classes, for example ClassA and ClassB. In my database, table A contains a foreign key referencing the primary key of table B. Hence, I have added to ClassA an instance of ClassB mapped in many-to-one:

private ClassB b;
[ManyToOne(0, Name = "B", Column = "ID_TABLE_B_TABLE_A", Class = "ClassB", Update = false, Insert = false)]
public virtual ClassB B 
{ 
    get { return b; } 
    set { b= value; } 
}

Now, I want to check the value of a field of ClassB when I'm accessing ClassA. I write the query in HQL:

Session.CreateQuery("select a.Id from ClassA a where a.ClassB.Name = 'xxx' ");

Here's the generated SQL:

select tablea0_.ID_TABLE_A as col_0_0_ 
from TABLE_A tablea0_, TABLE_B tableb1_
where tablea0_.ID_TABLE_B_TABLE_A = tableb1_.ID_TABLE_B 
and tableb1_.NAME_TABLE_B='xxx'

I thought this kind of HQL query was supposed to generated a join statement rather than a where statement, as I have defined a many-to-one association between the two classes. Something like this would've been better:

select tablea0_.ID_TABLE_A as col_0_0_ 
from TABLE_A tablea0_ 
left join TABLE_B tableb1_ on tableb1_.ID_TABLE_B = tablea0_.ID_TABLE_B_TABLE_A 
where tableb1_.NAME_TABLE_B='xxx'

In my opinion, join looks cleaner to where. I would like to know if there is a way to set up the behaviour of NHibernate accordingly, without specifying the join statement explicitly in the HQL query.

Any help would be appreciated !

Answer 1

It's been a while since I worked with NHibernate, but I don't think you can force it to do this.

You can try specifying "fetch=join" in the many-to-one mapping, but if memory serves, this only changes the strategy NHibernate uses when fetching related entities in general, not how it translates a custom HQL query to SQL.

But why even worry about it? In 99% of the cases I actually looked at the generated SQL, it was because incorrect SQL was generated (usually because I made a mistake), or to investigate performance issues.

Answer 2

The join works like this:

Session.CreateQuery("select a.Id from ClassA a join a.ClassB b where b.Name = 'xxx' ");
Session.CreateQuery("select a.Id from ClassA a left join a.ClassB b where b.Name = 'xxx' ");
Session.CreateQuery("select a.Id from ClassA a left outer join a.ClassB b where b.Name = 'xxx' ");

Of course, it's now for you to figure out which join works best for you. :)

More information @ http://docs.jboss.org/hibernate/stable/core/reference/en/html/queryhql.html#queryhql-joins

On a side note, if you want to be one of the cool kids, you could always use Linq to NHibernate:

var result = Session.Linq<ClassA>().Where(a => a.B.Name == 'xxx').ToList();

Would generate an inner join query.

Answer 3

The generated SQL depends on the datababase dialect. Oracle did not support the ANSI join syntax until 9i so it's possible that the Oracle dialect in NHibernate still uses the old syntax for inner joins. What SQL is produced by a left join? If it's tablea0_.ID_TABLE_B_TABLE_A += tableb1_.ID_TABLE_B then it's using the old syntax for those as well.

Answer 4

What if you wanted to acces a property of class A While accessing class B? I mean, in this case, class A would be a ISet property in Class B, right? How would I be able to get, for example, rows with a classA.name = 'XX' while working on Class B? is this possible?