We use NHibernate Criteria for complex search query. In multiple places we need to use Expression.Sql.
Sample (not working, just sample) code:
//Parent entity - Parent table in database
public class Parent
{
public int ID
{
get;set;
}
public string ParentName
{
get;set;
}
}
//Child entity - one-to-one linked Child table in database
public class Child: Parent
{
public string ChildName
{
get;set;
}
}
-- SQL Query which we need to obtain from NHibernate
SELECT * FROM Child this JOIN Parent p ON this.id = p.id WHERE p.ParentName = SomeSqlFunction(?)
//Here is problem. We can't get alias name for Parent without overhead in form of useless joins.
SessionService.GetSession().AddCriteria<Child>.Expression.SQL.("{alias}.ParentName = SomeSqlFunction(?) ", someData)
We are trying to find ways to define alias name for generated by NHibernate SQL code or find alias name from criteria, but not prevail. As workaround we use ParentName without alias, but it can make problems if another entity with same column name will appear in future.