2

Not sure how to accomplish the date expression in the example below in the last line. A similar query fails to execute. I assume I'll have to use some sort of Projections SqlFunction? Anyone help?

Session.QueryOver(() => myobj)
.JoinAlias(() => myobj.Object, () => o1)
.Where(() => myobj.SomeInt > o1.Date.Subtract(someTime).Minutes);
Suedeuno
  • 389
  • 7
  • 21

1 Answers1

3

We need to convert your date operations from C# methods into DB Server functions. In case of MS SQL Server, we can use the DATEDIFF function:

DATEDIFF(datepart, startdate, endate)

Expecting that your object has a column Date so we can create this ICriterion

DateTime someTime = ...;
var sqlString = " DATEDIFF( mi, ?, [Date]) < {alias}.SomeInt ";
var sql = NHibernate.Criterion.Expression.Sql(sqlString
              , someTime
              , NHibernate.NHibernateUtil.DateTime);

Having this SQL statement as ICriterion we can adjust your snippet:

Session
  .QueryOver(() => myobj)
  .JoinAlias(() => myobj.Object, () => o1)
  .Where(sql); // sql = our ICriterion above

This is just a quick example... starting point how to use NHibernate API to do it... some subquery should be more suitable to avoid using explicit SomeInt column name...

Radim Köhler
  • 122,561
  • 47
  • 239
  • 335
  • I've worked your example with an adjustment to your sql string now as " DATEDIFF( mi, ?, {alias}.SomeDate) between {alias}.SomeInt and {alias}.AnotherInt" to fit what I'm working with. My question is in my real code I have several aliases and .SomeDate and .SomeInt are aliases of different types. What would I do to make these assign the correct aliases? Thanks for you help. – Suedeuno Jan 13 '13 at 19:59
  • My answer is just a hint to your generic question. Just a know how, starting point - showing the way and power of SqlExpression. Well, try to create a detached `subQuery` of your `o1` returning the result of *DATEDIFF*. In this case `{alias}` will be replaced with the `o1` alias. Next step, add `.Where(Subqueries.PropertyGt("SomeInt", subQuery.DetachedCriteria))`. Find *'subquery'* working example here http://stackoverflow.com/a/14080092/1679310 – Radim Köhler Jan 14 '13 at 05:45