Geek asks and answers

Nhibernate QueryOver - How to group by month?

Trying to get the total number of rows grouped by month and fighting QueryOver. From the limited resources that I have found, it seems that the key is Projections.SqlGroupProjection, but I cannot get it right.

This is what I still have:

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>();
+3
source share
3 answers

Hereinafter, for reference, the following:

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

What happened:

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
+6
source

you can use datepart functions, look here to see an example. Just write a few lines:

var result = session.QueryOver<Item>()
                .Where(
                Restrictions.Eq(
                Projections.SqlFunction("month"
                , NHibernateUtil.Int32
                , Projections.Property<Item>(item => item.StartDate)
                ) 
                ,1)).List();

Item StartDate. , datepart .

+3

I did not try myself, I just put the answers to other questions together:

session.QueryOver<...>()
   .SelectList(list => list
      .Select(GroupProperty(Projections.SqlProjection(...)))
      .SelectSum(x => x.Price)
   );

+

group post by new { post.Date.Year, post.Date.Month } into grouped

=

.Select(GroupProperty(Projections.SqlProjection(
    new 
    { 
        post.Date.Year, 
        post.Date.Month 
    })))

Edit

Another attempt:

   .SelectList(list => list
       .SelectGroup(c => c.Date.Year),
       .SelectGroup(c => c.Date.Month),
       .SelectCount(m => m))
0
source

More articles:


All Articles