NHibernate如何将这个SQL查询转换为QueryOver或Linq

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.

对于如何将这个sql查询转换为NHibernate查询，我确实需要一些指导。我的尝试是使用Linq，因为我对Linq比较熟悉。我们正在进行的项目使用的是QueryOver，所以现在最好保持这种方式。

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

工作SQL查询:将DateTime改为1分钟，组/排序/计数正确…

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:

收益率以下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 :)

只需要弄清楚如何在其中获取datediff:)

1 个解决方案

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

通过不屈不挠的力量，我设法赢得了这场战斗。下面是我对上述SQL查询的查询……我相信我已经在这个NHibernate的事情上转了个弯:)

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

#1