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),
Restrictions.EqProperty(buildingAlias+"."+Reflect<Building>.PropertyName(x => x.Id),
Reflect<ConsumptionQuestionnaire>.PropertyName(x => x.Building)));
questCriteria.CreateCriteria(Reflect<ConsumptionQuestionnaire>.PropertyName(x => x.ApplicationUser),
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?