How to make conditional join in nhibernate queryover using to_char()?

Question

We have 2 tables/classes lets call them NewOne and OldOne. OldOne has Id type of int and NewOne has property OldValues tape of OldOne and string SourceType which is mapped (database is Oracle):

<many-to-one name="OldValues" column="SOURCE_ID"   />
<property name="SourceType " column="Source_Type " type="AnsiString" />

We want to get data from NewOne and OldOne when SourceType='Old', but the problem is that ID column of OldOne table is number and Source_Id column is varchar which stores other types of ids like names etc. We cannot change type of ID neither Source_ID columns. Such join can be easily done in sql using to_char() function:

left outer join OldOne old_     
on this_.SOURCE_ID = to_char(old_.ID)
and (this_.Source_Type = 'Old') 

But I cannot find equivalent of to_char in QueryOver. Below is the version with conditional join but without to_char():

 OldOne OldOneAlias = null;
 NewOne NewOneAlias = null;
 return this.ActiveSession.QueryOver<NewOne>(() => NewOneAlias)
                .JoinAlias(r => r.OldValues,
                           () => OldOneAlias,
                           JoinType.LeftOuterJoin,
                           Restrictions.Eq(
                               Projections.Property(() => NewOneAlias.SourceType), "Old")) 

Is there any way to create query with to_char using QueryOver?

Update:
I have found a little bit ugly solution here: "Safe" TO_NUMBER() and after changing my mapping everything works:

    <many-to-one name="OldValues" formula="COALESCE(TO_NUMBER(REGEXP_SUBSTR(SOURCE_ID, '^\d+')), 0)"   />   

The main problem was that previous query ignores "and (this_.Source_Type = 'Old')" and tries to make join with OldOne even when Source_Type != Old and SOURCE_ID isn't number.Not sure if it is only Oracle's issue.

Answer 1

There are in general two ways in this scenario.

I. HQL, Cartesian product, custom WHERE

In case, we do not have mapping between our NewOne and OldOne, we can profit (as always) from powerful feature set of NHibernate.

• 14.2. The from clause (small cite and snippet)

Multiple classes may appear, resulting in a cartesian product or "cross" join.

from Formula, Parameter
from Formula as form, Parameter as param

• Oracle dialect is supporting to_char since Oracle8iDialect

(small snippet)

protected virtual void RegisterFunctions()
{
    ...
    RegisterFunction("to_char", new StandardSQLFunction("to_char"
                              , NHibernateUtil.String));

Having this in our toolbelt - this kind of query should work:

var session = ... // get your session
// HQL
var hql = 
  // firstly use SELECT to define the resultset columns
  // - to use Transformer, do not forget to add the AS statement
  @"SELECT n.ID AS ID " +
  // the trick with a CROSS join
  " FROM NewOne AS n, OldOne AS o " +
  // here should be some OR handling if we want to result
  // LEFT style join, i.e. if oldValue is null (skipped below)
  " WHERE n.SourceId = to_char(o.ID) ";

var query = session.CreateQuery(hql);
// result transfomer if we do not want to get List<Object[]>
// NewOneDTO could have property set from both old and new
query.SetResultTransformer(Transformers.AliasToBean<NewOneDTO>());
// here we go
var list = query.List<NewOneDTO>();

NOTE: not fully sure which should be converted with to_char (old, new) but the logic is clear. What we would for sure need, is that the mapping will Contain

<property name="SourceId" column="SOURCE_ID"   />

II. formula="" and property-ref="" mapping

As Andrew Whitaker already pointed out in the comment, we can also adjust the mapping. But we even do not need special features of DB engine (e.g. computed column), because NHibernate provide us with:

• 5.1.9. property - formula=""

small snippet and cite:

<property
    ...
    formula="arbitrary SQL expression"  (5)

(5) formula (optional): an SQL expression that defines the value for a computed property. Computed properties do not have a column mapping of their own.

• 5.1.10. many-to-one - property-ref=""

small snippet and cite:

<many-to-one
    ...
    property-ref="PropertyNameFromAssociatedClass"     (7)

(7) property-ref: (optional) The name of a property of the associated class that is joined to this foreign key. If not specified, the primary key of the associated class is used.

Having these in our growing toolbelt, we can introduce mapping on the old one:

<property name="ConvertedSourcId" formula="to_char(ID)" 
                                  insert="false" update="false" />

And new can have an explicit reference:

<many-to-one name="OldValue" column="SOURCE_ID" property-ref="ConvertedSourcId"
                                  insert="false" update="false" />

In both cases, we need readonly solution. But for getting old data, it is more than expected, is not it?

Finally, with this mapping, our QueryOver should work with standard JoinAlias...