NHibernate How to convert this SQL Query to QueryOver or Linq

Question

I could really use some guidance on how to take this sql query to NHibernate QueryOver. My attempts were to use Linq as I am a bit more familiar with Linq. The project we are working on is using QueryOver so it may be best to stay that route for now.

Working SQL Query: Rounds DateTime to 1 minute and groups/sorts/counts properly...

select dateadd(minute,(datediff(minute,0,dateTimeColumn)/1)*1,0), COUNT(*)
from PartData
group by dateadd(minute,(datediff(minute,0,dateTimeColumn)/1)*1,0)
order by dateadd(minute,(datediff(minute,0,dateTimeColumn)/1)*1,0)

Returns expected results of :

2012-08-31 00:00:00.000, 3
2012-08-31 00:01:00.000, 4
2012-08-31 00:02:00.000, 3
2012-08-31 00:03:00.000, 3
2012-08-31 00:04:00.000, 4
2012-08-31 00:05:00.000, 3
2012-08-31 00:06:00.000, 3
2012-08-31 00:07:00.000, 4
2012-08-31 00:08:00.000, 3

EDIT

I am getting closer...

private IQueryOver<entities.PartData, entities.PartData> PartPerHourQuery(ISession session)
{
    return session.QueryOver<entities.PartData>()
        .Select(
            Projections.Alias(
                Projections.GroupProperty(
                    Projections.SqlFunction(
                        new SQLFunctionTemplate(NHibernateUtil.DateTime, "DateAdd(mm,1,Date)"),
                        NHibernateUtil.DateTime, _partsDate))
                , "Date"),
            Projections.Alias(Projections.RowCount(), "Count"))
                .TransformUsing(Transformers.AliasToBean<entities.PartsPerHour>());
}

Yields the following SQL:

SELECT DateAdd(mm,1,dateTimeColumn), count(*)
FROM [PartData] 
GROUP BY DateAdd(mm,1,dateTimeColumn)

Just need to figure out how to get the datediff in there :)

Shutz

Answer 1

Through the power of perserverance, I managed to win this battle. Here is my QueryOver for the above stated SQL query... I believe I have turned a corner in this NHibernate thing :)

Any suggestions for improving or alternative ways to re-produce the intended results are greatly appreciated.

private IQueryOver<entities.PartData, entities.PartData> PartPerHourQuery(ISession session)
{
    return session.QueryOver<entities.PartData>()
        .Select(
            Projections.Alias(
                Projections.GroupProperty(
                    Projections.SqlFunction(
                        new SQLFunctionTemplate(NHibernateUtil.DateTime, "DateAdd(minute," +
                                                                         new SQLFunctionTemplate(
                                                                             NHibernateUtil.DateTime,
                                                                             "(DateDiff(minute, 0, Date)/1)*1") +
                                                                         ",0)"),
                        NHibernateUtil.DateTime, _partsDate))
                , "Date"),
            Projections.Alias(Projections.RowCount(), "Count"))
        .TransformUsing(Transformers.AliasToBean<entities.PartsPerHour>());
}

Shutz