Nhibernate QueryOver - 如何按月分组？

Question

试图获得按月分组的总行数，并与 QueryOver 作斗争。从我发现的有限资源来看，似乎线索是 Projections.SqlGroupProjection 但我无法正确理解。

这是我目前所拥有的:

var jobs = _statelessSession.QueryOver<Job>()
                .SelectList(list => list
                                        .Select(Projections.SqlGroupProjection("MONTH(DateCreated) AS Month", "MONTH(DateCreated)",new string[]{"DateCreated"},new[]{NHibernateUtil.Int32}))
                                        .SelectCount(m => m))
                .List<object>();

Answer 1

最后得到以下内容以供将来引用:

            var startDate = DateTime.Now.AddMonths(-6);
            startDate = new DateTime(startDate.Year,startDate.Month,1);
            var jobs = _session.QueryOver<Job>()
                .WhereRestrictionOn(c => c.DateCreated).IsBetween(startDate).And(DateTime.Now)
                .SelectList(list => list
                    .Select(Projections.SqlGroupProjection(
                        "YEAR(DateCreated) As [Year]",
                        "YEAR(DateCreated)",
                        new[] { "YEAR" },
                        new IType[]{NHibernateUtil.Int32}))
                    .Select(Projections.SqlGroupProjection(
                        "MONTH(DateCreated) As [Month]",
                        "MONTH(DateCreated)",
                        new[] { "Month" },
                        new IType[] { NHibernateUtil.Int32 }))
                    .SelectCount(x=>x.Id))
                .OrderBy(Projections.SqlFunction(
                       "YEAR",
                       NHibernateUtil.Int32,
                       Projections.Property<Job>(item=>item.DateCreated))).Desc
                .ThenBy(Projections.SqlFunction(
                       "MONTH",
                       NHibernateUtil.Int32,
                       Projections.Property<Job>(item => item.DateCreated))).Desc
                .List<object>();

生产者:

SELECT YEAR(DateCreated)  As [Year],
       MONTH(DateCreated) As [Month],
       count(this_.Id)    as y2_
FROM   Jobs this_
WHERE  this_.DateCreated between '2011-11-01T00:00:00.00' /* @p0 */ and '2012-05-07T14:23:52.00' /* @p1 */
GROUP  BY YEAR(DateCreated),
          MONTH(DateCreated)
ORDER  BY datepart(year, this_.DateCreated) desc,
          datepart(month, this_.DateCreated) desc