Geek asks and answers

NHibernate How to convert this SQL query to QueryOver or Linq

I could really use some recommendations on how to take this sql query in NHibernate QueryOver. My attempts have been to use Linq, as I am a little more familiar with Linq. The project we are working on uses QueryOver, so it’s best to stay on this path.

Working SQL query: DateTime rounds up to 1 minute and group / sort / count correctly ...

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 the expected results:

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

Installs the following SQL:

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

You just need to figure out how to get the datif there :)

+5
source share
1 answer

, . QueryOver SQL-... , NHibernate:)

.

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

More articles:


All Articles