NHibernate: Many to one and Fetch=join

[Hawkwood]

I have send my problems to Ayende, who answered me for the first
point :

The problem is with the fetch mode « join » on a many-to-one
association.

FIRST :

I have two class NHReport and NHEmployee, there is the mapping :

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
namespace="PersistenceComparison.NHibernate"
assembly="PersistenceComparison.NHibernate">
<class name="NHReport" table="Reports">
<id name="Id" column="ID" type="Int32">
<generator class="assigned" />
</id>
<property name="Date" column= "date" type="DateTime"/>
<property name="Text" type="String" length="8000" />
<many-to-one name="ParentEmployee"
class="NHEmployee" lazy="false" fetch="join"
column="employeeId" cascade="all"/>
</class>
</hibernate-mapping>

AND

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
namespace="PersistenceComparison.NHibernate"
assembly="PersistenceComparison.NHibernate">
<class name="NHEmployee" table="Employees">
<id name="Id" column="ID" type="Int32">
<generator class="assigned" />
</id>
<property name="Name" column= "name" type="String" length="255"/>
<property name="Salary" type="Int32"/>
</class>
</hibernate-mapping>

1) If i execute this code :
NHReport n = session.Get<NHReport>(2);
I have this SQL :
SELECT nhreport0_.ID as ID0_1_, nhreport0_.date as date0_1_,
nhreport0_.Text as Text0_1_, nhreport0_.employeeId as employeeId0_1_,
nhemployee1_.ID as ID1_0_, hemployee1_.name as
name1_0_, nhemployee1_.Salary as Salary1_0_, nhemployee1_.companyId as
companyId1_0_
FROM Reports nhreport0_ left outer join Employees nhemployee1_ on
hreport0_.employeeId=nhemployee1_.ID
WHERE nhreport0_.ID=?p0; ?p0 = '2'

2) If i execute this code :
ICriteria crit = session.CreateCriteria(typeof(NHReport));
IList result = crit.List();
I have this SQL :
SELECT this_.ID as ID0_1_, this_.date as date0_1_, this_.Text as
Text0_1_, this_.employeeId as employeeId0_1_, nhemployee2_.ID as
ID1_0_, nhemployee2_.name as name1_0_, nhemployee2_.Salary as
Salary1_0_, nhemployee2_.companyId as companyId1_0_
FROM Reports this_ left outer join Employees nhemployee2_ on
this_.employeeId=nhemployee2_.ID

3) But if i execute this :
String requete = "SELECT r FROM NHReport r WHERE r.id=1";
IList<NHReport> result1 =
session.CreateQuery(requete).List<NHReport>();
I have this two SQL Query :
SELECT nhreport0_.ID as ID0_, nhreport0_.date as date0_,
nhreport0_.Text as Text0_, nhreport0_.employeeId as employeeId0_
FROM Reports nhreport0_
WHERE (nhreport0_.ID=1 )

SELECT nhemployee0_.ID as ID1_0_, nhemployee0_.name as name1_0_,
nhemployee0_.Salary as Salary1_0_, nhemployee0_.companyId as
companyId1_0_
FROM Employees nhemployee0_
WHERE nhemployee0_.ID=?p0; ?p0 = '1'

If i specify this
String requete = "SELECT r FROM NHReport r LEFT JOIN FETCH
r.ParentEmployee pe WHERE r.id=1";
I have only one query :
SELECT nhreport0_.ID as ID0_1_, nhreport0_.date as date0_1_,
nhreport0_.Text as Text0_1_, nhreport0_.employeeId as employeeId0_1_,
nhemployee1_.ID as ID1_0_, hemployee1_.name as
name1_0_, nhemployee1_.Salary as Salary1_0_, nhemployee1_.companyId as
companyId1_0_
FROM Reports nhreport0_ left outer join Employees nhemployee1_
on hreport0_.employeeId=nhemployee1_.ID
WHERE nhreport0_.ID=?p0; ?p0 = '1'

The question is why, if y specify fetch « join » and lazy « false »,
when i execute this HQL "SELECT r FROM NHReport r WHERE r.id=1",
Nhibernate quering this in two SQL ?

Ayend explain that because HQL ignores any hints we put in the
mapping.

For my example that's true for the fetch attributes but not for the
lazy.Because if i specify lazy=false and i execute this "SELECT r FROM
NHReport r WHERE r.id=1" i have two query, the second is automaticaly
executed by nhibernate.

How to know what mapping attributes are taken into account by the
HQL?

SECOND :
In our domain model, we have the same scenario of use of many-to-one
and fetch « join ».

When i execute something like this "SELECT r FROM NHReport r LEFT JOIN
FETCH r.ParentEmployee", i dont know why, but for some, a second query
is executed to get the « Employees » like this :

**
SELECT nhemployee0_.ID as ID1_0_, nhemployee0_.name as name1_0_,
nhemployee0_.Salary as Salary1_0_, nhemployee0_.companyId as
companyId1_0_
FROM Employees nhemployee0_
WHERE nhemployee0_.ID=?p0; ?p0 = '1'

I can’t find the reason of that. Would you know what this could come?
The data contain’s in « Employees » aren’t different.

If i excute this "SELECT r FROM NHReport r LEFT JOIN FETCH
r.ParentEmployee pe WHERE r.id=1" i have only one query
And if execute this "SELECT r FROM NHReport r LEFT JOIN FETCH
r.ParentEmployee pe WHERE r.id=2" i have two query, the first query is
the same as the previous and the second is like **.

The problem is the same if i use this syntax :
session.CreateCriteria(typeof(T)).Add(Expression.Eq("id",
1)).List<T>(); --> only one query
and
session.CreateCriteria(typeof(T)).Add(Expression.Eq("id",
2)).List<T>(); --> two query

I have search near the return of the first query, but the result is
the same in the twice. (there is no duplicate line, there are data for
both, There is only one possible « Employees » for each case).

Ayend explain that because HQL ignores any hints we put in the
mapping. I'm ok with the first point but for the second i don't
understand why Nhiernate needs two query whereas in the first it has
all the data needed ?

Thank you in advance for your help.

[Hawkwood]

I found a track. For the second point, in our domain model, the column
use for the many to one, is a VARCHAR.

Example of ID for the employee :

C234
E546
0123
6789

When the id contains only numbers, i have juste one query, but if
contains letters, the second query is executed.
The new question is why ?

[Hawkwood]

Another discovery if I use LinqToNhibernate to query, just one sql
query is executed.

var report = from report in session.Linq< NHReport >()
where report.IdCompteBancaire == 1
select report ;

You think it's a bug of Nhibernate ?

[Hawkwood]

Nobody has any idea why when the key is of type
chain, NHibernate performs two queries ?

This produces several optimization problem when I query a large number
of objects.

Please Help.