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)

Firstly, you should have an `ON` clause included on the join not an `AND`. Secondly is `DatePerformed ` a datetime or a date? can you show some sample rows and your desired output? — Tanner, Jun 11 '15 at 08:34

score 1 · Answer 1 · answered Jun 11 '15 at 08:42

1

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

answered Jun 11 '15 at 08:42

Radim Köhler

122,561
47
239
335

Thanks @Radim. Good detail in your explanation. I am trying it out now. – tinonetic Jun 11 '15 at 09:51
Hi @Radim, what about the actual NHibernate mappings? Are you saying I wont need them between the two entities? – tinonetic Jun 11 '15 at 10:08
Not sure if I understand. Well I do not. If you are asking "should we define the JOIN explicitly" ... NO this will be done for us by NHibernate. it comes from relationship... it cannot be avoided... is it your question? – Radim Köhler Jun 11 '15 at 10:09
I realise, you were answering before I did an edit in the expected SQL statement. Sorry about that. I had mistakenly joined on Id as well. I meant to join on that date field. ...without a defined relationship on the existing data – tinonetic Jun 11 '15 at 10:14
1

OK, so your question is *"Can I query **two** entities, with my OWN custom JOIN clause?"* the answer is **NO** with `QueryOver` or `ICriteria`, - but yes with `HQL`. Please, check this explanation *(larger than comment;)* http://stackoverflow.com/q/28689706/1679310 or here http://stackoverflow.com/q/26884917/1679310, Also, the concept shown in this answer with HQL would be very similar ;) – Radim Köhler Jun 11 '15 at 10:16
Thanks for the links. Would you please include your last comment in your answer, so I mark it as an answer? – tinonetic Jun 11 '15 at 11:06
Had to add a bit more complex answer.. with all the HQL details ;) Good luck with amazing NHibernate ;) sir! – Radim Köhler Jun 11 '15 at 11:40

score 1 · Accepted Answer · edited May 23 '17 at 10:26

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:)

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>();

Mapping to Date part of Sql DateTime

2 Answers2