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, JoinType.InnerJoin); 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
|