These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]

Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 
Author Message
 Post subject: Criteria generate joins in wrong sequence
PostPosted: Fri Nov 25, 2011 5:21 pm 

Joined: Fri Nov 25, 2011 4:58 pm
Posts: 1
I'm trying to build a Criteria but the SQL that is generated has the joins in wrong order.

Here is an extract from my code:

const string businessUnitAlias = "BusinessUnit";
const string buildingAlias = "Building";
const string consumptionQuestionnaireAlias = "ConsumptionQuestionnaire";
const string applicationUserAlias = "ApplicationUser";
const string consumptionQuestionnaireAlias2 = "ConsumptionQuestionnaire";
const string applicationUserAlias2 = "ApplicationUser";

var criteria = session.CreateCriteria<BusinessUnitBuilding>();
criteria.CreateCriteria(Reflect<BusinessUnitBuilding>.PropertyName(x => x.Building), buildingAlias, JoinType.InnerJoin);

var buCriteria = criteria.CreateCriteria(Reflect<BusinessUnitBuilding>.PropertyName(x => x.BusinessUnit), businessUnitAlias,
var questCriteria = buCriteria.CreateCriteria(Reflect<BusinessUnit>.PropertyName(x => x.ConsumptionQuestionnaires),
consumptionQuestionnaireAlias, JoinType.LeftOuterJoin,
Restrictions.EqProperty(buildingAlias+"."+Reflect<Building>.PropertyName(x => x.Id),
Reflect<ConsumptionQuestionnaire>.PropertyName(x => x.Building)));
questCriteria.CreateCriteria(Reflect<ConsumptionQuestionnaire>.PropertyName(x => x.ApplicationUser),
applicationUserAlias, JoinType.LeftOuterJoin);

The SQL that is generated is:
SELECT this_.DistributionYear as y0_, this_.DistributionPercentage as y1_, this_.IsPrincipalOccupant as y2_,
businessun2_.BusinessUnitId as y3_, businessun2_.Name as y4_, this_.BusinessUnitBuildingId as y5_,
building1_.BuildingId as y6_, applicatio4_.Code as y7_, applicatio4_.FirstName as y8_, applicatio4_.LastName as y9_
FROM BusinessUnitBuilding this_
inner join BusinessUnit businessun2_ on this_.BusinessUnitId=businessun2_.BusinessUnitId
left outer join ConsumptionQuestionnaire consumptio3_ on businessun2_.BusinessUnitId=consumptio3_.BusinessUnitId
and ( building1_.BuildingId = consumptio3_.BuildingId )
left outer join ApplicationUser applicatio4_ on consumptio3_.ApplicationUserId=applicatio4_.ApplicationUserId
inner join Building building1_ on this_.BuildingId=building1_.BuildingId
WHERE businessun2_.BusinessUnitId = @p0
ORDER BY y0_ desc

The problem is the "inner join Building building1_ on this_.BuildingId=building1_.BuildingId" is too far because
the previous join already make reference to the building1_ alias and it is not existing yet.

When I execute the query in SQL Studio having move the inner join at the right place, the resulting data is exactly
what I expect.

How can I force this inner join to arrive earlier in the query?

Thank you

Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.