Mapping to Date part of Sql DateTime

Question

I have a requirement to be able to fetch data related to a particular Date as opposed to DateTime, for a particular colmumn.

Is it at all possible to Map the related objects according to an extracted date part of a date time? Where no explicit foreign key relationship exists between the two entities?

Relating it to SQL, I would like it to generate a query as follows:

SELECT * 
FROM Transactions trans 
    INNER JOIN TransactionDetails details
       ON trans.DatePerformed = CAST(details.datetimedetails AS DATE)

Answer 1

The way to go in case we need the skip the mapped relationship mapping (when CROSS JOIN with WHERE clause is needed) we can use HQL (only way, check e.g. here:)

• How to join Two tables of two non relashinship defined columns using Nhibernate QueryOver
• Nhibernate QueryOver JoinAlias UnRelated Entity

The HQL sytax then would be:

var hql = " SELECT trans " +
          " FROM TransactionEntity trans "
          "    , TransactionDetails details " + // CROSS JOIN without any ON
          " WHERE trans.DatePerformed = CAST(trans.datetimedetails as Date) " +
          "";

var query = session.CreateQuery(hql);
var results = query.List<TransactionEntity>();

Answer 2

The way with QueryOver would be:

TransactionEntitytrans = null;
TransactionDetails details = null;

var results = session.QueryOver<TransactionEntity>(() => trans)
    .JoinQueryOver(() => trans.Details, () => details, JoinType.InnerJoin
        , Restrictions.EqProperty(
            Projections.Property<TransactionEntity>(_ => trans.DatePerformed ),
            Projections.Cast(
              NHibernate.NHibernateUtil.Date, 
              Projections.Property<Occupation>(_ => details.datetimedetails ))
            )
    )
    .List<TransactionEntity>();

What we are effectively doing is the usage of the forth parameter of the .JoinQueryOver(). This parameter is a restriction (ICriterion withClause) and is added to the JOIN with AND operator

INNER JOIN TransactionDetails details
   ON  trans.ID = details.TransactonID
   // here is injected the with clause
   AND trans.DatePerformed = CAST(details.datetimedetails AS DATE)

And in this withClause we are just creating restriction with a small trick Restrictions.EqProperty(), because this takes to projections

EqProperty(IProjection lshProjection, IProjection rshProjection)

(so we do not compare two properties but some other projections). And then we just use CAST to get the required result