CODEWITHSUNDEEP
c#nhibernatenhibernate-mapping
dkeck
dkeck

Reputation: 11

NHibernate: Using createSQLQuery with one-to-one mapping causing extra queries

I am using the following code in my query

var query = session.CreateSQLQuery(sqlQuery)
    .AddEntity("g", typeof(AllegroGoalContract))
    .AddJoin("gd", "g.GoalDetail")
    .SetInt32("max", max.Value)
    .SetGuid("callerId", ServerContext.Current.TeamMemberUniqueId)
    .SetInt32("ver", lastChange);

It is using the following mapping:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" schema="Methodology" >
    <class name="SixDisciplines.AllegroGoalContract, SixDisciplines" table="Goal" lazy="false">
        .
        .
        <one-to-one name="GoalDetail" class="SixDisciplines.AllegroGoalDetailContract, SixDisciplines" constrained="false"/>
        .
        .
    </class>
</hibernate-mapping>

And here is a small sample of the SQL. The actual SQL is using features specific to Sql Server, but this simplified SQL demonstrates the same issue:

SELECT
g.UniqueId AS {g.UniqueId},
g.Description AS {g.Description},
g.StatusId AS {g.Status},
gd.UniqueId AS {gd.UniqueId},
gd.ActualEnd AS {gd.ActualEnd}
FROM Methodology.Goal g
LEFT JOIN Methodology.GoalDetail gd ON g.UniqueId = gd.UniqueId

Now my sql query returns all of the AllegroGoalContract instances along with the GoalDetail one-to-one association. Notice that the mapping for the one-to-one association has constrained="false" set indicating that there does not have to be a GoalDetail for every AllegroGoalContract.

When I run this code, I see my query run but then I see nhibernate issue a separate query for every AllegroGoalContract instance where my query returned null for the GoalDetail. I suspect this is because nhibernate does not know if the null was returned because it was a lazy load or because it really does not exist.

So how can I tell nhibernate that the null from the CreateSQLQuery call for GoalDetails really means that they are not there so don't go and try and fetch them again.

Upvotes: 1

Views: 1068

Answers (1)

Mr Mush
Mr Mush

Reputation: 1538

The NHibernate mapping node - <one-to-one/> has an attribute called fetch, default value is - select which make a separate query for each adjunct entity. If you want NHibernate to use SQL Join change to - 

<one-to-one name="GoalDetail" class="SixDisciplines.AllegroGoalDetailContract, SixDisciplines" constrained="false" fetch="join" outer-join="true"/>

Upvotes: 1

Related Questions