Truncate DateTime in NHibernate QueryOver SelectGroup

Question

I have a fairly run-of-the-mill QueryOver query containing the following,

.SelectList(list => list
    .SelectGroup(() => txn.GroupField)
    .SelectGroup(() => txn.Date))
.List<object[]>()

This query works as expected however I now have a requirement to group by the truncated Date as some of the Date's for these objects may contain a time component. This seems like it should be a trivial change but I can't find a way that is supported by NHibernate.

The obvious solution would be the change below but is not supported.

.SelectGroup(() => txn.Date.Date))

Any ideas?

Thanks

Leda

Answer 1

Your QueryOver could look like this:

Status alias = null;
var query = QueryOver.Of(() => alias)
    .Select(Projections.GroupProperty(
        Projections.SqlFunction("date", NHibernateUtil.Date, Projections.Property(() => alias.Date))));

Output (pseudo sql):

SELECT 
           ...
FROM       [Status] this_
GROUP BY   dateadd(dd, 0, datediff(dd, 0, this_.Date))

Hope this helps, cheers!

Umbles

Thanks a lot, MonkeyCoder. I tried to use SelectList()/.SelectGroup() with Projections but failed. – Silma

Answer 2

You might want to add a helper property to your map, using the Formula command, to be able to use the date (instead of datetime) in queries.

here's an example from my code; it uses a decimal value, but this works fine with any subquery:

model class has this property, to be mapped to a formula:

public virtual decimal Profit
{
    get { return this.SellPrice - this.Cost; }
    set { return; }
}

fluentNHibernate map:

//SellPrice and Cost are columns in the object's table
Map(v => v.Profit).Formula("(SellPrice - Cost)"); // this field is calculated, not read

be sure to put the formula between () brackets though.

If you'd make your formula a select query that trunks the datetime into a date, you could then group by that property in your query.

Impeachment