Hibernate version: 1.2 Beta3
Full stack trace of any exception that occurs:
Name and version of the database you are using:
Firebird 1.5.3
Standard / basic generated SQL for Firebird using criteria
NHibernate: SELECT first 100 this_."ISBNNUMBER" as ISBNNUMBER1_0_0_, this_."TITLE" as TITLE2_0_0_, this_."DATEPURCHASED" as DATEPURC3_0_0_, this_."NUMBEROFPAGES" as NUMBEROF4_0_0_, this_."CATEGORYID" as CATEGORYID5_0_0_, this_."SUBCATEGORYID" as SUBCATEG6_0_0_ FROM BOOKS this_ ORDER BY this_."TITLE" asc
Now if the criteria contains joins for some strange reason the Order by does not include the Table alias any more
SELECT first 100 this_."ISBNNUMBER" as y0_, this_."TITLE" as y1_, this_."DATEPURCHASED" as y2_, this_."NUMBEROFPAGES" as y3_, this_."CATEGORYID" as y4_, this_."SUBCATEGORYID" as y5_, categories1_."DESCRIPTION" as y6_ FROM BOOKS this_ left outer join CATEGORIES categories1_ on this_."CATEGORYID"=categories1_."CATEGORYID" and this_."SUBCATEGORYID"=categories1_."SUBCATEGORYID"
ORDER BY y1_ asc
Basically the problem is that when using joins the alias for the property seems to be lost (and it does not use the default “this”).
Partial code where the criteria gets created:
Code:
public ICriteria GetBasicCriteria()
{
ICriteria _criteria = Session.CreateCriteria(typeof (BOOKS));
if (_criteria == null) throw new Exception("Failed to create criteria for BOOKS");
// --- CustomView Criteria definition ---
_criteria.CreateCriteria("CATEGORIES", "CATEGORIES", NHibernate.SqlCommand.JoinType.InnerJoin);
_criteria.SetProjection(GetProjectionList());
_criteria.SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean(typeof (CustomView1)));
return _criteria;
}
protected virtual IProjection GetProjectionList()
{
ProjectionList _projectionList = Projections.ProjectionList();
_projectionList.Add(NHibernate.Expression.Property.ForName("ISBNNUMBER"), "ISBNNUMBER");
_projectionList.Add(NHibernate.Expression.Property.ForName("TITLE"), "TITLE");
_projectionList.Add(NHibernate.Expression.Property.ForName("DATEPURCHASED"), "DATEPURCHASED");
_projectionList.Add(NHibernate.Expression.Property.ForName("NUMBEROFPAGES"), "NUMBEROFPAGES");
_projectionList.Add(NHibernate.Expression.Property.ForName("CATEGORYID"), "CATEGORYID");
_projectionList.Add(NHibernate.Expression.Property.ForName("SUBCATEGORYID"), "SUBCATEGORYID");
_projectionList.Add(NHibernate.Expression.Property.ForName("CATEGORIES.DESCRIPTION"), "CTG_DESCRIPTION");
return _projectionList;
Workaround:
If I set an alias for the root table like shown below then it works. (add the table alias to the order by)
ICriteria _criteria = Session.CreateCriteria(typeof (BOOKS), "Books");